Re: [HACKERS] path toward faster partition pruning

From: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
To: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>, Rajkumar Raghuwanshi <rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com>, Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>, Jesper Pedersen <jesper(dot)pedersen(at)redhat(dot)com>, Amit Langote <amitlangote09(at)gmail(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>, Beena Emerson <memissemerson(at)gmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] path toward faster partition pruning
Date: 2018-03-27 10:51:56
Message-ID: CAKJS1f887XimCYzAVE-3VptM+tSpX8Bxo0w-tnAshKhhPck2KQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v40_drowley_fixes.patch application/octet-stream 12.0 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Alexander Korotkov 2018-03-27 11:01:21 Re: [HACKERS] GUC for cleanup indexes threshold.
Previous Message Simon Riggs 2018-03-27 10:46:55 Re: [HACKERS] MERGE SQL Statement for PG11