From: Tom Lane <tgl@sss.pgh.pa.us>
To: Samuel Gendler <sgendler@ideasculptor.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: near identical queries have vastly different plans
Date: Fri, 01 Jul 2011 18:46:49 -0400
Message-ID: <6960.1309560409@sss.pgh.pa.us> (raw)
In-Reply-To: <BANLkTin9E_2ZGOPoSvsewv2xHe6nv2Pnjg@mail.gmail.com>
References: <BANLkTin9E_2ZGOPoSvsewv2xHe6nv2Pnjg@mail.gmail.com>
Samuel Gendler <sgendler@ideasculptor.com> writes:
> I've got 2 nearly identical queries that perform incredibly differently.
The reason the slow query sucks is that the planner is estimating at
most one "s" row will match that complicated AND/OR condition, so it
goes for a nestloop. In the "fast" query there is another complicated
AND/OR filter condition, but it's not so far off on the number of
matching rows, so you get a better plan choice. Can't tell from the
given information whether the better guess is pure luck, or there's some
difference in the column statistics that makes it able to get a better
estimate for that.
In general, though, you're skating on thin ice anytime you ask the
planner to derive statistical estimates about combinations of correlated
columns --- and these evidently are correlated. Think about refactoring
the table definitions so that you're only testing a single column, which
ANALYZE will be able to provide stats about. Or maybe you can express
it as a test on a computed expression, which you could then keep an
index on, prompting ANALYZE to gather stats about that.
regards, tom lane
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, sgendler@ideasculptor.com
Subject: Re: near identical queries have vastly different plans
In-Reply-To: <6960.1309560409@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