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@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@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)