On Tuesday, September 6, 2022 11:13 AM Tom Lane <t...@sss.pgh.pa.us> wrote: > > Jaime Casanova <jcasa...@systemguards.com.ec> writes: > > Just trying the new column/row filter on v15, I found this issue that > > could be replicated very easily. > > Bleah. Post-beta4 catversion bump, here we come.
Oh, Sorry for the miss. > > This could be solved by adding a "NOT attisdropped", simple patch > > attached. > > That view seems quite inefficient as written --- I wonder if we can't do > better by > nuking the join-to-unnest business and putting the restriction in a WHERE > clause on the pg_attribute scan. > The query plan that you get for it right now is certainly awful. I agree and try to improve the query as suggested. Here is the new version patch. I think the query plan and cost looks better after applying the patch. Best regards, Hou zj
v2-0001-Ignore-dropped-columns-in-pg_publication_tables.patch
Description: v2-0001-Ignore-dropped-columns-in-pg_publication_tables.patch
QUERY PLAN AFTER APPLYING THE PATCH
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=24.41..92627.20 rows=5000 width=256)
Hash Cond: (c.relnamespace = n.oid)
-> Nested Loop (cost=23.32..137.61 rows=5000 width=200)
-> Seq Scan on pg_publication p (cost=0.00..1.05 rows=5 width=64)
-> Hash Join (cost=23.32..35.97 rows=1000 width=136)
Hash Cond: (gpt.relid = c.oid)
-> Function Scan on pg_get_publication_tables gpt
(cost=0.01..10.01 rows=1000 width=68)
-> Hash (cost=18.14..18.14 rows=414 width=72)
Buckets: 1024 Batches: 1 Memory Usage: 51kB
-> Seq Scan on pg_class c (cost=0.00..18.14 rows=414
width=72)
-> Hash (cost=1.04..1.04 rows=4 width=68)
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.006..0.008 rows=4 loops=1)
SubPlan 1
-> Aggregate (cost=18.48..18.49 rows=1 width=32) (actual
time=0.027..0.027 rows=1 loops=3)
-> Index Scan using pg_attribute_relid_attnum_index on pg_attribute
a (cost=0.28..18.47 rows=1 width=66) (actual time=0.020..0.022 rows=1 loops=3)
Index Cond: ((attrelid = gpt.relid) AND (attnum > 0))
Filter: ((NOT attisdropped) AND ((attnum = ANY
((gpt.attrs)::smallint[])) OR (gpt.attrs IS NULL)))
Rows Removed by Filter: 2
QUERY PLAN BEFORE APPLYING THE PATCH
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=24.41..166633.24 rows=5000 width=256) (actual
time=0.593..0.735 rows=3 loops=1)
Hash Cond: (c.relnamespace = n.oid)
-> Nested Loop (cost=23.32..137.61 rows=5000 width=202) (actual
time=0.492..0.550 rows=3 loops=1)
-> Seq Scan on pg_publication p (cost=0.00..1.05 rows=5 width=64)
(actual time=0.005..0.007 rows=5 loops=1)
-> Hash Join (cost=23.32..35.97 rows=1000 width=138) (actual
time=0.107..0.107 rows=1 loops=5)
Hash Cond: (gpt.relid = c.oid)
-> Function Scan on pg_get_publication_tables gpt
(cost=0.01..10.01 rows=1000 width=68) (actual time=0.025..0.025 rows=1 loops=5)
-> Hash (cost=18.14..18.14 rows=414 width=74) (actual
time=0.392..0.392 rows=414 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 52kB
-> Seq Scan on pg_class c (cost=0.00..18.14 rows=414
width=74) (actual time=0.006..0.243 rows=414 loops=1)
-> Hash (cost=1.04..1.04 rows=4 width=68) (actual time=0.016..0.017 rows=4
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.009..0.011 rows=4 loops=1)
SubPlan 2
-> Aggregate (cost=33.28..33.29 rows=1 width=32) (actual
time=0.049..0.050 rows=1 loops=3)
InitPlan 1 (returns $2)
-> Aggregate (cost=12.51..12.52 rows=1 width=32) (actual
time=0.012..0.013 rows=1 loops=1)
-> Function Scan on generate_series g (cost=0.01..10.01
rows=1000 width=4) (actual time=0.004..0.005 rows=3 loops=1)
-> Sort (cost=20.75..20.76 rows=1 width=66) (actual
time=0.043..0.043 rows=2 loops=3)
Sort Key: a.attnum
Sort Method: quicksort Memory: 25kB
-> Hash Join (cost=0.54..20.69 rows=1 width=66) (actual
time=0.033..0.035 rows=2 loops=3)
Hash Cond: (a.attnum = k.k)
-> Index Scan using pg_attribute_relid_attnum_index on
pg_attribute a (cost=0.28..20.40 rows=7 width=66) (actual time=0.010..0.015
rows=9 loops=3)
Index Cond: (attrelid = gpt.relid)
-> Hash (cost=0.13..0.13 rows=10 width=4) (actual
time=0.011..0.011 rows=2 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Function Scan on unnest k (cost=0.03..0.13
rows=10 width=4) (actual time=0.008..0.009 rows=2 loops=3)
