I have a table indexed on a char(35) field and want a query to return a distinct list of the values in this column. Is there some syntactical magic I can do to get these values without a sequential scan? I assume, ( here we go again ) that these values are in the index somewhere and I seem to recall a select DISTINCT using an index in DB2 way back when but I may be mistaken.
Searching the archives I found an email that indicated this was possible in 7.4 and infact is the reason I upgraded from 7.2x. The table in question has about 700K rows and grows daily so seq scans hurt more and more all the time. Tips appreciated. elevating=# \d logrecords Table "public.logrecords" Column | Type | Modifiers ------------------+---------------+----------------------------------------------------------- serial | integer | not null default nextval('"logrecords_serial_seq"'::text) city | smallint | not null building | smallint | not null display | integer | not null advertiser | character(35) | not null pagename | character(65) | not null log_date | date | not null exhibition_count | integer | not null Indexes: "logrecords_pkey" primary key, btree (serial) "logrecords_advertiser" btree (advertiser) "logrecords_building" btree (building) "logrecords_city" btree (city) "logrecords_date" btree (log_date) Triggers: "RI_ConstraintTrigger_1709151" AFTER INSERT OR UPDATE ON logrecords FROM cities NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins"('logrec_ref_cities', 'logrecords', 'cities', 'UNSPECIFIED', 'city', 'num') "RI_ConstraintTrigger_1709152" AFTER INSERT OR UPDATE ON logrecords FROM buildings NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins"('logrecords_ref_buildings', 'logrecords', 'buildings', 'UNSPECIFIED', 'building', 'num') "RI_ConstraintTrigger_1709153" AFTER INSERT OR UPDATE ON logrecords FROM displays NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins"('logrecords_ref_display', 'logrecords', 'displays', 'UNSPECIFIED', 'display', 'num') elevating=# explain select distinct advertiser from logrecords; QUERY PLAN -------------------------------------------------------------------------------- Unique (cost=136440.68..139858.43 rows=61 width=39) -> Sort (cost=136440.68..138149.55 rows=683550 width=39) Sort Key: advertiser -> Seq Scan on logrecords (cost=0.00..20785.50 rows=683550 width=39) (4 rows) Bret ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])