Hi,
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))
I know, that I could get rid of this problem, by rewriting the query to include
the partitioned table in the where clause like this:
WHERE fact.part_key >= 210 and fact.part_key <= 260
Partition pruning happens very nicely then.
Unfortunately this is not an option for us, because the code in our case is
generated by some third party software (sigh).
Do you have any suggestions, what else I could do? (Or maybe you could add it
as a new feature for v14 😉)?
Regards,
Sigrid
