Thread: Using the correct index

I have a table I'll call SomeTable that has columns called id, date, and
name along with a few others.  I created two indexes for this table, one was
on id (called SomeTable_id) and the other was on id and date (called
SomeTable_id_date).  When I do:

EXPLAIN SELECT * FROM SomeTable WHERE id = '0101'

it tells me:

Index Scan using SomeTable_id_date on Sometable ...

Even when I do:

EXPLAIN SELECT id FROM SomeTable WHERE id = '0101'

it still says it's using the SomeTable_id_date index.  Why is it using the
multicolumn index when the date column isn't involved?

-- Jeremy  [jeremy@wellsgaming.com]

Jeremy Buchmann <jeremy@wellsgaming.com> writes:
> I have a table I'll call SomeTable that has columns called id, date, and
> name along with a few others.  I created two indexes for this table, one was
> on id (called SomeTable_id) and the other was on id and date (called
> SomeTable_id_date).  When I do:
> EXPLAIN SELECT * FROM SomeTable WHERE id = '0101'
> it tells me:
> Index Scan using SomeTable_id_date on Sometable ...
> Even when I do:
> EXPLAIN SELECT id FROM SomeTable WHERE id = '0101'
> it still says it's using the SomeTable_id_date index.  Why is it using the
> multicolumn index when the date column isn't involved?

If the indexes are small then the cost estimates for scanning them will
be the same, and it's a random matter which one gets picked (in fact,
I suspect 7.0 may deliberately prefer the "more sorted" one).

Once the indexes get larger --- more than 100 pages or so --- the system
will notice that scanning the double-column index is more expensive, and
will avoid using it unless there's good reason.

            regards, tom lane