Runtime Partition Pruning

Hello all,

Here is the updated patch which is rebased over v10 of Amit Langote's
path towards faster pruning patch [1]/messages/by-id/b8094e71-2c73-ed8e-d8c3-53f232c8c049@lab.ntt.co.jp. It modifies the PartScanKeyInfo
struct to hold expressions which is then evaluated by the executor to
fetch the correct partitions using the function.

The code still chooses the custom plan instead of the generic plan for
the prepared statements. I am working on it. The following output is
after adding a hack in the code forcing selection of generic plan.

postgres=# EXPLAIN EXECUTE prstmt_select(70000);
QUERY PLAN
------------------------------------------------------------------
Append (cost=0.00..1732.25 rows=2 width=8)
-> Seq Scan on tprt_1 (cost=0.00..847.00 rows=16667 width=8)
Filter: ($1 > col1)
-> Seq Scan on tprt_2 (cost=0.00..847.00 rows=16667 width=8)
Filter: ($1 > col1)
(5 rows)

postgres=# EXPLAIN EXECUTE prstmt_select(200000);
QUERY PLAN
------------------------------------------------------------------
Append (cost=0.00..1732.25 rows=3 width=8)
-> Seq Scan on tprt_1 (cost=0.00..847.00 rows=16667 width=8)
Filter: ($1 > col1)
-> Seq Scan on tprt_2 (cost=0.00..847.00 rows=16667 width=8)
Filter: ($1 > col1)
-> Seq Scan on tprt_3 (cost=0.00..38.25 rows=753 width=8)
Filter: ($1 > col1)
(7 rows)

[1]: /messages/by-id/b8094e71-2c73-ed8e-d8c3-53f232c8c049@lab.ntt.co.jp

Tested on commit: 9b9cb3c4534d717c1c95758670198ebbf8a20af2

--

Beena Emerson

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

On Thu, Nov 9, 2017 at 6:18 AM, Beena Emerson <memissemerson@gmail.com> wrote:

The code still chooses the custom plan instead of the generic plan for
the prepared statements. I am working on it.

I don't think it's really the job of this patch to do anything about
that problem.

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

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

On Thu, Nov 9, 2017 at 9:01 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Nov 9, 2017 at 6:18 AM, Beena Emerson <memissemerson@gmail.com> wrote:

The code still chooses the custom plan instead of the generic plan for
the prepared statements. I am working on it.

I don't think it's really the job of this patch to do anything about
that problem.

+1. I think if we really want to do something about plan choice when
partitions are involved that should be done as a separate patch.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

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

On Thu, Nov 9, 2017 at 4:48 PM, Beena Emerson <memissemerson@gmail.com> wrote:

Hello all,

Here is the updated patch which is rebased over v10 of Amit Langote's
path towards faster pruning patch [1]. It modifies the PartScanKeyInfo
struct to hold expressions which is then evaluated by the executor to
fetch the correct partitions using the function.

Hi Beena,

I have started looking into your patch, here few initial comments
for your 0001 patch:

1.
351 + * Evaluate and store the ooutput of ExecInitExpr for each
of the keys.

Typo: ooutput

2.
822 + if (IsA(constexpr, Const) &&is_runtime)
823 + continue;
824 +
825 + if (IsA(constexpr, Param) &&!is_runtime)
826 + continue;
827 +

Add space after '&&'

3.
1095 + * Generally for appendrel we don't fetch the clause from the the

Typo: Double 'the'

4.
272 -/*-------------------------------------------------------------------------
273 + /*-------------------------------------------------------------------------

Unnecessary hunk.

5.
313 + Node *n =
eval_const_expressions_from_list(estate->es_param_list_info, val);
314 +

Crossing 80 column window. Same at line # 323 & 325

6.
315 + keys->eqkeys_datums[i++] = ((Const *) n)->constvalue;

Don’t we need a check for IsA(n, Const) or assert ?

7.
1011 + if (prmList)
1012 + context.boundParams = prmList; /* bound Params */
1013 + else
1014 + context.boundParams = NULL;

No need of prmList null check, context.boundParams = prmList; is enough.

8. It would be nice if you create a separate patch where you are moving
PartScanKeyInfo and exporting function declaration.

9. Could you please add few regression tests, that would help in
review & testing.

10. Could you please rebase your patch against latest "path toward faster
partition pruning" patch by Amit.

Regards,
Amul

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

Hello Amul,

Thank you for reviewing.

On Fri, Nov 10, 2017 at 4:33 PM, amul sul <sulamul@gmail.com> wrote:

On Thu, Nov 9, 2017 at 4:48 PM, Beena Emerson <memissemerson@gmail.com> wrote:

Hello all,

Here is the updated patch which is rebased over v10 of Amit Langote's
path towards faster pruning patch [1]. It modifies the PartScanKeyInfo
struct to hold expressions which is then evaluated by the executor to
fetch the correct partitions using the function.

Hi Beena,

I have started looking into your patch, here few initial comments
for your 0001 patch:

1.
351 + * Evaluate and store the ooutput of ExecInitExpr for each
of the keys.

Typo: ooutput

Corrected.

2.
822 + if (IsA(constexpr, Const) &&is_runtime)
823 + continue;
824 +
825 + if (IsA(constexpr, Param) &&!is_runtime)
826 + continue;
827 +

Add space after '&&'

Done.

3.
1095 + * Generally for appendrel we don't fetch the clause from the the

Typo: Double 'the'

4.
272 -/*-------------------------------------------------------------------------
273 + /*-------------------------------------------------------------------------

Unnecessary hunk.

Removed.

5.
313 + Node *n =
eval_const_expressions_from_list(estate->es_param_list_info, val);
314 +

Crossing 80 column window. Same at line # 323 & 325

Fixed.

6.
315 + keys->eqkeys_datums[i++] = ((Const *) n)->constvalue;

Don’t we need a check for IsA(n, Const) or assert ?

added

7.
1011 + if (prmList)
1012 + context.boundParams = prmList; /* bound Params */
1013 + else
1014 + context.boundParams = NULL;

No need of prmList null check, context.boundParams = prmList; is enough.

8. It would be nice if you create a separate patch where you are moving
PartScanKeyInfo and exporting function declaration.

This is in 0001.

9. Could you please add few regression tests, that would help in
review & testing.

I will make a seperate regression patch and submit soon.

10. Could you please rebase your patch against latest "path toward faster
partition pruning" patch by Amit.

The following is rebased over v11 Amit's patch [1]/messages/by-id/62d21a7b-fea9-f2d7-c33a-8caa12eca612@lab.ntt.co.jp

[1]: /messages/by-id/62d21a7b-fea9-f2d7-c33a-8caa12eca612@lab.ntt.co.jp

--

Beena Emerson

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

PFA the updated patches.

On Tue, Nov 14, 2017 at 11:45 AM, Beena Emerson <memissemerson@gmail.com> wrote:

Hello Amul,

Thank you for reviewing.

On Fri, Nov 10, 2017 at 4:33 PM, amul sul <sulamul@gmail.com> wrote:

On Thu, Nov 9, 2017 at 4:48 PM, Beena Emerson <memissemerson@gmail.com> wrote:

Hello all,

Here is the updated patch which is rebased over v10 of Amit Langote's
path towards faster pruning patch [1]. It modifies the PartScanKeyInfo
struct to hold expressions which is then evaluated by the executor to
fetch the correct partitions using the function.

Hi Beena,

I have started looking into your patch, here few initial comments
for your 0001 patch:

1.
351 + * Evaluate and store the ooutput of ExecInitExpr for each
of the keys.

Typo: ooutput

Corrected.

2.
822 + if (IsA(constexpr, Const) &&is_runtime)
823 + continue;
824 +
825 + if (IsA(constexpr, Param) &&!is_runtime)
826 + continue;
827 +

Add space after '&&'

Done.

3.
1095 + * Generally for appendrel we don't fetch the clause from the the

Typo: Double 'the'

4.
272 -/*-------------------------------------------------------------------------
273 + /*-------------------------------------------------------------------------

Unnecessary hunk.

Removed.

5.
313 + Node *n =
eval_const_expressions_from_list(estate->es_param_list_info, val);
314 +

Crossing 80 column window. Same at line # 323 & 325

Fixed.

6.
315 + keys->eqkeys_datums[i++] = ((Const *) n)->constvalue;

Don’t we need a check for IsA(n, Const) or assert ?

added

7.
1011 + if (prmList)
1012 + context.boundParams = prmList; /* bound Params */
1013 + else
1014 + context.boundParams = NULL;

No need of prmList null check, context.boundParams = prmList; is enough.

8. It would be nice if you create a separate patch where you are moving
PartScanKeyInfo and exporting function declaration.

This is in 0001.

9. Could you please add few regression tests, that would help in
review & testing.

I will make a seperate regression patch and submit soon.

10. Could you please rebase your patch against latest "path toward faster
partition pruning" patch by Amit.

The following is rebased over v11 Amit's patch [1]

[1] /messages/by-id/62d21a7b-fea9-f2d7-c33a-8caa12eca612@lab.ntt.co.jp

--

Beena Emerson

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

On Tue, Nov 14, 2017 at 11:46 AM, Beena Emerson <memissemerson@gmail.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.

Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation

On 14 November 2017 at 19:16, Beena Emerson <memissemerson@gmail.com> wrote:

PFA the updated patches.

Hi Beena,

Thanks for working on this. I've had a look at the patch to try to
understand how it is working. I found it a bit surprising that the
code assumes it can rely on the order of Append->appendplans matching
what's needed based on the return value of get_partitions_for_keys().

I tried using the following to break this:

drop table if exists ab;
create table ab (a int not null, b int not null) partition by list(a);
create table ab_a2 partition of ab for values in(2) partition by list (b);
create table ab_a2_b1 partition of ab_a2 for values in (1);
create table ab_a2_b2 partition of ab_a2 for values in (2);
create table ab_a2_b3 partition of ab_a2 for values in (3);

create table ab_a1 partition of ab for values in(1) partition by list (b);
create table ab_a1_b1 partition of ab_a1 for values in (1);
create table ab_a1_b2 partition of ab_a1 for values in (2);
create table ab_a1_b3 partition of ab_a1 for values in (3);
create table ab_a3 partition of ab for values in(3) partition by list (b);
create table ab_a3_b1 partition of ab_a3 for values in (1);
create table ab_a3_b2 partition of ab_a3 for values in (2);
create table ab_a3_b3 partition of ab_a3 for values in (3);

prepare ab_q1 (int, int) as select * from ab where a = $1 and b = $2;

explain execute ab_q1 (2,3);
explain execute ab_q1 (2,3);
explain execute ab_q1 (2,3);
explain execute ab_q1 (2,3);

postgres=# explain execute ab_q1 (2,3);
QUERY PLAN
---------------------------------------------------------------
Append (cost=0.00..43.90 rows=1 width=8)
-> Seq Scan on ab_a2_b3 (cost=0.00..43.90 rows=1 width=8)
Filter: ((a = 2) AND (b = 3))
(3 rows)

postgres=# explain execute ab_q1 (2,3);
QUERY PLAN
---------------------------------------------------------------
Append (cost=0.00..395.10 rows=1 width=8)
-> Seq Scan on ab_a1_b2 (cost=0.00..43.90 rows=1 width=8)
<--------- wrong partition
Filter: ((a = $1) AND (b = $2))
(3 rows)

As soon as we hit the generic plan the wrong partition is selected

I think to do this you're going to have to store some sort of array
that maps the partition index to the subpath in the Append node so you
can correctly identify the subpath based on what you're getting back
from get_partitions_for_keys(). Perhaps what you had worked previously
when we were not returning a Bitmapset with that function.

Once you've got that design worked out I can take another look at this.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 15 November 2017 at 01:57, David Rowley <david.rowley@2ndquadrant.com> wrote:

I think to do this you're going to have to store some sort of array
that maps the partition index to the subpath in the Append node so you
can correctly identify the subpath based on what you're getting back
from get_partitions_for_keys(). Perhaps what you had worked previously
when we were not returning a Bitmapset with that function.

Once you've got that design worked out I can take another look at this.

I think this is a bit more broken than I originally mentioned above.
The code you have at the moment assumes there will be just a single
partitioned table in the hierarchy. Remember that complex partitioned
hierarchies will be flattened during set_append_rel_pathlist(), so
there may be multiple partitioned relations to search for.

A more simple way to break the patch is to have some constants in the
query to eliminate some of the partitions during planning, leaving
just a few to be eliminated during execution.

Something like:

deallocate ab_q1;
drop table if exists ab;
create table ab (a int not null, b int not null) partition by list(a);
create table ab_a1 partition of ab for values in (1);
create table ab_a2 partition of ab for values in (2);
create table ab_a3 partition of ab for values in (3);
create table ab_a4 partition of ab for values in (4);
create table ab_a5 partition of ab for values in (5);
create table ab_a6 partition of ab for values in (6);
create table ab_a7 partition of ab for values in (7);
create table ab_a8 partition of ab for values in (8);
create table ab_a9 partition of ab for values in (9);
create table ab_a10 partition of ab for values in (10);

prepare ab_q1 (int) as select * from ab where a between 4 and 5 and a = $1;

explain execute ab_q1 (4);
explain execute ab_q1 (4);
explain execute ab_q1 (4);
explain execute ab_q1 (4);
explain execute ab_q1 (4);

explain execute ab_q1 (4); -- TRAP: FailedAssertion("!(n <
list->length)", File: "src/backend/nodes/list.c", Line: 392)

So some sort of hierarchical structure of the partition hierarchy
would need to be stored in the Append node and then you'd need to
search at each level, and then somehow match the results up to the
subpaths that you have in the Append. Although, I'm still not sure
this is the best way to go about this.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 2017/11/15 8:13, David Rowley wrote:

So some sort of hierarchical structure of the partition hierarchy
would need to be stored in the Append node and then you'd need to
search at each level, and then somehow match the results up to the
subpaths that you have in the Append. Although, I'm still not sure
this is the best way to go about this.

I think we should try to use PartitionDispatch stuff for this somehow,
just like get_partition_for_tuple() does, although I haven't thought very
hard whether that infrastructure would be useful as is.

Thanks,
Amit

Hello Rajkumar,

On Tue, Nov 14, 2017 at 2:22 PM, Rajkumar Raghuwanshi
<rajkumar.raghuwanshi@enterprisedb.com> wrote:

On Tue, Nov 14, 2017 at 11:46 AM, Beena Emerson <memissemerson@gmail.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.

Thanks for your review. I am looking into it.

--

Beena Emerson

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

Hello David,

Thank you for reviewing.

On Wed, Nov 15, 2017 at 4:43 AM, David Rowley
<david.rowley@2ndquadrant.com> wrote:

On 15 November 2017 at 01:57, David Rowley <david.rowley@2ndquadrant.com> wrote:

I think to do this you're going to have to store some sort of array
that maps the partition index to the subpath in the Append node so you
can correctly identify the subpath based on what you're getting back
from get_partitions_for_keys(). Perhaps what you had worked previously
when we were not returning a Bitmapset with that function.

Once you've got that design worked out I can take another look at this.

I think this is a bit more broken than I originally mentioned above.
The code you have at the moment assumes there will be just a single
partitioned table in the hierarchy. Remember that complex partitioned
hierarchies will be flattened during set_append_rel_pathlist(), so
there may be multiple partitioned relations to search for.

A more simple way to break the patch is to have some constants in the
query to eliminate some of the partitions during planning, leaving
just a few to be eliminated during execution.

Something like:

deallocate ab_q1;
drop table if exists ab;
create table ab (a int not null, b int not null) partition by list(a);
create table ab_a1 partition of ab for values in (1);
create table ab_a2 partition of ab for values in (2);
create table ab_a3 partition of ab for values in (3);
create table ab_a4 partition of ab for values in (4);
create table ab_a5 partition of ab for values in (5);
create table ab_a6 partition of ab for values in (6);
create table ab_a7 partition of ab for values in (7);
create table ab_a8 partition of ab for values in (8);
create table ab_a9 partition of ab for values in (9);
create table ab_a10 partition of ab for values in (10);

prepare ab_q1 (int) as select * from ab where a between 4 and 5 and a = $1;

explain execute ab_q1 (4);
explain execute ab_q1 (4);
explain execute ab_q1 (4);
explain execute ab_q1 (4);
explain execute ab_q1 (4);

explain execute ab_q1 (4); -- TRAP: FailedAssertion("!(n <
list->length)", File: "src/backend/nodes/list.c", Line: 392)

So some sort of hierarchical structure of the partition hierarchy
would need to be stored in the Append node and then you'd need to
search at each level, and then somehow match the results up to the
subpaths that you have in the Append. Although, I'm still not sure
this is the best way to go about this.

Thank you for your suggestion. I am looking into this and will post a
patch soon.

--

Beena Emerson

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

On Wed, Nov 15, 2017 at 4:43 AM, David Rowley
<david.rowley@2ndquadrant.com> wrote:

On 15 November 2017 at 01:57, David Rowley <david.rowley@2ndquadrant.com> wrote:

I think to do this you're going to have to store some sort of array
that maps the partition index to the subpath in the Append node so you
can correctly identify the subpath based on what you're getting back
from get_partitions_for_keys(). Perhaps what you had worked previously
when we were not returning a Bitmapset with that function.

Once you've got that design worked out I can take another look at this.

So some sort of hierarchical structure of the partition hierarchy
would need to be stored in the Append node and then you'd need to
search at each level, and then somehow match the results up to the
subpaths that you have in the Append. Although, I'm still not sure
this is the best way to go about this.

Instead of hierarchical structure can’t we maintain an array (one
entry per partition), and whenever any leaf partition’s subpath
is added to the append rel (in function set_append_rel_pathlist) we
can set that subpath number in corresponding array index.

And, later we can add some wrapper over get_partitions_for_key such
that it can recursively traverse the non-leaf partitions (something
like get_partition_for_tuple does as Amit mentioned.). And, ultimately
gives the output as a list of leaf partition's indexes. Then we can
find the sub-plan number by looking into the array.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

On Wed, Nov 15, 2017 at 3:53 PM, Beena Emerson <memissemerson@gmail.com> wrote:

Thank you for your suggestion. I am looking into this and will post a
patch soon.

It has been two weeks since this update and no new patch has showed
up. I am marking the patch as returned with feedback. If you can
produce a new version, of course feel free to post it and register a
new entry in the CF app.
--
Michael

Hello,

PFA the new version of the patch which can be applied over v11 patches
of Amit Langote [1]/messages/by-id/62d21a7b-fea9-f2d7-c33a-8caa12eca612@lab.ntt.co.jp. The patch has been completely modified and the
0001 patch of previous series is no longer required. As mentioned
above, I have used the PartitionDispatchInfo and an array to which
holds the actual subplan index.

This one addresses the problems mentioned above but still needs to be
thoroughly tested. I will soon post a new patch rebased over Amit's
v13 patches[2]/messages/by-id/df609168-b7fd-4c0b-e9b2-6e398d411e27@lab.ntt.co.jp soon with additional code comments.

[1]: /messages/by-id/62d21a7b-fea9-f2d7-c33a-8caa12eca612@lab.ntt.co.jp
[2]: /messages/by-id/df609168-b7fd-4c0b-e9b2-6e398d411e27@lab.ntt.co.jp

--

Beena Emerson

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

Hello Rajkumar,

On Tue, Nov 14, 2017 at 2:22 PM, Rajkumar Raghuwanshi
<rajkumar.raghuwanshi@enterprisedb.com> wrote:

On Tue, Nov 14, 2017 at 11:46 AM, Beena Emerson <memissemerson@gmail.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

On Tue, Nov 14, 2017 at 6:27 PM, David Rowley
<david.rowley@2ndquadrant.com> wrote:

On 14 November 2017 at 19:16, Beena Emerson <memissemerson@gmail.com> wrote:

PFA the updated patches.

Hi Beena,

Thanks for working on this. I've had a look at the patch to try to
understand how it is working. I found it a bit surprising that the
code assumes it can rely on the order of Append->appendplans matching
what's needed based on the return value of get_partitions_for_keys().

I tried using the following to break this:

drop table if exists ab;
create table ab (a int not null, b int not null) partition by list(a);
create table ab_a2 partition of ab for values in(2) partition by list (b);
create table ab_a2_b1 partition of ab_a2 for values in (1);
create table ab_a2_b2 partition of ab_a2 for values in (2);
create table ab_a2_b3 partition of ab_a2 for values in (3);

create table ab_a1 partition of ab for values in(1) partition by list (b);
create table ab_a1_b1 partition of ab_a1 for values in (1);
create table ab_a1_b2 partition of ab_a1 for values in (2);
create table ab_a1_b3 partition of ab_a1 for values in (3);
create table ab_a3 partition of ab for values in(3) partition by list (b);
create table ab_a3_b1 partition of ab_a3 for values in (1);
create table ab_a3_b2 partition of ab_a3 for values in (2);
create table ab_a3_b3 partition of ab_a3 for values in (3);

prepare ab_q1 (int, int) as select * from ab where a = $1 and b = $2;

explain execute ab_q1 (2,3);
explain execute ab_q1 (2,3);
explain execute ab_q1 (2,3);
explain execute ab_q1 (2,3);

postgres=# explain execute ab_q1 (2,3);
QUERY PLAN
---------------------------------------------------------------
Append (cost=0.00..43.90 rows=1 width=8)
-> Seq Scan on ab_a2_b3 (cost=0.00..43.90 rows=1 width=8)
Filter: ((a = 2) AND (b = 3))
(3 rows)

postgres=# explain execute ab_q1 (2,3);
QUERY PLAN
---------------------------------------------------------------
Append (cost=0.00..395.10 rows=1 width=8)
-> Seq Scan on ab_a1_b2 (cost=0.00..43.90 rows=1 width=8)
<--------- wrong partition
Filter: ((a = $1) AND (b = $2))
(3 rows)

With the new patch, the output is:
postgres=# explain execute ab_q1 (2,3);
QUERY PLAN
---------------------------------------------------------------
Append (cost=0.00..43.90 rows=1 width=8)
-> Seq Scan on ab_a2_b3 (cost=0.00..43.90 rows=1 width=8)
Filter: ((a = 2) AND (b = 3))
(3 rows)

postgres=# explain execute ab_q1 (2,3);
QUERY PLAN
---------------------------------------------------------------
Append (cost=0.00..395.10 rows=4 width=8)
-> Seq Scan on ab_a2_b3 (cost=0.00..43.90 rows=1 width=8)
Filter: ((a = $1) AND (b = $2))
(3 rows)

--

Beena Emerson

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

Hello,

On Wed, Nov 15, 2017 at 4:43 AM, David Rowley
<david.rowley@2ndquadrant.com> wrote:

On 15 November 2017 at 01:57, David Rowley <david.rowley@2ndquadrant.com> wrote:

I think to do this you're going to have to store some sort of array
that maps the partition index to the subpath in the Append node so you
can correctly identify the subpath based on what you're getting back
from get_partitions_for_keys(). Perhaps what you had worked previously
when we were not returning a Bitmapset with that function.

Once you've got that design worked out I can take another look at this.

I think this is a bit more broken than I originally mentioned above.
The code you have at the moment assumes there will be just a single
partitioned table in the hierarchy. Remember that complex partitioned
hierarchies will be flattened during set_append_rel_pathlist(), so
there may be multiple partitioned relations to search for.

A more simple way to break the patch is to have some constants in the
query to eliminate some of the partitions during planning, leaving
just a few to be eliminated during execution.

Something like:

deallocate ab_q1;
drop table if exists ab;
create table ab (a int not null, b int not null) partition by list(a);
create table ab_a1 partition of ab for values in (1);
create table ab_a2 partition of ab for values in (2);
create table ab_a3 partition of ab for values in (3);
create table ab_a4 partition of ab for values in (4);
create table ab_a5 partition of ab for values in (5);
create table ab_a6 partition of ab for values in (6);
create table ab_a7 partition of ab for values in (7);
create table ab_a8 partition of ab for values in (8);
create table ab_a9 partition of ab for values in (9);
create table ab_a10 partition of ab for values in (10);

prepare ab_q1 (int) as select * from ab where a between 4 and 5 and a = $1;

explain execute ab_q1 (4);
explain execute ab_q1 (4);
explain execute ab_q1 (4);
explain execute ab_q1 (4);
explain execute ab_q1 (4);

explain execute ab_q1 (4); -- TRAP: FailedAssertion("!(n <
list->length)", File: "src/backend/nodes/list.c", Line: 392)

This is handled in the new patch.

postgres=# explain execute ab_q1 (4);
QUERY PLAN
------------------------------------------------------------
Append (cost=0.00..49.55 rows=1 width=8)
-> Seq Scan on ab_a4 (cost=0.00..49.55 rows=1 width=8)
Filter: ((a >= 4) AND (a <= 5) AND (a = 4))
(3 rows)

postgres=# explain execute ab_q1 (4);
QUERY PLAN
------------------------------------------------------------
Append (cost=0.00..99.10 rows=1 width=8)
-> Seq Scan on ab_a4 (cost=0.00..49.55 rows=1 width=8)
Filter: ((a >= 4) AND (a <= 5) AND (a = $1))
(3 rows)

--

Beena Emerson

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

Hello,

On Wed, Nov 29, 2017 at 7:11 AM, Michael Paquier
<michael.paquier@gmail.com> wrote:

On Wed, Nov 15, 2017 at 3:53 PM, Beena Emerson <memissemerson@gmail.com> wrote:

Thank you for your suggestion. I am looking into this and will post a
patch soon.

It has been two weeks since this update and no new patch has showed
up. I am marking the patch as returned with feedback. If you can
produce a new version, of course feel free to post it and register a
new entry in the CF app.

I have moved it to next CF with status needs review.

--

Beena Emerson

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