Re: RT3.4 query needed a lot more tuning with 9.2 than it did with 8.1

On Mon, May 13, 2013 at 4:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Mon, May 13, 2013 at 4:14 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> You know, of course, that the join size estimate isn't arrived at that
>>> way. Still, this point does make it seem more like a planner bug and
>>> less like bad input stats. It would be nice to see a self-contained
>>> example ...
>
>> Yeah, I remember there have been examples like this that have come up
>> before. Unfortunately, I haven't fully grokked what's actually going
>> on here that allows this kind of thing to happen. Refresh my memory
>> on where the relevant code is?
>
> The point is that we estimate the size of a joinrel independently of
> any particular input paths for it, and indeed before we've built any
> such paths. So this seems like a bug somewhere in selectivity
> estimation, but I'm not prepared to speculate as to just where.

Hmm. I went looking for the relevant code and found
calc_joinrel_size_estimate(). If that's actually the right place to
be looking, it's hard to escape the conclusion that pselec > 1.

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