Sample Header Ad - 728x90

information_schema.columns slow performance

2 votes
1 answer
1525 views
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
Asked by Mikhail Aksenov (430 rep)
Feb 10, 2021, 11:12 AM
Last activity: Jul 31, 2025, 10:07 PM