From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Jeremy Buchmann <jeremy(at)wellsgaming(dot)com> |
Cc: | pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Using the correct index |
Date: | 2001-02-22 04:28:07 |
Message-ID: | 13806.982816087@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Jeremy Buchmann <jeremy(at)wellsgaming(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Maggie Chan | 2001-02-22 04:37:14 | lock the database |
Previous Message | Jeremy Buchmann | 2001-02-22 00:05:52 | Using the correct index |