Tom Lane wrote:
> 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.
So is a multi-column index really just two separate indexes with a
constraint added if necessary? I guess I had an idea in my head that it
would be something like an index on the concatenation of the two fields.
> It
> would be interesting to see the reltuples and relpages stats from
> pg_class for your single- and multi-column indexes.
It's easy to reverse the process. How would I get those stats?
> 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).
Thanks- that will make my app a bit more efficient. (But now I've got to go
back & work on tuning my query again because this apparently wasn't the
source of the poor performance.)
-Nick