we have system that quite a lot and often asks database about:

FROM "information_schema"."columns" 
WHERE TABLE_NAME = 'some_instances' 
 AND TABLE_SCHEMA = 'public' 

Time to execute this query is 10s+, and it ruins overall performance

"information_schema"."columns" contains more than 5m records

Postgres version is 9.6

How to speed up such query?

UPD. cant paste here execution plan as it's off stackexchange limitation. here is the link: https://github.com/mkdel/code_pile/blob/main/EXPLAIN_ANALYZE_BUFFERS

  • Run EXPLAIN (ANALYZE, BUFFERS) SELECT ..., edit the question and add the output there. Feb 10 '21 at 11:56
  • Hi @Mikhail, I have tried to reproduce your issue by creating 7M columns in my test 9.6 database - but the plan is completely different, it uses index scan. explain.depesz.com/s/WHMK Maybe you have autovacuum disabled and the stats are not updated? This could affect query planner.
    – filiprem
    Feb 13 '21 at 10:57

I think you have a very strange row estimation here (2064 vs 1)

->  Seq Scan on pg_class c  (cost=0.00..96893.03 rows=2064 width=76) (actual time=0.073..386.865 rows=1 loops=1)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
                                                              Filter: ((relkind = ANY ('{r,v,f}'::"char"[])) AND (((relname)::information_schema.sql_identifier)::text = 'some_instances'::text))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                                                              Rows Removed by Filter: 962568                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
                                                              Buffers: shared hit=69844                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

I think maybe this is affecting you PG 12 release notes:

Treat object-name columns in the information_schema views as being of type name, not varchar (Tom Lane)

Per the SQL standard, object-name columns in the information_schema views are declared as being of domain type sql_identifier. In PostgreSQL, the underlying catalog columns are really of type name. This change makes sql_identifier be a domain over name, rather than varchar as before. This eliminates a semantic mismatch in comparison and sorting behavior, which can greatly improve the performance of queries on information_schema views that restrict an object-name column. Note however that inequality restrictions, for example

SELECT ... FROM information_schema.tables WHERE table_name < 'foo'; will now use calculation“C”-locale comparison semantics by default, rather than the database's default collation as before. Sorting on these columns will also follow “C” ordering rules. The previous behavior (and inefficiency) can be enforced by adding a COLLATE "default" clause.

Edit, in pg 12 I get for your example query:

->  Index Scan using pg_class_relname_nsp_index on pg_class c  (cost=0.27..8.30 rows=1 width=76) (actual time=0.018..0.018 rows=0 loops=1)
                                                         Index Cond: (relname = 'some_instances'::name)
                                                         Filter: (relkind = ANY ('{r,v,f,p}'::"char"[]))
                                                   ->  Seq Scan on pg_namespace nc  (cost=0.00..1.09 rows=1 width=68) (never executed)
                                                         Filter: ((NOT pg_is_other_temp_schema(oid)) AND (nspname = 'public'::name))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.