Re: [HACKERS] Runtime Partition Pruning

Hello David,

Thank you for reviewing.

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)
>
> 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