Thread: "Group by" and "index".

Hi,

PostgreSQL 6.5.3.

I've created the following index:

>create index test_index on test (filed1);
>vacuum;

And then:

>explain select field1 from test group by field1;
NOTICE:  QUERY PLAN:

Group  (cost=2510.40 rows=58709 width=12) ->  Sort  (cost=2510.40 rows=58709 width=12)       ->  Seq Scan on test
(cost=2510.40rows=58709 width=12)
 

It don't use this index :-(. I've tried to create two types of index:
b-tree and hash - both doesn't work until "group" is present. If I type

> explain select field1 from test where field1='tra-ta-ta';

NOTICE:  QUERY PLAN:

Index Scan using test_index on test (cost=2.05 rows=2 width=12)


It work this way.


Any ideas?

_____________
Vlad Marchenko

"Vlad Marchenko" <vlad@infonet.com.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