Re: near identical queries have vastly different plans

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.

regards, tom lane