information_schema.columns慢性能

Question

我们有相当多的系统，经常询问数据库：

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

执行此查询的时间是10s+，它会破坏整体性能。

“information_schema”。“列”包含超过500万条记录。

Postgres版本为9.6

如何加快查询速度？

UPD。无法粘贴在这里的执行计划，因为它是不受stackexchange限制。下面是链接：https://github.com/mkdel/code_堆/团/主/解释_分析_缓冲器

Answer 1

我认为您有一个非常奇怪的行估计(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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

我想这可能影响了你，PG 12发行说明：

根据information_schema标准，将information_schema视图中的对象名称列视为类型名称，而不是varchar (Tom )，则information_schema视图中的对象名称列被声明为域类型sql_identifier。在PostgreSQL中，基础目录列实际上是类型名称。这一更改使sql_identifier成为一个域名而不是以前的varchar。这消除了比较和排序行为中的语义不匹配，这可以极大地提高对限制对象名称列的information_schema视图的查询性能。但是请注意，不平等限制，例如选择.在information_schema.tables中，table_name < 'foo'；在默认情况下将使用计算“C”-locale比较语义，而不是像以前一样使用数据库的默认排序规则。对这些列的排序也将遵循“C”排序规则。以前的行为(和低效率)可以通过添加一个排序规则“默认”子句来强制执行。

编辑，在pg 12中，我为您的示例查询获得如下信息：

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