To: Samuel Gendler <sgendler@ideasculptor.com>
cc: pgsql-performance@postgresql.org
Subject: Re: near identical queries have vastly different plans 
In-reply-to: <BANLkTin9E_2ZGOPoSvsewv2xHe6nv2Pnjg@mail.gmail.com> 
References: <BANLkTin9E_2ZGOPoSvsewv2xHe6nv2Pnjg@mail.gmail.com>
Comments: In-reply-to Samuel Gendler <sgendler@ideasculptor.com>
	message dated "Thu, 30 Jun 2011 01:53:14 -0700"
Date: Fri, 01 Jul 2011 18:46:49 -0400
Message-ID: <6960.1309560409@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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