On Tue, 2020-11-03 at 13:20 +0000, Ehrenreich, Sigrid wrote: > I would like to join a partitioned table and have the joined columns in the where clause to be used for partition pruning. > From some readings in the internet, I conclude that this was not possible in v12. I hoped for the > “improvements in partition pruning” in v13, but it seems to me, that it is still not possible, or is it and I am missing something here? > > My testcase: > create table fact (part_key integer) partition by range (part_key); > create table fact_100 partition of fact for values from (1) to (101); > create table fact_200 partition of fact for values from (101) to (201); > > insert into fact (part_key) select floor(random()*100+1) from generate_series(1,10000); > insert into fact (part_key) select floor(random()*100+101) from generate_series(1,10000); > > create table dim as (select distinct part_key from fact); > create unique index on dim (part_key); > > analyze fact; > analyze dim; > > -- Statement > explain SELECT > count(*) > FROM > dim INNER JOIN fact ON (dim.part_key=fact.part_key) > WHERE dim.part_key >= 110 and dim.part_key <= 160; > > Plan shows me, that all partitions are scanned: > Aggregate (cost=461.00..461.01 rows=1 width=8) > -> Hash Join (cost=4.64..448.25 rows=5100 width=0) > Hash Cond: (fact.part_key = dim.part_key) > -> Append (cost=0.00..390.00 rows=20000 width=4) > -> Seq Scan on fact_100 fact_1 (cost=0.00..145.00 rows=10000 width=4) ⇐==== unnecessarily scanned > -> Seq Scan on fact_200 fact_2 (cost=0.00..145.00 rows=10000 width=4) > -> Hash (cost=4.00..4.00 rows=51 width=4) > -> Seq Scan on dim (cost=0.00..4.00 rows=51 width=4) > Filter: ((part_key >= 110) AND (part_key <= 160)) One thing you could try is to partition "dim" just like "fact" and set "enable_partitionwise_join = on". I didn't test it, but that might do the trick. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
