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: Re: [pgjdbc/pgjdbc] issue #3505: Metadata queries are much slower after update to 24.7.5
Date: Mon, 03 Feb 2025 18:36:29 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
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>
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