Subject: [pgjdbc/pgjdbc] PR #3533: refactor: Use key_column_usage instead of indnkeyatts in PgDatabaseMetaData for faster speed and improved compatibility

From: "hlcianfagna (@hlcianfagna)" <[email-masked]>
Date: Thu, 20 Feb 2025 11:45:16 +0000

https://github.com/pgjdbc/pgjdbc/pull/3434 introduced a change to avoid mistakenly treating included columns as part of the PK, however the way this is done relies on a particular metadata field which some "PostgreSQL-compatible" database systems (e.g. CrateDB) do not populate in the same way as PostgreSQL does, this refactor addresses this by getting the same information via the standard `information_schema.key_column_usage` view, this has the added benefit of shaving a few milliseconds on the `getPrimaryKeys` call.

### All Submissions:

* [YES] Have you followed the guidelines in our [Contributing](https://github.com/pgjdbc/pgjdbc/blob/master/CONTRIBUTING.md) document?
* [YES] Have you checked to ensure there aren't other open [Pull Requests](../../pulls) for the same update/change?

### Changes to Existing Features:

* [NO] Does this break existing behaviour? If so please explain.
* [YES] Have you added an explanation of what your changes do and why you'd like us to include them?
* [NA] Have you written new tests for your core changes, as applicable?
* [YES] Have you successfully run tests with your changes locally?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 20 Feb 2025 12:01:21 +0000

Please make sure this does not introduce a performance regression

---

From: "hlcianfagna (@hlcianfagna)" <[email-masked]>
Date: Fri, 28 Feb 2025 10:09:04 +0000

> Please make sure this does not introduce a performance regression

Good point @davecramer , is there any particular benchmark that you would use to validate this?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 28 Feb 2025 12:17:57 +0000

No real benchmark, just run explain analyze on the two queries to make sure you don't introduce a regression

---

From: "hlcianfagna (@hlcianfagna)" <[email-masked]>
Date: Fri, 07 Mar 2025 17:50:55 +0000

> No real benchmark, just run explain analyze on the two queries to make sure you don't introduce a regression

This new version I just committed is actually faster that what we have in master:

<details>

<summary>Before this PR</summary>

```
QUERY PLAN                                                                                                                                                               |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Sort  (cost=4446.51..4446.54 rows=13 width=324) (actual time=4.990..5.010 rows=162 loops=1)                                                                              |
  Sort Key: result.table_name, result.pk_name, result.key_seq                                                                                                            |
  Sort Method: quicksort  Memory: 79kB                                                                                                                                   |
  ->  Subquery Scan on result  (cost=53.16..4446.27 rows=13 width=324) (actual time=0.770..4.839 rows=162 loops=1)                                                       |
        Filter: ((result.key_seq <= result.key_count) AND (result.a_attnum = (result.keys).x))                                                                           |
        Rows Removed by Filter: 1794                                                                                                                                     |
        ->  Result  (cost=53.16..4331.07 rows=7680 width=360) (actual time=0.767..4.645 rows=1956 loops=1)                                                               |
              ->  ProjectSet  (cost=53.16..395.07 rows=7680 width=292) (actual time=0.762..4.224 rows=1956 loops=1)                                                      |
                    ->  Hash Join  (cost=53.16..160.83 rows=768 width=287) (actual time=0.570..1.256 rows=1316 loops=1)                                                  |
                          Hash Cond: (a.attrelid = ct.oid)                                                                                                               |
                          ->  Seq Scan on pg_attribute a  (cost=0.00..88.26 rows=3126 width=70) (actual time=0.007..0.256 rows=3126 loops=1)                             |
                          ->  Hash  (cost=51.89..51.89 rows=102 width=229) (actual time=0.520..0.533 rows=102 loops=1)                                                   |
                                Buckets: 1024  Batches: 1  Memory Usage: 35kB                                                                                            |
                                ->  Hash Join  (cost=28.53..51.89 rows=102 width=229) (actual time=0.322..0.479 rows=102 loops=1)                                        |
                                      Hash Cond: (ct.relnamespace = n.oid)                                                                                               |
                                      ->  Hash Join  (cost=27.44..50.24 rows=102 width=169) (actual time=0.301..0.427 rows=102 loops=1)                                  |
                                            Hash Cond: (ct.oid = i.indrelid)                                                                                             |
                                            ->  Seq Scan on pg_class ct  (cost=0.00..18.15 rows=415 width=72) (actual time=0.003..0.043 rows=415 loops=1)                |
                                            ->  Hash  (cost=26.17..26.17 rows=102 width=97) (actual time=0.261..0.272 rows=102 loops=1)                                  |
                                                  Buckets: 1024  Batches: 1  Memory Usage: 21kB                                                                          |
                                                  ->  Hash Join  (cost=6.92..26.17 rows=102 width=97) (actual time=0.088..0.228 rows=102 loops=1)                        |
                                                        Hash Cond: (ci.oid = i.indexrelid)                                                                               |
                                                        ->  Seq Scan on pg_class ci  (cost=0.00..18.15 rows=415 width=68) (actual time=0.002..0.054 rows=415 loops=1)    |
                                                        ->  Hash  (cost=5.64..5.64 rows=102 width=37) (actual time=0.077..0.078 rows=102 loops=1)                        |
                                                              Buckets: 1024  Batches: 1  Memory Usage: 15kB                                                              |
                                                              ->  Seq Scan on pg_index i  (cost=0.00..5.64 rows=102 width=37) (actual time=0.004..0.040 rows=102 loops=1)|
                                                                    Filter: indisprimary                                                                                 |
                                                                    Rows Removed by Filter: 62                                                                           |
                                      ->  Hash  (cost=1.04..1.04 rows=4 width=68) (actual time=0.009..0.009 rows=5 loops=1)                                              |
                                            Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                 |
                                            ->  Seq Scan on pg_namespace n  (cost=0.00..1.04 rows=4 width=68) (actual time=0.005..0.006 rows=5 loops=1)                  |
Planning Time: 0.772 ms                                                                                                                                                  |
Execution Time: 5.162 ms                                                                                                                                                 |
```

</details>

<details>

<summary>After this PR</summary>

```
QUERY PLAN                                                                                                                                                                                             |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Sort  (cost=93.68..93.68 rows=1 width=324) (actual time=1.222..1.228 rows=82 loops=1)                                                                                                                  |
  Sort Key: ((ss.relname)::information_schema.sql_identifier), ((ss.conname)::information_schema.sql_identifier), (((ss.x).n)::information_schema.cardinal_number)                                     |
  Sort Method: quicksort  Memory: 52kB                                                                                                                                                                 |
  ->  Result  (cost=31.73..93.67 rows=1 width=324) (actual time=0.789..1.186 rows=82 loops=1)                                                                                                          |
        One-Time Filter: (current_database() = current_database())                                                                                                                                     |
        ->  Nested Loop  (cost=31.73..93.66 rows=1 width=288) (actual time=0.786..1.160 rows=82 loops=1)                                                                                               |
              ->  Nested Loop  (cost=31.44..57.45 rows=7 width=232) (actual time=0.763..0.979 rows=82 loops=1)                                                                                         |
                    ->  Hash Join  (cost=31.29..51.28 rows=28 width=236) (actual time=0.759..0.839 rows=181 loops=1)                                                                                   |
                          Hash Cond: (pg_class_key.relname = ss.conname)                                                                                                                               |
                          ->  Seq Scan on pg_class pg_class_key  (cost=0.00..18.15 rows=415 width=68) (actual time=0.006..0.029 rows=415 loops=1)                                                      |
                          ->  Hash  (cost=30.94..30.94 rows=28 width=232) (actual time=0.721..0.724 rows=181 loops=1)                                                                                  |
                                Buckets: 1024  Batches: 1  Memory Usage: 55kB                                                                                                                          |
                                ->  Subquery Scan on ss  (cost=21.27..30.94 rows=28 width=232) (actual time=0.300..0.648 rows=181 loops=1)                                                             |
                                      ->  ProjectSet  (cost=21.27..30.66 rows=28 width=341) (actual time=0.299..0.630 rows=181 loops=1)                                                                |
                                            ->  Nested Loop  (cost=21.27..26.95 rows=14 width=224) (actual time=0.160..0.253 rows=110 loops=1)                                                         |
                                                  ->  Hash Join  (cost=21.13..26.05 rows=14 width=228) (actual time=0.134..0.191 rows=110 loops=1)                                                     |
                                                        Hash Cond: (r.relnamespace = nr.oid)                                                                                                           |
                                                        ->  Hash Join  (cost=20.04..24.87 rows=18 width=168) (actual time=0.106..0.146 rows=110 loops=1)                                               |
                                                              Hash Cond: (c.conrelid = r.oid)                                                                                                          |
                                                              ->  Seq Scan on pg_constraint c  (cost=0.00..4.54 rows=110 width=96) (actual time=0.004..0.018 rows=110 loops=1)                         |
                                                                    Filter: (contype = ANY ('{p,u,f}'::"char"[]))                                                                                      |
                                                                    Rows Removed by Filter: 2                                                                                                          |
                                                              ->  Hash  (cost=19.19..19.19 rows=68 width=76) (actual time=0.098..0.098 rows=68 loops=1)                                                |
                                                                    Buckets: 1024  Batches: 1  Memory Usage: 16kB                                                                                      |
                                                                    ->  Seq Scan on pg_class r  (cost=0.00..19.19 rows=68 width=76) (actual time=0.014..0.072 rows=68 loops=1)                         |
                                                                          Filter: (relkind = ANY ('{r,p}'::"char"[]))                                                                                  |
                                                                          Rows Removed by Filter: 347                                                                                                  |
                                                        ->  Hash  (cost=1.05..1.05 rows=3 width=68) (actual time=0.018..0.018 rows=5 loops=1)                                                          |
                                                              Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                             |
                                                              ->  Seq Scan on pg_namespace nr  (cost=0.00..1.05 rows=3 width=68) (actual time=0.014..0.016 rows=5 loops=1)                             |
                                                                    Filter: (NOT pg_is_other_temp_schema(oid))                                                                                         |
                                                  ->  Memoize  (cost=0.14..0.23 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=110)                                                            |
                                                        Cache Key: c.connamespace                                                                                                                      |
                                                        Cache Mode: logical                                                                                                                            |
                                                        Hits: 109  Misses: 1  Evictions: 0  Overflows: 0  Memory Usage: 1kB                                                                            |
                                                        ->  Index Only Scan using pg_namespace_oid_index on pg_namespace nc  (cost=0.13..0.22 rows=1 width=4) (actual time=0.022..0.022 rows=1 loops=1)|
                                                              Index Cond: (oid = c.connamespace)                                                                                                       |
                                                              Heap Fetches: 1                                                                                                                          |
                    ->  Index Scan using pg_index_indexrelid_index on pg_index i  (cost=0.14..0.22 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=181)                                         |
                          Index Cond: (indexrelid = pg_class_key.oid)                                                                                                                                  |
                          Filter: indisprimary                                                                                                                                                         |
                          Rows Removed by Filter: 1                                                                                                                                                    |
              ->  Index Scan using pg_attribute_relid_attnum_index on pg_attribute a  (cost=0.28..5.16 rows=1 width=70) (actual time=0.002..0.002 rows=1 loops=82)                                     |
                    Index Cond: ((attrelid = ss.roid) AND (attnum = (ss.x).x))                                                                                                                         |
                    Filter: ((NOT attisdropped) AND (pg_has_role(ss.relowner, 'USAGE'::text) OR has_column_privilege(ss.roid, attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))                    |
Planning Time: 0.890 ms                                                                                                                                                                                |
Execution Time: 1.326 ms                                                                                                                                                                               |
```

</details>

The only thing is that `information_schema.key_column_usage` does not seem to include data for the `pg_toast` schema, would that be acceptable?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sat, 08 Mar 2025 11:38:54 +0000

seems there is a compile error

---

From: "hlcianfagna (@hlcianfagna)" <[email-masked]>
Date: Mon, 10 Mar 2025 10:19:39 +0000

> seems there is a compile error

Apologies there was a missing semicolon

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 10 Mar 2025 13:44:35 +0000

(on pgjdbc/src/main/java/org/postgresql/jdbc/PgDatabaseMetaData.java:2347)

I think you need to rebase. This code where we are selecting `current_database() as TABLE_CAT` is in HEAD

---

From: "hlcianfagna (@hlcianfagna)" <[email-masked]>
Date: Mon, 10 Mar 2025 14:37:31 +0000

This has not changed since I forked this branch, but in the version I am proposing I have `table_catalog=current_database()` in the `WHERE` clause so I can select `table_catalog` as `TABLE_CAT`

---

From: "hlcianfagna (@hlcianfagna)" <[email-masked]>
Date: Mon, 10 Mar 2025 14:41:48 +0000

One more thing to note about this line, in the old version there was an outer query with quoted upper case identifiers and this inner query with unquoted identifiers. In the new version there are no subqueries, so I am returning quoted upper-case identifiers as was the case in the original outer query.

---

From: "hlcianfagna (@hlcianfagna)" <[email-masked]>
Date: Wed, 12 Mar 2025 09:18:46 +0000

As this is now performance-related, maybe @vlsi also wants to take a look?