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

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(dot)Opsahl(at)nibio(dot)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
>