"Nick Fankhauser" <nickf@ontko.com> writes:
>> That seems strange to me also, particularly if the index column ordering
>> is indeed actor_id,case_id and not the other way round
> Actually, it *is* the other way around- I didn't realize that could make a
> difference.
Ah. Okay, that eliminates the apparent impossibility.
> I think this solves my immediate problem, but it seems like even with the
> reversed order, the planner shouldn't have chosen the combined index to
> drive the query, so I'm happy for now, but I fear that I've added a task to
> someone's list in the developer's enclave.
The only reason the planner should choose a single-column index over
using the first column of a multi-column index is that the latter index
is likely to be physically larger and thus require more I/O to access.
So, there's no penalty in the cost calculations other than the
number-of-blocks-of-I/O estimated from the physical index size. It
would be interesting to see the reltuples and relpages stats from
pg_class for your single- and multi-column indexes. (However, it may be
too late now since you blew away and rebuilt the multi-column index;
we can't be entirely sure what stats the planner was seeing before.)
It's actually a standard recommendation that you not bother with an
index on a single column x if you also have one on (x,y). Unless the
table is almost entirely read-only, the cost of updating two indexes
outweighs the I/O savings of using the smaller index for queries that
use only x.
regards, tom lane