Runtime Partition Pruning

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

Runtime Partition Pruning

Beena Emerson
I have been working on implementing the runtime partition pruning
which would increase the performance of queries involving partitioned
table to a great extent.

PFA the POC which can be applied over Amit's patch for faster
partition pruning [1] and Dilip's refactor patch [2] on commit
2c74e6c1dcc5002fa8b822e5757f6c95d899fb7a.

[1] https://www.postgresql.org/message-id/e02923ea-a117-a6ad-6a3e-ea5e1ba41ece%40lab.ntt.co.jp

[2] https://www.postgresql.org/message-id/CAFiTN-tGnQzF_4QtbOHT-3hE%3DOvNaMfbbeRxa4UY0CQyF0G8gQ%40mail.gmail.com

There were a couple of things that need improvement/opinion:
In get_rel_partition_info, we store minop and maxop for each partition
key. For the equality case, which is most common, both would store the
same value. We could make it better by storing equal (bound, bound,
....) instead repeating the same values.

get_partitions_for_keys currently returns the list of partitions valid
for the given keys but for a table with many partitions this list
would be very long so maybe for range qual ( key > a & key < b ) we
could only store the min and max partition number and increment
as_whichplan by 1 till we reach max partition number. For
non-continuous partitions, we would still need the list.

Currently, the partitions numbers are recalculated whenever the
ChgParam is set, This can be optimised by skipping this step when only
a non-partition key column has changed; reusing the existing
partitions selected.

Others:
- better handling of multiple key
- allow use of expression in the quals.
- To use min_incl, max_incl properly in get_partitions_for_keys.
- pruning during function calls.


Currently with patch, during NestLoop:
Nested Loop
-> SeqScan tbl1
-> Append
  -> Index Scan p01
  -> Index Scan p02
  -> Index Scan p03

For each tuple from tbl1, only the relevant partition (p01or p02 or
p03) will be scanned.


--- Prepared Statement Behaviour with patch---

Table Descritpion:
                                   Table "public.tprt"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats
target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
 col1   | integer |           |          |         | plain   |              |
 col2   | integer |           |          |         | plain   |              |
Partition key: RANGE (col1)
Partitions: tprt_1 FOR VALUES FROM (1) TO (50001),
            tprt_2 FOR VALUES FROM (50001) TO (100001),
            tprt_3 FOR VALUES FROM (100001) TO (200001)

EXPLAIN EXECUTE prstmt_select(15);

                            QUERY PLAN
------------------------------------------------------------------
 Append  (cost=0.00..1736.55 rows=1 width=8)
   ->  Seq Scan on tprt_1  (cost=0.00..849.15 rows=16724 width=8)
         Filter: (col1 < $1)
(3 rows)

EXPLAIN EXECUTE prstmt_select(60000);
                            QUERY PLAN
------------------------------------------------------------------
 Append  (cost=0.00..1736.55 rows=2 width=8)
   ->  Seq Scan on tprt_1  (cost=0.00..849.15 rows=16724 width=8)
         Filter: (col1 < $1)
   ->  Seq Scan on tprt_2  (cost=0.00..849.15 rows=16724 width=8)
         Filter: (col1 < $1)
(5 rows)


--

Beena Emerson

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


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

0001-POC-Implement-runtime-partiton-pruning.patch (42K) Download Attachment
Beena Emerson
Reply | Threaded
Open this post in threaded view
|

Re: Runtime Partition Pruning

Beena Emerson
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.

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] https://www.postgresql.org/message-id/b8094e71-2c73-ed8e-d8c3-53f232c8c049%40lab.ntt.co.jp

Tested on commit: 9b9cb3c4534d717c1c95758670198ebbf8a20af2

--

Beena Emerson

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


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

0001-Implement-runtime-partiton-pruning.patch (57K) Download Attachment
Beena Emerson
Reply | Threaded
Open this post in threaded view
|

Re: Runtime Partition Pruning

Robert Haas
On Thu, Nov 9, 2017 at 6:18 AM, Beena Emerson <[hidden email]> 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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Runtime Partition Pruning

akapila
On Thu, Nov 9, 2017 at 9:01 PM, Robert Haas <[hidden email]> wrote:
> On Thu, Nov 9, 2017 at 6:18 AM, Beena Emerson <[hidden email]> 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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Runtime Partition Pruning

amul sul
In reply to this post by Beena Emerson
On Thu, Nov 9, 2017 at 4:48 PM, Beena Emerson <[hidden email]> 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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Runtime Partition Pruning

Beena Emerson
Hello Amul,

Thank you for reviewing.

On Fri, Nov 10, 2017 at 4:33 PM, amul sul <[hidden email]> wrote:

> On Thu, Nov 9, 2017 at 4:48 PM, Beena Emerson <[hidden email]> 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] https://www.postgresql.org/message-id/62d21a7b-fea9-f2d7-c33a-8caa12eca612%40lab.ntt.co.jp


--

Beena Emerson

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

Beena Emerson
Reply | Threaded
Open this post in threaded view
|

Re: Runtime Partition Pruning

Beena Emerson
PFA the updated patches.

On Tue, Nov 14, 2017 at 11:45 AM, Beena Emerson <[hidden email]> wrote:

> Hello Amul,
>
> Thank you for reviewing.
>
> On Fri, Nov 10, 2017 at 4:33 PM, amul sul <[hidden email]> wrote:
>> On Thu, Nov 9, 2017 at 4:48 PM, Beena Emerson <[hidden email]> 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] https://www.postgresql.org/message-id/62d21a7b-fea9-f2d7-c33a-8caa12eca612%40lab.ntt.co.jp
>

--

Beena Emerson

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

0002-Implement-runtime-partiton-pruning_v2.patch (50K) Download Attachment
0001-Refactor-functions-and-structs-required-for-runtime_v2.patch (10K) Download Attachment
Beena Emerson
Reply | Threaded
Open this post in threaded view
|

Re: Runtime Partition Pruning

rajkumar.raghuwanshi
On Tue, Nov 14, 2017 at 11:46 AM, Beena Emerson <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: Runtime Partition Pruning

David Rowley-3
In reply to this post by Beena Emerson
On 14 November 2017 at 19:16, Beena Emerson <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: Runtime Partition Pruning

David Rowley-3
On 15 November 2017 at 01:57, David Rowley <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: Runtime Partition Pruning

Amit Langote-2
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


Reply | Threaded
Open this post in threaded view
|

Re: Runtime Partition Pruning

Beena Emerson
In reply to this post by rajkumar.raghuwanshi
Hello Rajkumar,

On Tue, Nov 14, 2017 at 2:22 PM, Rajkumar Raghuwanshi
<[hidden email]> wrote:

> On Tue, Nov 14, 2017 at 11:46 AM, Beena Emerson <[hidden email]>
> 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

Beena Emerson
Reply | Threaded
Open this post in threaded view
|

Re: Runtime Partition Pruning

Beena Emerson
In reply to this post by David Rowley-3
Hello David,

Thank you for reviewing.

On Wed, Nov 15, 2017 at 4:43 AM, David Rowley
<[hidden email]> wrote:

> On 15 November 2017 at 01:57, David Rowley <[hidden email]> 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

Beena Emerson
Reply | Threaded
Open this post in threaded view
|

Re: Runtime Partition Pruning

Dilip Kumar-2
In reply to this post by David Rowley-3
On Wed, Nov 15, 2017 at 4:43 AM, David Rowley
<[hidden email]> wrote:
> On 15 November 2017 at 01:57, David Rowley <[hidden email]> 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

Previous Thread Next Thread