|
>> >> 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
|
