pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddistinct values without seq scan
2+ messages / 2 participants
[nested] [flat]
* distinct values without seq scan
@ 2004-04-06 17:06 Bret Hughes <bhughes@elevating.com>
2004-04-07 15:43 ` Re: distinct values without seq scan Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 2+ messages in thread
From: Bret Hughes @ 2004-04-06 17:06 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: distinct values without seq scan
2004-04-06 17:06 distinct values without seq scan Bret Hughes <bhughes@elevating.com>
@ 2004-04-07 15:43 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 2+ messages in thread
From: Josh Berkus @ 2004-04-07 15:43 UTC (permalink / raw)
To: Bret Hughes <bhughes@elevating.com>; pgsql-sql
Bret,
> 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.
Well, leaving aside that you have a data normalization issue (these values
should really be in a reference list with an FK), to take advantage of the
7.4 optimization, try:
SELECT advertiser FROM logrecords GROUP BY advertiser ORDER BY advertiser;
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-04-07 15:43 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-06 17:06 distinct values without seq scan Bret Hughes <bhughes@elevating.com>
2004-04-07 15:43 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox