Re: [HACKERS] path toward faster partition pruning

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, David Rowley <david(dot)rowley(at)2ndquadrant(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 17:54:35
Message-ID: 20180327175435.o7npw2x2lrutephf@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

Amit Langote wrote:

> [Jesper] also pointed out a case with a
> list-partitioned table where pruning doesn't a produce a result as one
> would expect and what constraint exclusion would produce.
>
> create table lp (a char) partition by list (a);
> create table lp_ad partition of lp for values in ('a', 'd');
> create table lp_bc partition of lp for values in ('b', 'c');
> create table lp_default partition of lp default;
> explain (costs off) select * from lp where a > 'a' and a < 'd';
> QUERY PLAN
> -----------------------------------------------------------
> Append
> -> Seq Scan on lp_ad
> Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar))
> -> Seq Scan on lp_bc
> Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar))
> -> Seq Scan on lp_default
> Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar))
> (7 rows)
>
> One would expect that lp_ad is not scanned.

One would? I, for one, wouldn't particularly sweat over this case TBH.
It seems a pretty silly case. If this works for "a <> 'a' and a <> 'd'"
(I mean, lp_ad is pruned for that qual), that sounds sufficient to me.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2018-03-27 18:02:10 Re: Changing WAL Header to reduce contention during ReserveXLogInsertLocation()
Previous Message Robert Haas 2018-03-27 17:48:57 Re: Changing WAL Header to reduce contention during ReserveXLogInsertLocation()