Re: [HACKERS] Runtime Partition Pruning

Hello Rajkumar,

On Tue, Nov 14, 2017 at 2:22 PM, Rajkumar Raghuwanshi
<rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com> wrote:
> On Tue, Nov 14, 2017 at 11:46 AM, Beena Emerson <memissemerson(at)gmail(dot)com>
> wrote:
>>
>> PFA the updated patches.
>
>
> Hi,
>
> I have started testing this along with fast pruning. It is crashing for sql
> with subqueries.
> one to test case is given below.
>
> CREATE TABLE prun_test_part (empno int, sal int, deptno 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 (10,90,10);
> INSERT INTO prun_test_part VALUES (11,100,10);
> INSERT INTO prun_test_part VALUES (20,110,20);
> INSERT INTO prun_test_part VALUES (21,200,20);
> INSERT INTO prun_test_part VALUES (30,210,30);
> INSERT INTO prun_test_part VALUES (31,300,30);
> INSERT INTO prun_test_part VALUES (50,310,20);
>
> explain (costs off)
> SELECT * FROM prun_test_part WHERE sal < (SELECT sal FROM prun_test_part
> WHERE sal = 50);
> 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.
>

With the new patch, the output is as follows:

postgres=# explain (costs off)
SELECT * FROM prun_test_part WHERE sal < (SELECT sal FROM
prun_test_part WHERE sal = 50);
QUERY PLAN
-----------------------------------------------------------------
Append
InitPlan 1 (returns $0)
-> Append
-> Seq Scan on prun_test_part_p1 prun_test_part_p1_1
Filter: (sal = 50)
-> Seq Scan on prun_test_part_p1
Filter: (sal < $0)
(7 rows)

--

Beena Emerson

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