Re: Slight change in query leads to unexpected change in query plan

Sam Mason <sam@samason.me.uk> writes:
> On Mon, Jun 22, 2009 at 05:55:28PM -0400, Jack Orenstein wrote:
>> Why does adding the value restriction so radically change the execution
>> plan?

> PG doesn't have any cross column statistics and hence it assumes that pk
> and value are uncorrelated.

Even if they are correlated, they aren't necessarily correlated in a
good way; the query plan Jack is hoping for could easily be pessimal.
We routinely see complaints where the planner does what he's hoping for
and gets burnt ...

If the speed of this particular type of query is critical, it might be
worth maintaining a 2-column index on (value, pk).  That would provide
guaranteed good performance since the desired rows form a contiguous run
in the index.

            regards, tom lane