Re: way to speed up a SELECT DISTINCT?

On Thu, 9 Oct 2003, Seth Ladd wrote:

> Hello,
>
> I am running 7.3.2 RPMs on RH9, on a celeron 1.7 w/ 1gig ram.
>
> I have a table that has 6.9 million rows, 2 columns, and an index on
> each column. When I run:
>
> SELECT DISTINCT column1 FROM table
>
> It is very, very slow (10-15 min to complete). An EXPLAIN shows no
> indexes are being used.
>
> Is there any way to speed this up, or is that DISTINCT going to keep
> hounding me?
>
> I checked the mailing list, and didn't see anything like this.
>
> Any tips or hints would be greatly appreciated. Thanks for your help!
> Seth
>
>
Try group by instead. I think this is an old bug its fixed in
7.3.2 which I'm using.

Peter Childs
`

peter(at)bernardo:express=# explain select distinct region from region;
QUERY PLAN
----------------------------------------------------------------------------------------------
Unique (cost=0.00..4326.95 rows=9518 width=14)
-> Index Scan using regionview_region on region (cost=0.00..4089.00
rows=95183 width=14)
(2 rows)

peter(at)bernardo:express=# explain select distinct region from region group
by region;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Unique (cost=0.00..4350.75 rows=952 width=14)
-> Group (cost=0.00..4326.95 rows=9518 width=14)
-> Index Scan using regionview_region on region
(cost=0.00..4089.00 rows=95183 width=14)
(3 rows)