On Thu, May 15, 2014 at 10:52 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Scott Marlowe <scott.marlowe@xxxxxxxxx> 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!
