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

On Mon, Feb 7, 2022, 10:26 PM Lars Aksel Opsahl <Lars(dot)Opsahl(at)nibio(dot)no>
wrote:

> Hi
>
> Sometimes simple sql's like this takes a very long time "select count(*) from
> information_schema.tables;"
>
> Other sql's not including system tables may work ok but login also takes a
> very long time.
>
> The CPU load on the server is around 25%. There is no iowait.
>
>
> This happens typically when we are running many functions in parallel
> creating many temp tables and unlogged tables I think.
>
> Here is a slow one:
>
> https://explain.depesz.com/s/tUt5
>
>
> and here is fast one :
>
> https://explain.depesz.com/s/yYG4
>
>
> Here are my settings (the server has around 256 GB og memory) :
>
> max_connections = 500
>
> work_mem = 20MB
>
> effective_cache_size = 96GB
>
> effective_io_concurrency = 256
>
> shared_buffers = 96GB
>
> temp_buffers = 80MB
>
> Any hints ?
>
>
> Thanks .
>
>
> Lars
>

Can you share the output of the below query?

From the past threads I have learnt that too many templates objects may add
to bloat of system catalogs and may in start resulting in impacting
performance.
Make a note especially around

pg_attribute
pg_depends
and check for bloat, if required, vacuum full? these objects to speed up.

SELECT relname, pg_size_pretty(pg_relation_size(C.oid)) FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname =
'pg_catalog' ORDER BY 2 DESC LIMIT 20; can you show the output of this query

>