Hi Lars,
> psql (14.1, server 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)) Maybe you can upgrade to 12.9 ( from 12.6 ) ( https://www.postgresql.org/docs/release/12.9/ ) And the next minor release = pg 12.10 is expected on February 10th, 2022 https://www.postgresql.org/developer/roadmap/ As I see - only a minor fix exists for "system columns": "Don't ignore system columns when estimating the number of groups using extended statistics (Tomas Vondra)" in 12.7 I have similar experiences with the system tables - vacuuming is extreme important in my case - I am calling "vacuum" in every ETL job - cleaning my system tables. select schemaname ,relname ,n_tup_ins ,n_tup_upd ,n_tup_del ,n_tup_hot_upd ,n_live_tup ,n_dead_tup from pg_stat_all_tables where n_dead_tup > 0 and schemaname='pg_catalog' ; Regards, Imre Lars Aksel Opsahl <lars.ops...@nibio.no> ezt írta (időpont: 2022. febr. 7., H, 18:40): > >> > > >> Here is a slow one: > > >> https://explain.depesz.com/s/tUt5 > > >> > > >> and here is fast one : > > >> https://explain.depesz.com/s/yYG4 > > > > > >The only difference is that this is sometimes many times slower. > > > > > > Finalize Aggregate (cost=42021.15..42021.16 rows=1 width=8) (actual > time=50602.755..117201.768 rows=1 loops=1) > > > -> Gather (cost=42020.94..42021.15 rows=2 width=8) (actual > time=130.527..117201.754 rows=3 loops=1) > > > Workers Planned: 2 > > > Workers Launched: 2 > > > > > >> Here are my settings (the server has around 256 GB og memory) : > > > > > > Hi > > > Here is some more info. > > > >What version of postgres ? What OS/version ? > > > psql (14.1, server 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)) > > >https://wiki.postgresql.org/wiki/Slow_Query_Questions > > > > > >Are there any server logs around that time ? > > > Yes but nothing in the logs that I could find. > > > >Or session logs for the slow query ? > > > > > >Is it because the table creation is locking (rows of) various system > catalogs ? > > >I'm not sure if it'd be a single, long delay that you could see easily > with > > >log_lock_waits, or a large number of small delays, maybe depending on > whether > > >your table creation is done within a transaction. > > > Added log_lock_waits but could not anything new in the logs > > > SHOW deadlock_timeout ; > > deadlock_timeout > > ------------------ > > 1s > > SHOW log_lock_waits; > > log_lock_waits > > ---------------- > > on > > (1 row) > > > In the logs I only things like this > > LOG: duration: 71841.233 ms statement: CREATE UNLOGGED TABLE IF NOT > EXISTS tmp_klimagass.styredata_tidligbygg_159298..... > > > LOG: duration: 12645.127 ms statement: GRANT SELECT ON TABLE > tmp_klimagass.vaerdata_159296 TO org_mojo2_sl_read_role; > > LOG: duration: 15783.611 ms statement: EXPLAIN ANALYZE select count(*) > > from information_schema.tables; > > LOG: duration: 35594.903 ms statement: EXPLAIN ANALYZE select count(*) > > Can not find anything here either > > > select relation::regclass, * from pg_locks where not granted; > > relation | locktype | database | relation | page | tuple | virtualxid | > transactionid | classid | objid | objsubid | virtualtransaction | pid | > mode | granted | fastpath > > > ----------+----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-----+------+---------+---------- > > (0 rows) > > > Time: 55.270 ms > > > > > > >-- > > >Justin > > Thanks > > Lars >
