From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Ehrenreich, Sigrid <Ehrenreich@consist.de>
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: Partition pruning with joins
Date: Tue, 03 Nov 2020 16:45:21 +0100
Message-ID: <2bc1899e624f66e4ca90978540d4e4eeecfe47c6.camel@cybertec.at> (raw)
In-Reply-To: <AM6PR02MB5287FD9D75C99468721AB52DAB110@AM6PR02MB5287.eurprd02.prod.outlook.com>
References: <AM6PR02MB5287FD9D75C99468721AB52DAB110@AM6PR02MB5287.eurprd02.prod.outlook.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, Ehrenreich@consist.de, pgsql-performance@lists.postgresql.org
Subject: Re: Partition pruning with joins
In-Reply-To: <2bc1899e624f66e4ca90978540d4e4eeecfe47c6.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox