Re: [HACKERS] Runtime Partition Pruning

On Thu, Oct 8, 2020 at 8:25 PM Ashutosh Bapat
<ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
> On Wed, Oct 7, 2020 at 7:00 PM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:
> > 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.
>
> Yes, I think we have to do something on those lines. But I am
> wondering why our stats machinery is failing to do that already. For
> equality I think it's straight forward. But even for other operators
> we should use the statistics from the partitioned table to estimate
> the selectivity and then adjust number of scanned partitions = (number
> of partitions * fraction of rows scanned). That might be better than
> 50% or 75%.

This is an interesting idea, that is, the idea of consulting parent
relation's stats to guess at the number of partitions that might be
scanned.

However, we don't currently consult an inheritance parent relation's
stats at all during "base" relation planning, which is why you don't
see the parent relation's statistics reflected in the row count and
costs assigned to its (Append at al) paths. The hard-coded rule is to
sum up children's rows and their paths' costs; see cost_append().

My thinking on this was that we'd just extend that hard-coded rule to
take into account that run-time pruning, if applicable in a given
plan, will cause some of those child paths to be discarded. Maybe
Andy is headed in that direction?

--
Amit Langote
EDB: http://www.enterprisedb.com