Subject: [pgjdbc/pgjdbc] issue #3505: Metadata queries are much slower after update to 24.7.5

From: "labkey-tchad (@labkey-tchad)" <[email-masked]>
Date: Sat, 01 Feb 2025 01:10:02 +0000

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

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sat, 01 Feb 2025 06:21:53 +0000

hmmm 2700 ms doesn't seem right. Thanks for the report.
Dave

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sat, 01 Feb 2025 16:29:53 +0000

I can't see the plans they overlap each other. Can you send them in text format?

---

From: "labkey-tchad (@labkey-tchad)" <[email-masked]>
Date: Mon, 03 Feb 2025 17:31:02 +0000

> hmmm 2700 ms doesn't seem right. Thanks for the report. Dave

2700ms is the total for fetching metadata for 117 different tables. Each table takes around 30ms (compared to 5ms with the 24.7.4 driver)
The full texts of the queries are hidden under the expandos above (e.g. "24.7.5 query")

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 03 Feb 2025 17:55:56 +0000

I'd like to see the plans for each in text

---

From: "labkey-tchad (@labkey-tchad)" <[email-masked]>
Date: Mon, 03 Feb 2025 18:36:29 +0000

Query plans:
<details><summary>24.7.5</summary>

```
Sort  (cost=36.05..36.05 rows=1 width=656) (actual time=2.927..2.929 rows=0 loops=1)
  Sort Key: pkn.nspname, pkc.relname, con.conname, pos.n
  Sort Method: quicksort  Memory: 25kB
  ->  Result  (cost=2.25..36.04 rows=1 width=656) (actual time=2.857..2.859 rows=0 loops=1)
        One-Time Filter: (current_database() = 'labkey_24_10_snap'::name)
        ->  Nested Loop  (cost=2.25..36.00 rows=1 width=520) (actual time=2.830..2.832 rows=0 loops=1)
              Join Filter: ((fkc.oid = fka.attrelid) AND (fka.attnum = con.conkey[pos.n]))
              ->  Nested Loop  (cost=1.83..29.51 rows=1 width=487) (actual time=2.830..2.832 rows=0 loops=1)
                    ->  Nested Loop  (cost=1.54..27.91 rows=1 width=427) (actual time=2.829..2.831 rows=0 loops=1)
                          Join Filter: (pka.attnum = con.confkey[pos.n])
                          ->  Nested Loop  (cost=1.54..27.19 rows=1 width=448) (actual time=2.829..2.830 rows=0 loops=1)
                                Join Filter: (fkn.oid = fkc.relnamespace)
                                ->  Nested Loop  (cost=1.54..22.02 rows=1 width=388) (actual time=2.829..2.830 rows=0 loops=1)
                                      ->  Nested Loop  (cost=1.12..20.69 rows=1 width=330) (actual time=2.829..2.830 rows=0 loops=1)
                                            ->  Nested Loop  (cost=0.98..20.53 rows=1 width=270) (actual time=2.829..2.829 rows=0 loops=1)
                                                  ->  Nested Loop  (cost=0.69..18.93 rows=1 width=198) (actual time=2.828..2.829 rows=0 loops=1)
                                                        ->  Index Scan using pg_class_relname_nsp_index on pg_class fkc  (cost=0.41..8.43 rows=1 width=72) (actual time=0.048..0.049 rows=1 loops=1)
                                                              Index Cond: (relname = 'document'::name)
                                                        ->  Index Scan using pg_constraint_conrelid_contypid_conname_index on pg_constraint con  (cost=0.28..10.50 rows=1 width=126) (actual time=2.777..2.777 rows=0 loops=1)
                                                              Index Cond: (conrelid = fkc.oid)
                                                              Filter: (contype = 'f'::"char")
                                                              Rows Removed by Filter: 1
                                                  ->  Index Scan using pg_class_oid_index on pg_class pkc  (cost=0.29..1.59 rows=1 width=72) (never executed)
                                                        Index Cond: (oid = con.confrelid)
                                            ->  Index Scan using pg_namespace_oid_index on pg_namespace pkn  (cost=0.14..0.16 rows=1 width=68) (never executed)
                                                  Index Cond: (oid = pkc.relnamespace)
                                      ->  Index Scan using pg_attribute_relid_attnum_index on pg_attribute pka  (cost=0.42..1.23 rows=9 width=70) (never executed)
                                            Index Cond: (attrelid = pkc.oid)
                                ->  Seq Scan on pg_namespace fkn  (cost=0.00..5.16 rows=1 width=68) (never executed)
                                      Filter: (nspname = 'cds'::name)
                          ->  Function Scan on generate_series pos  (cost=0.00..0.32 rows=32 width=4) (never executed)
                    ->  Index Scan using pg_class_oid_index on pg_class pkic  (cost=0.29..1.60 rows=1 width=68) (never executed)
                          Index Cond: (oid = con.conindid)
                          Filter: ((relkind = 'i'::"char") OR (relkind = 'I'::"char"))
              ->  Index Scan using pg_attribute_relid_attnum_index on pg_attribute fka  (cost=0.42..6.35 rows=9 width=70) (never executed)
                    Index Cond: (attrelid = con.conrelid)
Planning Time: 86.614 ms
Execution Time: 3.778 ms
```
</details>

<details><summary>24.7.4</summary>

```
Sort  (cost=36.04..36.04 rows=1 width=592) (actual time=0.015..0.016 rows=0 loops=1)
  Sort Key: pkn.nspname, pkc.relname, con.conname, pos.n
  Sort Method: quicksort  Memory: 25kB
  ->  Nested Loop  (cost=2.24..36.03 rows=1 width=592) (actual time=0.012..0.013 rows=0 loops=1)
        ->  Nested Loop  (cost=1.96..34.40 rows=1 width=460) (actual time=0.012..0.013 rows=0 loops=1)
              Join Filter: ((fkc.oid = fka.attrelid) AND (fka.attnum = con.conkey[pos.n]))
              ->  Nested Loop  (cost=1.54..27.91 rows=1 width=427) (actual time=0.012..0.012 rows=0 loops=1)
                    Join Filter: (pka.attnum = con.confkey[pos.n])
                    ->  Nested Loop  (cost=1.54..27.19 rows=1 width=448) (actual time=0.012..0.012 rows=0 loops=1)
                          Join Filter: (fkn.oid = fkc.relnamespace)
                          ->  Nested Loop  (cost=1.54..22.02 rows=1 width=388) (actual time=0.012..0.012 rows=0 loops=1)
                                ->  Nested Loop  (cost=1.12..20.69 rows=1 width=330) (actual time=0.012..0.012 rows=0 loops=1)
                                      ->  Nested Loop  (cost=0.98..20.53 rows=1 width=270) (actual time=0.012..0.012 rows=0 loops=1)
                                            ->  Nested Loop  (cost=0.69..18.93 rows=1 width=198) (actual time=0.012..0.012 rows=0 loops=1)
                                                  ->  Index Scan using pg_class_relname_nsp_index on pg_class fkc  (cost=0.41..8.43 rows=1 width=72) (actual time=0.006..0.006 rows=1 loops=1)
                                                        Index Cond: (relname = 'document'::name)
                                                  ->  Index Scan using pg_constraint_conrelid_contypid_conname_index on pg_constraint con  (cost=0.28..10.50 rows=1 width=126) (actual time=0.004..0.004 rows=0 loops=1)
                                                        Index Cond: (conrelid = fkc.oid)
                                                        Filter: (contype = 'f'::"char")
                                                        Rows Removed by Filter: 1
                                            ->  Index Scan using pg_class_oid_index on pg_class pkc  (cost=0.29..1.59 rows=1 width=72) (never executed)
                                                  Index Cond: (oid = con.confrelid)
                                      ->  Index Scan using pg_namespace_oid_index on pg_namespace pkn  (cost=0.14..0.16 rows=1 width=68) (never executed)
                                            Index Cond: (oid = pkc.relnamespace)
                                ->  Index Scan using pg_attribute_relid_attnum_index on pg_attribute pka  (cost=0.42..1.23 rows=9 width=70) (never executed)
                                      Index Cond: (attrelid = pkc.oid)
                          ->  Seq Scan on pg_namespace fkn  (cost=0.00..5.16 rows=1 width=68) (never executed)
                                Filter: (nspname = 'cds'::name)
                    ->  Function Scan on generate_series pos  (cost=0.00..0.32 rows=32 width=4) (never executed)
              ->  Index Scan using pg_attribute_relid_attnum_index on pg_attribute fka  (cost=0.42..6.35 rows=9 width=70) (never executed)
                    Index Cond: (attrelid = con.conrelid)
        ->  Index Scan using pg_class_oid_index on pg_class pkic  (cost=0.29..1.60 rows=1 width=68) (never executed)
              Index Cond: (oid = con.conindid)
              Filter: ((relkind = 'i'::"char") OR (relkind = 'I'::"char"))
Planning Time: 3.158 ms
Execution Time: 0.051 ms
```
</details>

---

From: "MalloD12 (@MalloD12)" <[email-masked]>
Date: Tue, 11 Feb 2025 17:00:50 +0000

Hey @davecramer, 

I would like to double-check with you whether the below change:

```java
if (catalog != null) {
      sql += " AND current_database() = " + escapeQuotes(catalog);
```

is a fix you guys have addressed in the latest release. I think if the answer is yes, we would need to make the appropriate change on the Liquibase side to avoid having a catalog mismatch that causes this [issue](https://github.com/liquibase/liquibase/issues/6666). Could you please let me know? 

Thanks in advance,
Daniel.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 11 Feb 2025 17:08:44 +0000

@MalloD12 what line numbers are those in our code

---

From: "cmuchinsky (@cmuchinsky)" <[email-masked]>
Date: Tue, 11 Feb 2025 17:11:37 +0000

> Hey [@davecramer](https://github.com/davecramer),
> 
> Thanks in advance, Daniel.

Is my assumption correct that the linked liquibase issue is caused by the strict equality check vs using `LIKE`? The performance is certainly worse, but the functional problem with liquibase is related to the case

---

From: "MalloD12 (@MalloD12)" <[email-masked]>
Date: Tue, 11 Feb 2025 18:07:17 +0000

> [@MalloD12](https://github.com/MalloD12) what line numbers are those in our code

Hey @davecramer,

That code change starts in  [L1205](https://github.com/pgjdbc/pgjdbc/blob/6437a20cac2d544bee36e79082bdad0a34704585/pgjdbc/src/main/java/org/postgresql/jdbc/PgDatabaseMetaData.java#L1205).

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 11 Feb 2025 18:28:44 +0000

I'd have to think about the change. But I'm curious why this is a problem for liquibase?

---

From: "MalloD12 (@MalloD12)" <[email-masked]>
Date: Tue, 11 Feb 2025 18:42:28 +0000

>From a Liquibase point of view, this is affecting us because we lowercase the catalog name. This is causing problems like the one one of our users is reporting [here](https://github.com/liquibase/liquibase/issues/6666):

> Upgrading the PostgreSQL JDBC driver from 42.7.4 to 42.7.5 causes Liquibase to fail during (a second) server startup, as it does not recognize the existing databasechangelog table and attempts to create it again, resulting in an error: relation "databasechangelog" already exists
The error prevents server startup, making the application unusable.

Thanks,
Daniel.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Tue, 11 Feb 2025 18:47:52 +0000

A key factor here is "planning time", so we could probably improve the thing a lot if we apply prepared statements.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 11 Feb 2025 19:06:56 +0000

> The error prevents server startup, making the application unusable.

So if you lowercase the catalog the test should be true, no ?

---

From: "MalloD12 (@MalloD12)" <[email-masked]>
Date: Tue, 11 Feb 2025 20:21:22 +0000

Yeap, I think we can do that to make it work on our side.

Thanks,
Daniel.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 11 Feb 2025 20:25:04 +0000

I thought you said you already did that?

---

From: "MalloD12 (@MalloD12)" <[email-masked]>
Date: Tue, 11 Feb 2025 20:36:34 +0000

Yeah, so far we were lowercasing the catalog from what the user was specifying, but what I meant now is that we can lowercase Postgres driver response, so we are always getting something already lowercase.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 11 Feb 2025 21:08:18 +0000

Let's not get too hasty. Do you allow upper case databases (catalogs)? There may be issues if the user creates an upper case database

---

From: "MalloD12 (@MalloD12)" <[email-masked]>
Date: Thu, 13 Feb 2025 18:53:11 +0000

Hi @davecramer, I missed your last response here. We could have someone specify a URL like this `jdbc:postgresql://localhost:5438/TEST` in their liquibase properties. What we have been doing so far is lowercase the specified catalog name, but considering the latest changes made to the Metadata we will have to update this. 

Thanks,
Daniel.

---

From: "kdebski85 (@kdebski85)" <[email-masked]>
Date: Tue, 25 Feb 2025 13:53:14 +0000

I think this issue has been sidetracked.
The report is about efficiency, not about checking lower/upper case.

---

From: "labkey-jeckels (@labkey-jeckels)" <[email-masked]>
Date: Fri, 13 Jun 2025 16:04:41 +0000

Checking in on the original issue reported here, the performance degradation. I work with the original reporter, and for our use case the slowdown is significant enough that it's preventing us from upgrading to newer releases of the driver. Please let us know if you need any more repro details.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 13 Jun 2025 16:49:16 +0000

would it be possible for you to try building with https://github.com/pgjdbc/pgjdbc/pull/3641 and let us know if that fixes it ?

---

From: "labkey-jeckels (@labkey-jeckels)" <[email-masked]>
Date: Tue, 17 Jun 2025 01:44:14 +0000

> would it be possible for you to try building with [#3641](https://github.com/pgjdbc/pgjdbc/pull/3641) and let us know if that fixes it ?

Thanks Dave. I see that the PR was merged already. We'll give it a try and report back. @labkey-adam

---

From: "labkey-adam (@labkey-adam)" <[email-masked]>
Date: Mon, 23 Jun 2025 19:25:54 +0000

Local testing of the current 42.7.7-SNAPSHOT driver shows a significant improvement in the performance of `getImportedKeys()`. While still 47% slower than 42.7.4, it's definitely a huge improvement over 42.7.5 and 42.7.6.

Average query time for getImportedKeys() on my local machine (in all cases >1,100 invocations across a wide variety of tables in many schemas):

| Version|Average Invocation Time|
|--------|--------|
| 42.7.7-SNAPSHOT|14.4ms|
| 42.7.6|88.5ms|
| 42.7.4|9.8ms|

My absolute times differ from what my colleague @labkey-tchad reported initially (different environments), but I see similar order of magnitude degradation in performance with the recent releases. It would be nice to get back below 10ms on average, but the SNAPSHOT shows enough improvement that we'll no longer be blocked from upgrading. We look forward to an official release of 42.7.8.

Thanks for all your work on this important piece of software!

Adam

---

From: "labkey-tchad (@labkey-tchad)" <[email-masked]>
Date: Fri, 31 Oct 2025 20:41:24 +0000

We've updated to 42.7.8 and are having no performance issues.
Thank you very much.