Re: slow "select count(*) from information_schema.tables;" in some cases

>>

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