RE: Partition pruning with joins

Hi Laurenz,

That trick did it!
Great idea!

I have tested it not only successfully with my little testcase, but with our real world data and it works there as well.

Thanks a lot for your help!

Regards,
Sigrid

-----Original Message-----
From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
Sent: Tuesday, November 3, 2020 4:45 PM
To: Ehrenreich, Sigrid <Ehrenreich(at)consist(dot)de>; pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Re: Partition pruning with joins

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