Re: path toward faster partition pruning

On Mon, Oct 23, 2017 at 3:24 PM, Rajkumar Raghuwanshi
<rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com> wrote:
>
> On Mon, Oct 23, 2017 at 1:12 PM, Amit Langote
> <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>>
>> The compiler I have here (gcc (GCC) 6.2.0) didn't complain like that for
>> this typedef redefinition introduced by the 0002 patch, but it seems that
>> it's not needed anyway, so got rid of that line in the attached updated
>> patch.
>>
>> Fixed one more useless diff in 0002, but no changes in any other patch
>
>
> Thanks for updated patches, I am able to compile it on head.
>
> While testing this, I got an observation, pruning is not scanning default
> partition leading to wrong output. below is test to reproduce this.
>
> create table rp (a int, b varchar) partition by range (a);
> create table rp_p1 partition of rp default;
> create table rp_p2 partition of rp for values from (1) to (10);
> create table rp_p3 partition of rp for values from (10) to (maxvalue);
>
> insert into rp values (-1,'p1');
> insert into rp values (1,'p2');
> insert into rp values (11,'p3');
>
> postgres=# select tableoid::regclass,* from rp;
> tableoid | a | b
> ----------+----+----
> rp_p2 | 1 | p2
> rp_p3 | 11 | p3
> rp_p1 | -1 | p1
> (3 rows)
>
> --with pruning
> postgres=# explain (costs off) select * from rp where a <= 1;
> QUERY PLAN
> --------------------------
> Append
> -> Seq Scan on rp_p2
> Filter: (a <= 1)
> (3 rows)
>
> postgres=# select * from rp where a <= 1;
> a | b
> ---+----
> 1 | p2
> (1 row)
>

I had noticed this and also that this crash:

tprt PARTITION BY RANGE(Col1)
tprt_1 FOR VALUES FROM (1) TO (50001) PARTITION BY RANGE(Col1)
tprt_11 FOR VALUES FROM (1) TO (10000),
tprt_1d DEFAULT
tprt_2 FOR VALUES FROM (50001) TO (100001)

EXPLAIN (COSTS OFF) SELECT * FROM tprt WHERE col1 BETWEEN 20000 AND 70000;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>

--

Beena Emerson

EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company