Using the correct index
Using the correct index
От:
Jeremy Buchmann <jeremy@wellsgaming.com>
Дата:
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]
Re: Using the correct index
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Jeremy Buchmann 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