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
Last activity: Jul 31, 2025, 10:07 PM