Message-ID: <2bc1899e624f66e4ca90978540d4e4eeecfe47c6.camel@cybertec.at>
Subject: Re: Partition pruning with joins
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "Ehrenreich, Sigrid" <Ehrenreich@consist.de>,
	"pgsql-performance@lists.postgresql.org"
	 <pgsql-performance@lists.postgresql.org>
Date: Tue, 03 Nov 2020 16:45:21 +0100
In-Reply-To: 
 <AM6PR02MB5287FD9D75C99468721AB52DAB110@AM6PR02MB5287.eurprd02.prod.outlook.com>
References: 
	<AM6PR02MB5287FD9D75C99468721AB52DAB110@AM6PR02MB5287.eurprd02.prod.outlook.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.5 (3.36.5-1.fc32) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Precedence: bulk

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