Message-ID: From: "labkey-tchad (@labkey-tchad)" To: "pgjdbc/pgjdbc" Date: Mon, 03 Feb 2025 18:36:29 +0000 Subject: Re: [pgjdbc/pgjdbc] issue #3505: Metadata queries are much slower after update to 24.7.5 In-Reply-To: References: List-Id: X-GitHub-Author-Login: labkey-tchad X-GitHub-Comment-Id: 2631770337 X-GitHub-Comment-Type: issue_comment X-GitHub-Issue: 3505 X-GitHub-Repo: pgjdbc/pgjdbc X-GitHub-Type: comment X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/3505#issuecomment-2631770337 Content-Type: text/plain; charset=utf-8 Query plans:
24.7.5 ``` 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 ```
24.7.4 ``` 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 ```