Re: Improve choose_custom_plan for initial partition prune case

Hi Ashutosh:

Thanks for coming.

On Mon, Oct 5, 2020 at 9:27 PM Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>
wrote:

> On Thu, Oct 1, 2020 at 9:34 PM 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'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.
>
> What happens when we
> execute plans with values that have estimates similar to the generic
> plan later when we moderate generic plan costs based on the custom
> plans?
>
>
The example at the beginning of this thread, I used the exact same values
every time, the custom plan will be chosen all the time, which is bad,
The main reason is the custom plan knows the exact value in Execute
message, so it run plan time partition prune, then the total cost is low,
however
for the generic plan the partition prune happens at
Runtime initial_partition prune
stage, so the cost of the partitions which can be pruned at that stage is
still
included the total cost, so generic plans can't be chosen. that would be
the
thing I want to fix.

If the table has good distribution of a partition key, which also
> results in good distribution of data across partitions, generic plan
> cost will be similar to the custom plan costs. If not that's something
> we want to fix.

> [1] talks about generic plan being
> not chosen just because it has higher cost even though its shape is
> similar to a custom plan. Leveraging that idea might be a good option.
> If the custom plans produced have shapes similar to the generic plan,
> stop producing those.
>

If I understand you correctly, the issue I want to fix here matches this
goal.

--
Best Regards
Andy Fan