On Fri, Jul 1, 2011 at 3:46 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Samuel Gendler <sgendler@xxxxxxxxxxxxxxxx> writes:The reason the slow query sucks is that the planner is estimating at
> I've got 2 nearly identical queries that perform incredibly differently.
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.
Thanks. There is actually already a column in s which is a primary key for the 2 columns that are currently being tested for. I didn't write the application code which generates the query, so can't say for sure why it is being generated as it is, but I'll ask the engineer in question to try the primary key column instead and see what happens.
