Re: [HACKERS] path toward faster partition pruning

On 27 March 2018 at 00:42, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> Also, I started thinking that implementing pruning using <> operators with
> a PartitionPruneCombineOp was not such a great idea. That needed us to
> add argexprs and argcmpfns to that struct, which seemed a bit odd. I
> defined a new pruning node type called PartitionPruneStepOpNe, which still
> seems a bit odd, but given that our support for pruning using <> is quite
> specialized, that may be fine.

Seems better

> I added a bunch of hopefully informative comments in partprune.c and for
> the struct definitions of pruning step nodes.

Yes. That looks better.

> Please find attached find a new version.

Thanks. I've made a pass over this and I only have the attached set of
fixes and the following to show for it.

1. Please add more comments in the switch statement in
get_partitions_for_keys_range

2. More an observation than anything else. I see we've lost the
ability to prune range queries on LIST partitions in some cases.

For example:

CREATE TABLE listp (a INT) PARTITION BY LIST(a);
CREATE TABLE listp1_3 PARTITION OF listp FOR VALUES IN(1,3);

EXPLAIN SELECT * FROM listp WHERE a > 1 AND a < 3;

This is just down to the new pruning step design. WHERE we first prune
on "a > 1", which matches listp1_3 due to 3, then binary-AND to the
results of the "a < 3", which matches listp1_3 due to 1. This is a
shame, but probably not the end of the world. Fixing it would likely
mean moving back towards the previous design.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services