Re: slow queries on system tables

On 8/23/19 3:47 PM, PegoraroF10 wrote:
> This week we added just 5 new customers. Every schema has 100 tables, 300
> indices, 400 triggers.
> I cannot imagine our script doing 75000 updates just for adding those
> schemas.

Very rough calculation:

https://www.postgresql.org/docs/11/catalog-pg-class.html

The catalog pg_class catalogs tables and most everything else that has
columns or is otherwise similar to a table. This includes indexes (but
see also pg_index), sequences (but see also pg_sequence), views,
materialized views, composite types, and TOAST tables; see relkind. ...

From above, not counting TOAST tables:

5 * (100 + 300) = 2,000 new entries.

From previous post you said you had 190 schemas at that time.

190 * 400 = 76,000 entries
+ 2,000
78,000

In pg_class rel* fields:

"It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE
INDEX."

Assuming tables/indexes have changed enough to trigger vacuum run:

78,000 * 1 vacuum = 78,000 updates

>
>
>
>
> --
> Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com