BUG #16836: performance drop while query information_schema since switch from postgres 10 to 12

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

BUG #16836: performance drop while query information_schema since switch from postgres 10 to 12

apt.postgresql.org Repository Update
The following bug has been logged on the website:

Bug reference:      16836
Logged by:          Al Bundy
Email address:      [hidden email]
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.