Re: [HACKERS] path toward faster partition pruning

Thanks Rajkumar.

On 2017/10/27 19:29, Rajkumar Raghuwanshi wrote:
> On Fri, Oct 27, 2017 at 2:41 PM, Amit Langote wrote:
>> 0001: added some new tests
>> 0002: no change
>> 0003: fixed issue that Rajkumar reported (cope with Params properly)
>> 0004: no change
>> 0005: fix the case to prune the default partition when warranted (the
>>       issue reported by Beena)
>>
> 
> Thanks for the updated patch, i am getting server crash with below query.
> 
> CREATE TABLE mp (c1 int, c2 int, c3 int) PARTITION BY LIST(c3);
> CREATE TABLE mp_p1 PARTITION OF mp FOR VALUES IN (10, 20) PARTITION BY
> RANGE(c2);
> CREATE TABLE mp_p1_1 PARTITION OF mp_p1 FOR VALUES FROM (0) TO (200);
> CREATE TABLE mp_p1_2 PARTITION OF mp_p1 FOR VALUES FROM (200) TO (400);
> CREATE TABLE mp_p2 PARTITION OF mp FOR VALUES IN (30, 40) PARTITION BY
> RANGE(c2);
> CREATE TABLE mp_p2_1 PARTITION OF mp_p2 FOR VALUES FROM (0) TO (300);
> CREATE TABLE mp_p2_2 PARTITION OF mp_p2 FOR VALUES FROM (300) TO (600);
> 
> INSERT INTO mp VALUES(10, 100, 10);
> INSERT INTO mp VALUES(20, 200, 20);
> INSERT INTO mp VALUES(21, 150, 30);
> INSERT INTO mp VALUES(30, 200, 40);
> INSERT INTO mp VALUES(31, 300, 30);
> INSERT INTO mp VALUES(40, 400, 40);
> 
> EXPLAIN (COSTS OFF) SELECT tableoid::regclass, * FROM mp WHERE c3 = 40 AND
> c2 < 300;
> 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.

Looks like an update I'd included in the last posted patch (viz. add the
non-root partitioned tables' partition constraint clauses to the list of
clauses used for pruning) exposed a bug in how ScalarArrayOpExpr clauses
are being handled by the new pruning code.  A partitioned list partition's
internal partition constraint clause contains ArrayExpr as the
ScalarArrayOpExpr's right-hand operand, whereas the pruning code thought
there could only ever be a Const holding an ArrayType value.

Fixed in the attached updated patch, along with a new test in 0001 to
cover this case.  Also, made a few tweaks to 0003 and 0005 (moved some
code from the former to the latter) around the handling of ScalarArrayOpExprs.

Thanks,
Amit

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers