pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feedFrom: hlcianfagna (@hlcianfagna) <[email protected]>
To: pgjdbc/pgjdbc <[email protected]>
Subject: Re: [pgjdbc/pgjdbc] PR #3533: refactor: Use key_column_usage instead of indnkeyatts in PgDatabaseMetaData for faster speed and improved compatibility
Date: Fri, 07 Mar 2025 17:50:55 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
> 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?
view thread (11+ 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] PR #3533: refactor: Use key_column_usage instead of indnkeyatts in PgDatabaseMetaData for faster speed and improved compatibility
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