Rafael Martinez 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

Search Discussions

Discussion Posts

Previous

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 7 of 7 | next ›
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedAug 26, '13 at 12:33p
activeAug 28, '13 at 7:08p
posts7
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2017 Grokbase