RE: Skip partition tuple routing with constant partition key

Hi Amit-san

From: Amit Langote <amitlangote09(at)gmail(dot)com>
Sent: Tuesday, May 25, 2021 10:06 PM
> Hou-san,
> > Thanks for the patch and It looks more compact than mine.
> >
> > After taking a quick look at the patch, I found a possible issue.
> > Currently, the patch does not search the parent's partition key expression
> recursively.
> > For example, If we have multi-level partition:
> > Table A is partition of Table B, Table B is partition of Table C.
> > It looks like if insert into Table A , then we did not replace the key expression
> which come from Table C.
>
> Good catch! Although, I was relieved to realize that it's not *wrong* per se, as
> in it does not produce an incorrect result, but only
> *slower* than if the patch was careful enough to replace all the parents' key
> expressions.
>
> > If we want to get the Table C, we might need to use pg_inherit, but it costs
> too much to me.
> > Instead, maybe we can use the existing logic which already scanned the
> > pg_inherit in function generate_partition_qual(). Although this change
> > is out of ExecPartitionCheck(). I think we'd better replace all the
> > parents and grandparent...'s key expression. Attaching a demo patch based
> on the patch you posted earlier. I hope it will help.
>
> Thanks.
>
> Though again, I think we can do this without changing the relcache interface,
> such as RelationGetPartitionQual().
>
> PartitionTupleRouting has all the information that's needed here.
> Each partitioned table involved in routing a tuple to the leaf partition has a
> PartitionDispatch struct assigned to it. That struct contains the PartitionKey
> and we can access partexprs from there. We can arrange to assemble them
> into a single list that is saved to a given partition's ResultRelInfo, that is, after
> converting the expressions to have partition attribute numbers. I tried that in
> the attached updated patch; see the 0002-* patch.

Thanks for the explanation !
Yeah, we can get all the parent table info from PartitionTupleRouting when INSERT into a partitioned table.

But I have two issues about using the information from PartitionTupleRouting to get the parent table's key expression:
1) It seems we do not initialize the PartitionTupleRouting when directly INSERT into a partition(not a partitioned table).
I think it will be better we let the pre-compute-key_expression feature to be used in all the possible cases, because it
could bring nice performance improvement.

2) When INSERT into a partitioned table which is also a partition, the PartitionTupleRouting is initialized after the ExecPartitionCheck.
For example:
create unlogged table parttable1 (a int, b int, c int, d int) partition by range (partition_func(a));
create unlogged table parttable1_a partition of parttable1 for values from (0) to (5000);
create unlogged table parttable1_b partition of parttable1 for values from (5000) to (10000);

create unlogged table parttable2 (a int, b int, c int, d int) partition by range (partition_func1(b));
create unlogged table parttable2_a partition of parttable2 for values from (0) to (5000);
create unlogged table parttable2_b partition of parttable2 for values from (5000) to (10000);

---When INSERT into parttable2, the code do partitioncheck before initialize the PartitionTupleRouting.
insert into parttable2 select 10001,100,10001,100;

Best regards,
houzj