Re: path toward faster partition pruning

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Rajkumar Raghuwanshi <rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com>
Cc: Beena Emerson <memissemerson(at)gmail(dot)com>, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: path toward faster partition pruning
Date: 2017-10-30 05:55:48
Message-ID: 3d033589-a2dd-3e32-a696-3f96991b3df6@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
0001-Add-new-tests-for-partition-pruning-v7.patch text/plain 45.8 KB
0002-Planner-side-changes-for-partition-pruning-v7.patch text/plain 37.3 KB
0003-Implement-get_partitions_from_clauses-v7.patch text/plain 34.4 KB
0004-Some-interface-changes-for-partition_bound_-cmp-bsea-v7.patch text/plain 10.1 KB
0005-Implement-get_partitions_for_keys-v7.patch text/plain 21.5 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Masahiko Sawada 2017-10-30 06:17:59 Re: Moving relation extension locks out of heavyweight lock manager
Previous Message Ashutosh Bapat 2017-10-30 05:29:03 Re: Removing [Merge]Append nodes which contain a single subpath