Re: [HACKERS] path toward faster partition pruning

On 2018/03/07 20:58, Amit Langote wrote:
> Hi.
>
> On 2018/03/05 17:38, Amit Langote wrote:
>> I'll
>> post an update in a couple of days to report on how that works out.
>
> I'm still working on this and getting most of the tests to pass with the
> new code, but not all of them yet.

Sorry about the delay.

Attached is a significantly revised version of the patch, although I admit
it could still use some work with regard to comments and other cleanup.

The rewrite introduces a notion of PartitionPruneStep nodes based on the
ideas described in [1]. So, instead of aggregating *all* of the pruning
clauses into a PartitionClauseInfo which was hard to serialize into a node
tree and then a PartScanKeyInfo (both of which no longer exist), this
generates a list of nodes. Each node inherits from the base
PartitionPruneStep node type and contains information enough to perform
partition pruning by directly comparing the information with partition
bounds or contains sub-nodes that do. For example, a PartitionPruneStepOp
step contains an integer telling the partitioning operator strategy (such
as various btree operator strategies) and a tuple to compare against
partition bounds stored in the relcache. A PartitionPruneStepCombine step
contains arguments that are in turn pruning steps themselves, which are
separately executed and partition sets obtained thereby are combined using
the specified combineOp.

Also, fixed a bug of the previous design as detailed in [2]. So, with the
patch:

create table lparted (a smallint) partition by list (a);
create table lparted_1 partition of lparted for values in (1);
create table lparted_16384 partition of lparted for values in (16384);

-- all partitions pruned (lparted_16384 wouldn't be pruned by previous
-- patches due to comparison using bogus a partsupfunc)

explain (costs off) select * from lparted where a = 100000000000000;
QUERY PLAN
--------------------------
Result
One-Time Filter: false
(2 rows)

Also,

create table rparted (a smallint) partition by range (a);
create table rparted_1 partition of rparted for values from (1) to (10);
create table rparted_16384 partition of rparted for values from (10) to
(16384);
create table rparted_maxvalue partition of rparted for values from (16384)
to (maxvalue);

-- all partitions except rparted_maxvalue pruned
explain (costs off) select * from rparted where a > 100000000000000;
QUERY PLAN
-------------------------------------------------
Append
-> Seq Scan on rparted_maxvalue
Filter: (a > '100000000000000'::bigint)
(3 rows)

I will continue working on improving the comments / cleaning things up and
post a revised version soon, but until then please look at the attached.

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/CA%2BTgmoahUxagjeNeJTcJkD0rbk%2BmHTXROzWcEd%2BtZ8DuQG83cg%40mail.gmail.com

[2]
https://www.postgresql.org/message-id/CA%2BTgmoYtKitwsFtA4%2B6cdeYGEfnS1%2BOY%2BG%3DUe26fgSzJZx%3DeJg%40mail.gmail.com