pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feedFrom: labkey-tchad (@labkey-tchad) <[email protected]>
To: pgjdbc/pgjdbc <[email protected]>
Subject: [pgjdbc/pgjdbc] issue #3505: Metadata queries are much slower after update to 24.7.5
Date: Sat, 01 Feb 2025 01:10:02 +0000
Message-ID: <[email protected]> (raw)
**Describe the issue**
Calls to `PgDatabaseMetaData.getImportedKeys` are taking 5-6 times longer after updating to 24.7.5. The `current_database()` calls introduced to these queries in https://github.com/pgjdbc/pgjdbc/pull/3390 seem to be the culprit.
**Driver Version:** 24.7.5
**Java Version:** 17
**OS Version:** Ubuntu 24.0.4 and Mac OS 15.1.1
**PostgreSQL Version:** 17.0 (Debian 17.0-1.pgdg120+1)
Query perf comparison (24.7.4 on left, 24.7.5 on right)
<details><summary>24.7.5 query</summary>
```sql
SELECT current_database() AS "PKTABLE_CAT",
pkn.nspname AS "PKTABLE_SCHEM",
pkc.relname AS "PKTABLE_NAME",
pka.attname AS "PKCOLUMN_NAME",
current_database() AS "FKTABLE_CAT",
fkn.nspname AS "FKTABLE_SCHEM",
fkc.relname AS "FKTABLE_NAME",
fka.attname AS "FKCOLUMN_NAME",
pos.n AS "KEY_SEQ",
CASE con.confupdtype
WHEN 'c' THEN 0
WHEN 'n' THEN 2
WHEN 'd' THEN 4
WHEN 'r' THEN 1
WHEN 'p' THEN 1
WHEN 'a' THEN 3
ELSE NULL END AS "UPDATE_RULE",
CASE con.confdeltype
WHEN 'c' THEN 0
WHEN 'n' THEN 2
WHEN 'd' THEN 4
WHEN 'r' THEN 1
WHEN 'p' THEN 1
WHEN 'a' THEN 3
ELSE NULL END AS "DELETE_RULE",
con.conname AS "FK_NAME",
pkic.relname AS "PK_NAME",
CASE
WHEN con.condeferrable AND con.condeferred THEN 5
WHEN con.condeferrable THEN 6
ELSE 7 END AS "DEFERRABILITY"
FROM pg_catalog.pg_namespace pkn,
pg_catalog.pg_class pkc,
pg_catalog.pg_attribute pka,
pg_catalog.pg_namespace fkn,
pg_catalog.pg_class fkc,
pg_catalog.pg_attribute fka,
pg_catalog.pg_constraint con,
pg_catalog.generate_series(1, 32) pos(n),
pg_catalog.pg_class pkic
WHERE pkn.oid = pkc.relnamespace
AND pkc.oid = pka.attrelid
AND pka.attnum = con.confkey[pos.n]
AND con.confrelid = pkc.oid
AND fkn.oid = fkc.relnamespace
AND fkc.oid = fka.attrelid
AND fka.attnum = con.conkey[pos.n]
AND con.conrelid = fkc.oid
AND con.contype = 'f'
AND (pkic.relkind = 'i' OR pkic.relkind = 'I')
AND pkic.oid = con.conindid
AND current_database() = 'labkey'
AND fkn.nspname = 'reagent'
AND fkc.relname = 'lots'
ORDER BY pkn.nspname, pkc.relname, con.conname, pos.n
```
</details>
<details><summary>24.7.5 query</summary>
```sql
SELECT NULL::text AS PKTABLE_CAT, pkn.nspname AS PKTABLE_SCHEM,
pkc.relname AS PKTABLE_NAME,
pka.attname AS PKCOLUMN_NAME,
NULL::text AS FKTABLE_CAT, fkn.nspname AS FKTABLE_SCHEM,
fkc.relname AS FKTABLE_NAME,
fka.attname AS FKCOLUMN_NAME,
pos.n AS KEY_SEQ,
CASE con.confupdtype
WHEN 'c' THEN 0
WHEN 'n' THEN 2
WHEN 'd' THEN 4
WHEN 'r' THEN 1
WHEN 'p' THEN 1
WHEN 'a' THEN 3
ELSE NULL END AS UPDATE_RULE,
CASE con.confdeltype
WHEN 'c' THEN 0
WHEN 'n' THEN 2
WHEN 'd' THEN 4
WHEN 'r' THEN 1
WHEN 'p' THEN 1
WHEN 'a' THEN 3
ELSE NULL END AS DELETE_RULE,
con.conname AS FK_NAME,
pkic.relname AS PK_NAME,
CASE WHEN con.condeferrable AND con.condeferred THEN 5 WHEN con.condeferrable THEN 6 ELSE 7 END AS DEFERRABILITY
FROM pg_catalog.pg_namespace pkn,
pg_catalog.pg_class pkc,
pg_catalog.pg_attribute pka,
pg_catalog.pg_namespace fkn,
pg_catalog.pg_class fkc,
pg_catalog.pg_attribute fka,
pg_catalog.pg_constraint con,
pg_catalog.generate_series(1, 32) pos(n),
pg_catalog.pg_class pkic
WHERE pkn.oid = pkc.relnamespace
AND pkc.oid = pka.attrelid
AND pka.attnum = con.confkey[pos.n]
AND con.confrelid = pkc.oid
AND fkn.oid = fkc.relnamespace
AND fkc.oid = fka.attrelid
AND fka.attnum = con.conkey[pos.n]
AND con.conrelid = fkc.oid
AND con.contype = 'f'
AND (pkic.relkind = 'i' OR pkic.relkind = 'I')
AND pkic.oid = con.conindid
AND fkn.nspname = 'reagent'
AND fkc.relname = 'lots'
ORDER BY pkn.nspname, pkc.relname, con.conname, pos.n
```
</details>
view thread (25+ messages) latest in thread
reply
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: github://pgjdbc/pgjdbc
Cc: [email protected], [email protected]
Subject: Re: [pgjdbc/pgjdbc] issue #3505: Metadata queries are much slower after update to 24.7.5
In-Reply-To: <<[email protected]>>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox