Re: Bad Row Count Estimate on View with 8.2

"Dave Dutcher" <dave(at)tridecap(dot)com> writes:
> I discovered a query which is taking 70 seconds on 8.2.1 which used to take
> under a second on 8.1.2. I was digging into what was causing it and I
> believe the problem is a view which the planner estimates will return 1 row
> when it actually returns 3500.

This is evidently a failure of estimate_num_groups(). However, I do not
see any difference in that code between 8.1 and 8.2 branch tips. I do
notice a possibly-relevant change that was applied in 8.1.4:

2006-05-02 00:34 tgl

* src/backend/: optimizer/path/allpaths.c, utils/adt/selfuncs.c
(REL8_1_STABLE): Avoid assuming that statistics for a parent
relation reflect the properties of the union of its child relations
as well. This might have been a good idea when it was originally
coded, but it's a fatally bad idea when inheritance is being used
for partitioning. It's better to have no stats at all than
completely misleading stats. Per report from Mark Liberman.

The bug arguably exists all the way back, but I've only patched
HEAD and 8.1 because we weren't particularly trying to support
partitioning before 8.1.

Eventually we ought to look at deriving union statistics instead of
just punting, but for now the drop kick looks good.

I think this was only meant to apply to table inheritance situations,
but on reflection it might affect UNION queries too. The question is
whether the numbers it was using before really mean anything --- they
seem to have been better-than-nothing in your particular case, but I'm
not sure that translates to a conclusion that we should depend on 'em.

In fact, since there isn't any "parent relation" in a UNION, I'm not
sure that this patch actually changed your results ... but I'm not
seeing what else would've ...

regards, tom lane