From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: therealhawk@freenet.de
Subject: BUG #16836: performance drop while query information_schema since switch from postgres 10 to 12
Date: Mon, 25 Jan 2021 08:39:02 +0000
Message-ID: <16836-c51f4131f981afcc@postgresql.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, therealhawk@freenet.de
Subject: Re: BUG #16836: performance drop while query information_schema since switch from postgres 10 to 12
In-Reply-To: <16836-c51f4131f981afcc@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox