Re: Improve choose_custom_plan for initial partition prune case

On Sat, Oct 3, 2020 at 10:05 AM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:

> Hi Amit:
>
> Very glad to see your comment!
>
> On Fri, Oct 2, 2020 at 4:21 PM Amit Langote <amitlangote09(at)gmail(dot)com>
> wrote:
>
>> Hi Andy,
>>
>> On Fri, Oct 2, 2020 at 1:04 AM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:
>> >
>> > Given the plan example:
>> >
>> > CREATE TABLE measurement (
>> > city_id int not null,
>> > logdate date not null,
>> > peaktemp int,
>> > unitsales int
>> > ) PARTITION BY RANGE (logdate);
>> >
>> > CREATE TABLE measurement_y2006m02 PARTITION OF measurement
>> > FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
>> >
>> > CREATE TABLE measurement_y2006m03 PARTITION OF measurement
>> > FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
>> >
>> > prepare s as select * from measurement where logdate = $1;
>> > execute s('2006-02-01').
>> >
>> > The generic plan will probably not be chosen because it doesn't reduce
>> the cost
>> > which can be reduced at initial_prune while the custom plan reduces
>> such cost
>> > at planning time. which makes the cost comparison not fair.
>>
>> I agree that there is something to be done here. Actually, I think we
>> should try to find a solution that will allow us to consider not just
>> "initial" pruning, but also "execution-time" pruning. The latter
>> will allow a nested loop join whose inner side scans a partitioned
>> table using a parameterized scan on the partition key to be favored
>> over other join plans, because that parameterized scan can use
>> execution-time pruning which can make the inner scan very cheap.
>>
>>
> This looks like to resolve another important issue of partition prune,
> which
> may happen at planning time totally (no generic plan or custom plan
> involved).
> for example between choosing a Nest Loop plan which can use
> some run-time partition prune and hash join which can't. I "repeat" your
> idea
> just to make sure I understand you correctly.
>
>
>> > I'm thinking if we can
>> > get an estimated cost reduction of initial_prunne for generic plan
>> based on the
>> > partition pruned at plan time from custom plan and then reducing
>> > such costs from the generic plan. I just went through the related code
>> but
>> > didn't write anything now. I'd like to see if this is a correct
>> direction to go.
>>
>> That's an interesting idea, that is, to try to do this totally outside
>> the planner. When I was thinking about this a little while ago, I was
>> trying to find a way to adjust the cost of the plan in the planner
>> itself by looking at the runtime pruning info in the nodes that
>> support it, that is, Append, MergeAppend. Actually, such an approach
>> had also come up in the original run-time pruning discussion [1].
>>
>>
> Thank you for your comments. Looks like your approach can be helpful
> for the both cases, and I did think a bit for that as well, However, that
> looks
> complex (for me) AND I am prefer to guess how many partitions can be
> pruned with real data even it is the real data in the past (I assume that
> will not cause too much difference in practice).
>
> I'm not sure if I should treat Robert's comments as an opposed idea[1] ,
> but I think there are some little differences. I'd like to implement my
> idea
> soon, and I'm glad to see any opposed idea at any time, of course the
> sooner
> the better:)
>

After some day's coding in this direction, I find a very upsetting
situation.

The main idea here is if we have N partition survived for custom plan after
plan time partition prune, then I assume we can get the similar partition
survived for generic plan after initial partition prune. Then we should
reduce
such costs from generic plans.

However the hard part in implementation is we can't associate the Append
node in the generic plan with the survived partition information from the
custom plan even Append node has apprelids in it.

1. Associate them with rtindex. The final rtindex is impacted by
glob->finalrtable,
however the glob->finalrtable includes the child partitions, we will get
less
finalrtable in the custom plan so rtindex can't be matched.

2. Associate them with RelationOid, and we can record such information in
the
Append node as well. The bad part is the same relation Oid may appear
multiple
times in a query. for example: SELECT .. FROM p p1, p p2 where
p1.partkey1 = $1
AND p2.partkey2 = $2;

So any hint on this will be appreciated..

--
Best Regards
Andy Fan