From: Tom Lane <tgl@sss.pgh.pa.us>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Query plan good in 8.4, bad in 9.2 and better in 9.3
Date: Thu, 15 May 2014 12:52:48 -0400
Message-ID: <28358.1400172768@sss.pgh.pa.us> (raw)
In-Reply-To: <CAOR=d=1znj5LbHxNVi8j+sP4rS5EV0bS2CM4rqksL15qA4Jhyg@mail.gmail.com>
References: <CAOR=d=1znj5LbHxNVi8j+sP4rS5EV0bS2CM4rqksL15qA4Jhyg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Scott Marlowe <scott.marlowe@gmail.com> 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
--
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, scott.marlowe@gmail.com
Subject: Re: Query plan good in 8.4, bad in 9.2 and better in 9.3
In-Reply-To: <28358.1400172768@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