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.klaver@aklaver.com