Обсуждение: select distinct too slow

[postgresql 7.1.3, Sun ultrasparc, Solaris 2.5.1]
create table myjunk(run_name text,wafer_num int2,pass int2,test_type text,yloc int2,xloc int2,test_num int,test_data
float8,commentstext,primary key(run_name,wafer_num,yloc,xloc,test_type,test_num))
 
[insert 240765 rows]
vacuum analyze myjunk;

There are only 3 distinct values of run_name right now.

select distinct run_name from myjunk;
[takes ~ 14 seconds]

explain select distinct run_name from myjunk;

Unique  (cost=34896.75..35498.66 rows=24076 width=12) ->  Sort  (cost=34896.75..34896.75 rows=240765 width=12)       ->
Seq Scan on myjunk  (cost=0.00..6785.65 rows=240765 width=12)
 

How can I make this query be fast?  I tried adding an index on run_name alone, that did not help.


I guess I can keep a separate table with unique run_name's...  sounds kind of ugly.
If I did this, what sort of trigger or rule could maintain it automatically -- and how could it
be done without grossly slowing down large data loads into that table?

-- 
Your mouse has moved.
Windows NT must be restarted for the change to take effect.
Reboot now?  [OK]

Did you vacuum analyse your table after creating the index?
A solution for your problem would be to turn sequential scans ofd before querying the table.
This should work somehow like that:

myjava=# SET enable_seqscan TO 'off';
SET VARIABLE
myjava=# show enable_seqscan;
NOTICE:  enable_seqscan is off
SHOW VARIABLE

check out the docs for further information.
   Hans