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. regards, tom lane
