Rafael Martinez <r.m.guerrero@usit.uio.no> writes:
> We have a SQL statement that with 9.1 takes ca 4000ms to finnish and
> with 9.2 over 22000ms.
> ...
> We can see that the query plan is very different between versions and
> that 9.2 is really wrong with the number of rows involved. Why is 9.2
> taking so wrong about the number of rows involved in some parts of the
> plan?
9.1's no better. The reason you don't get a similar plan out of 9.1
is that it doesn't flatten the nested EXISTS sub-selects, so that a
parameterized nestloop plan is the best it can do no matter what.
9.2 is able to consider more types of plan for this query, and it's
finding one that it thinks is cheaper. Unfortunately, parameterized
nestloop really is the best thing in this specific case.
I think the rowcount estimation error that's actually serious is the one
for the "19 < (Subplan 1)" condition, where it's expecting 161252 rows but
reality is only 179. If that were even just one order of magnitude closer
to reality, the other plan style would look cheaper.
Unfortunately, I can't offhand think of anything you can do to improve
the estimation of that condition as-is. Maybe there's some other way to
phrase the query? The current coding of the query looks rather like it's
been tuned for the one case that pre-9.2 releases can manage to do well.
If you don't want to do any major rewriting, you could probably stick an
OFFSET 0 into the outer EXISTS sub-select (and/or the inner one) to get
something similar to the 9.1 plan.
For some context see commit 0816fad6eebddb8f1f0e21635e46625815d690b9 and
the previous commits it references.
regards, tom lane