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.