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