Re: [HACKERS] Runtime Partition Pruning

Hello David,

On Wed, Dec 27, 2017 at 8:36 AM, David Rowley
<david.rowley@2ndquadrant.com> wrote:
> Hi,
>
> Please find attached my 4th version this patch.

Thanks for the patch
>
> This is now based on v17 of Amit's faster partition pruning patch [1].
> It also now includes Beena's tests which I've done some mostly
> cosmetic changes to.
>
> I've also fixed a few bugs, one in a case where I was not properly
> handling zero matching partitions in nodeAppend.c.
>
> Another change I've made is to now perform the partition pruning at
> run-time using a new memory context that's reset each time we
> redetermine the matching partitions. This was required since we're
> calling a planner function which might not be too careful about
> pfreeing memory it allocates. A test case I was running before making
> this change ended out failing to palloc memory due to OOM.
>
> I've not done anything about reducing the cost of the Append path when
> runtime pruning is enabled. I'm still thinking over the best way to
> handle that.
>

I think you are testing without asserts

The following assert fails: src/backend/optimizer/plan/setrefs.c :
set_plan_refs: ln 921
Assert(splan->plan.qual == NIL);
Append node now has runtime partition quals.

Also since the valid subplans are set in ExecInitAppend, the queries
with Init Plans do not work. I had moved it to ExecAppend in my patch
to handle the InitPlans as well.

DROP TABLE IF EXISTS prun_test_part;
CREATE TABLE prun_test_part (sal int) PARTITION BY RANGE(sal);
CREATE TABLE prun_test_part_p1 PARTITION OF prun_test_part FOR VALUES
FROM (0) TO (100);
CREATE TABLE prun_test_part_p2 PARTITION OF prun_test_part FOR VALUES
FROM (100) TO (200);
CREATE TABLE prun_test_part_p3 PARTITION OF prun_test_part FOR VALUES
FROM (200) TO (300);
CREATE TABLE prun_test_part_p4 PARTITION OF prun_test_part FOR VALUES
FROM (300) TO (400);

INSERT INTO prun_test_part VALUES (90), (100), (110), (200), (210),
(300), (310);
=# explain (analyze, costs off, summary off, timing off) SELECT * FROM
prun_test_part WHERE sal < (SELECT sal FROM prun_test_part WHERE sal =
200);
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