Re: [HACKERS] Runtime Partition Pruning

On Wed, Oct 7, 2020 at 5:05 PM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:

>
>
> On Sun, Oct 4, 2020 at 3:10 PM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:
>
>>
>>>
>>> Now, in my experience, the current system for custom plans vs. generic
>>> plans doesn't approach the problem in this way at all, and in my
>>> experience that results in some pretty terrible behavior. It will do
>>> things like form a custom plan every time because the estimated cost
>>> of the custom plan is lower than the estimated cost of the generic
>>> plan even though the two plans are structurally identical; only the
>>> estimates differ. It will waste gobs of CPU cycles by replanning a
>>> primary key lookup 5 times just on the off chance that a lookup on the
>>> primary key index isn't the best option. But this patch isn't going
>>> to fix any of that. The best we can probably do is try to adjust the
>>> costing for Append paths in some way that reflects the costs and
>>> benefits of pruning. I'm tentatively in favor of trying to do
>>> something modest in that area, but I don't have a detailed proposal.
>>>
>>>
>> I just realized this issue recently and reported it at [1], then Amit
>> pointed
>> me to this issue being discussed here, so I would like to continue this
>> topic
>> here.
>>
>> I think we can split the issue into 2 issues. One is the partition prune
>> in initial
>> partition prune, which maybe happen in custom plan case only and caused
>> the above issue. The other one happens in the "Run-Time" partition
>> prune,
>> I admit that is an important issue to resolve as well, but looks harder.
>> So I
>> think we can fix the first one at first.
>>
>> ... When we count for the cost of a
>> generic plan, we can reduce the cost based on such information.
>>
>
> This way doesn't work since after the initial partition prune, not only
> the
> cost of the Append node should be reduced, the cost of other plans should
> be reduced as well [1]
>
> However I think if we can use partition prune information from a custom
> plan
> at the cost_append_path stage, it looks the issue can be fixed. If so,
> the idea
> is similar to David's idea in [2], however Robert didn't agree with
> this[2].
> Can anyone elaborate this objection? for a partkey > $1 or BETWEEN cases,
> some real results from the past are probably better than some hard-coded
> assumptions IMO.
>

I can understand Robert's idea now, he intended to resolve both the
"initial-partition-prune" case and "runtime partition prune" case while I
always think
about the former case (Amit reminded me about that at the beginning, but I
just
wake up right now..)

With the Robert's idea, I think we can do some hack at create_append_path,
we can figure out the pruneinfo (it was done at create_append_plan now) at
that
stage, and then did a fix_append_path with such pruneinfo. We need to fix
not
only the cost of AppendPath, but also rows of AppendPath, For example:
SELECT .. FROM t1, t2, p where t1.a = p.partkey and t1.b = t2.b, if the
plan is:
Nest Loop
Nest Loop
t1
Append (p)
t2

Then the rows of Append (p) will be very important.

For this idea, how to estimate how many partitions/rows can be pruned is a
key
part. Robert said "I was thinking, rather, that if we know for example that
we've doing pruning on partition_column = $1, then we know that only
one partition will match. That's probably a common case. If we've
got partition_column > $1, we could assume that, say, 75% of the
partitions would match. partition_column BETWEEN $1 and $2 is
probably a bit more selective, so maybe we assume 50% of the
partitions would match.". I think we can't say the 75% or 50% is a good
number, but the keypoint may be "partition_column = $1" is a common
case. And for the others case, we probably don't make it worse.

I think we need to do something here, any thoughts? Personally I'm more
like this idea now.

--
Best Regards
Andy Fan