Re: Future directions for inheritance-hierarchy statistics

On Tue, Mar 17, 2015 at 11:26 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>> This would have one significant drawback, which is that planning for
>>>> large inheritance trees (many children) would probably get noticeably
>>>> slower. (But in the common case that constraint exclusion limits a
>>>> query to scanning just one or a few children, the hit would be small.)
>
>> That's a pretty big drawback. I'm not sure whether it's big enough to
>> sink the whole idea, but we really need to make planning time on large
>> inheritance trees cheaper, not more expensive.
>
> Ah, but note the point about how there's no added cost for partitions that
> are removed by constraint exclusion. That should mean that in practical
> use it's not a huge problem. (If you're going to scan K partitions, you
> should not be surprised that planning time is O(K). It will be anyway
> thanks to other things such as index selection.)
>
> Also, you're ignoring the prospect of getting better estimates and hence
> better plans through having stats that dynamically adapt to the set of
> partitions being scanned. Given the lousy state of maintenance of
> whole-tree stats, I really think that this consideration might outweigh
> even a significant planning-time hit. Shaving planning time by producing
> crappy estimates isn't usually a good tradeoff.

Perhaps so, but I know that the planning time of large inheritance
trees has been a major issue for some of EnterpriseDB's customers. In
fact, EnterpriseDB has run into a number of customer situations where
planning time even for non-inheritance queries is substantially higher
than, shall we say, a competing commercial product. With inheritance,
even people who aren't making comparisons with other products start to
get unhappy. I've always been very pleased with the quality of plans
that our planner generates, but it's becoming increasingly clear to me
that at least one other product is able to provide good plans at a
significantly lower CPU cost, and inheritance is particular trouble
spot. I don't know exactly what we ought to do about that and perhaps
it's to one side of the issue you're raising here, but I do think it's
an issue that we (the PostgreSQL community) ought to be thinking
about.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company