Re: Partition pruning with joins

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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






[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux