Обсуждение: BUG #16836: performance drop while query information_schema since switch from postgres 10 to 12
BUG #16836: performance drop while query information_schema since switch from postgres 10 to 12
От
PG Bug reporting form
Дата:
The following bug has been logged on the website: Bug reference: 16836 Logged by: Al Bundy Email address: therealhawk@freenet.de PostgreSQL version: 12.3 Operating system: Linux Description: Since we swtiched from PostgeSQL 10 to 12 we noticed a significant performance drop while executing this query: SELECT x.table_schema as schema_name, c.constraint_name as constraint_name, x.table_name as table_name, x.column_name as column_name, y.table_name as referenced_table_name, y.column_name as referenced_column_name, y.ordinal_position as ordinal_position FROM information_schema.referential_constraints c JOIN information_schema.key_column_usage x ON (x.constraint_name = c.constraint_name) JOIN information_schema.key_column_usage y ON (y.ordinal_position = x.position_in_unique_constraint AND y.constraint_name = c.unique_constraint_name) WHERE LOWER(y.table_name) = LOWER('PUT_TABLE_NAME_HERE') ORDER BY x.table_name, c.constraint_name, x.ordinal_position; In Postgres 10 this query took under 1sec and since upgrading (fresh install + pg_restore) this query need 20sec! I noticed that the query gets much faster (maybe like in PostgreSQL 10) if I change LOWER(y.table_name) to y.table_name Unfortunately I could not reproduce this with an empty database to provide a test-case. Maybe this depends on the amounts of tables or references.