Re: [SQL] "Group by" and "index".

"Vlad Marchenko" <vlad(at)infonet(dot)com(dot)ua> writes:
>> explain select field1 from test group by field1;
> It don't use this index :-(.

That's a deficiency of the 6.5 planner; it won't consider an indexscan
unless there's a WHERE clause that matches the index. This is fixed for
7.0.

However, the 6.5 planner is not necessarily doing the wrong thing here!
Replacing the explicit sort with an index scan could easily produce a
slower query. Index scans are good for pulling out a few items,
but when you are going to end up fetching the whole table, they are
pretty slow because of the nonsequential disk accesses they cause.

regards, tom lane