Re: near identical queries have vastly different plans

On Fri, Jul 1, 2011 at 3:46 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Samuel Gendler <sgendler(at)ideasculptor(dot)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.
>

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.