Message-ID: <gh-pgjdbc-pgjdbc-3505@github.com>
From: "labkey-tchad (@labkey-tchad)" <noreply+labkey-tchad@github.com>
To: "pgjdbc/pgjdbc" <noreply+pgjdbc-pgjdbc@github.com>
Date: Sat, 01 Feb 2025 01:10:02 +0000
Subject: [pgjdbc/pgjdbc] issue #3505: Metadata queries are much slower after update to 24.7.5
List-Id: <gh-pgjdbc-pgjdbc.github.com>
X-GitHub-Assignees: davecramer
X-GitHub-Author-Id: 5263798
X-GitHub-Author-Login: labkey-tchad
X-GitHub-Issue: 3505
X-GitHub-Repo: pgjdbc/pgjdbc
X-GitHub-State: closed
X-GitHub-Type: issue
X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/3505
Content-Type: text/plain; charset=utf-8

**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)
![Image](https://github.com/user-attachments/assets/64fa0ec9-5f11-49ac-9956-1c54f000f2b1)


<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>