information_schema.columns slow performance

Question

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

SELECT TABLE_NAME, TABLE_SCHEMA, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, NUMERIC_PRECISION, CHARACTER_MAXIMUM_LENGTH, NUMERIC_SCALE, DATA_TYPE, IS_NULLABLE
FROM "information_schema"."columns" 
WHERE TABLE_NAME = 'some_instances' 
 AND TABLE_SCHEMA = 'public' 
ORDER BY ORDINAL_POSITION

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

Answer 1

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))