Re: Query plan good in 8.4, bad in 9.2 and better in 9.3

On Thu, May 15, 2014 at 10:52 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> writes:
>> OK so we have a query that does OK in 8.4, goes to absolute crap in
>> 9.2 and then works great in 9.3. Thing is we've spent several months
>> regression testing 9.2 and no time testing 9.3, so we can't just "go
>> to 9.3" in an afternoon. But we might have to. 9.2 seems hopelessly
>> broken here.
>
>> The query looks something like this:
>
>> SELECT COUNT(*) FROM u, ug
>> WHERE u.ugid = ug.id
>> AND NOT u.d
>> AND ug.somefield IN (SELECT somefunction(12345));
>
> You really should show us somefunction's definition if you want
> useful comments. I gather however that it returns a set. 8.4
> seems to be planning on the assumption that the set contains
> only one row, which is completely unjustified in general though
> it happens to be true in your example. 9.2 is assuming 1000 rows
> in the set, and getting a sucky plan because that's wrong. 9.3
> is still assuming that; and I rather doubt that you are really
> testing 9.3 on the same data, because 9.2 is finding millions of
> rows in a seqscan of u while 9.3 is finding none in the exact
> same seqscan.
>
> I'd suggest affixing a ROWS estimate to somefunction, or better
> declaring it to return singleton not set if that's actually
> always the case.

Well great, now I look like an idiot. Last time I trust someone else
to set up my test servers.

Anyway, yeah, affixing a rows estimate fixes this for us 100%. So thanks!