Re: near identical queries have vastly different plans

From: Tom Lane
Subject: Re: near identical queries have vastly different plans
Date: ,
Msg-id: 6960.1309560409@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: near identical queries have vastly different plans  (Samuel Gendler)
Responses: Re: near identical queries have vastly different plans  (Samuel Gendler)
List: pgsql-performance

Tree view

near identical queries have vastly different plans  (Samuel Gendler, )
 Re: near identical queries have vastly different plans  (Samuel Gendler, )
 Re: near identical queries have vastly different plans  (Samuel Gendler, )
 Re: near identical queries have vastly different plans  (Tom Lane, )
  Re: near identical queries have vastly different plans  (Samuel Gendler, )

Samuel Gendler <> 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


pgsql-performance by date:

From: Samuel Gendler
Date:
Subject: Re: near identical queries have vastly different plans
From: Dave Page
Date:
Subject: Re: Infinite Cache