From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rafael Martinez <r.m.guerrero@usit.uio.no>
Cc: pgsql-performance@postgresql.org
Subject: Re: SQL statement over 500% slower with 9.2 compared with 9.1
Date: Wed, 28 Aug 2013 15:08:10 -0400
Message-ID: <17619.1377716890@sss.pgh.pa.us> (raw)
In-Reply-To: <521B4B2A.5010800@usit.uio.no>
References: <521B4B2A.5010800@usit.uio.no>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, r.m.guerrero@usit.uio.no
Subject: Re: SQL statement over 500% slower with 9.2 compared with 9.1
In-Reply-To: <17619.1377716890@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox