Re: [HACKERS] Runtime Partition Pruning

Hello,

On Wed, Nov 15, 2017 at 4:43 AM, David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> On 15 November 2017 at 01:57, David Rowley <david(dot)rowley(at)2ndquadrant(dot)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