pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
way to speed up a SELECT DISTINCT?
6+ messages / 4 participants
[nested] [flat]

* way to speed up a SELECT DISTINCT?
@ 2003-10-10 09:41  Seth Ladd <seth@picklematrix.net>
  0 siblings, 1 reply; 6+ messages in thread

From: Seth Ladd @ 2003-10-10 09:41 UTC (permalink / raw)
  To: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: way to speed up a SELECT DISTINCT?
@ 2003-10-10 10:07  Peter Childs <blue.dragon@blueyonder.co.uk>
  parent: Seth Ladd <seth@picklematrix.net>
  0 siblings, 1 reply; 6+ messages in thread

From: Peter Childs @ 2003-10-10 10:07 UTC (permalink / raw)
  To: Seth Ladd <seth@picklematrix.net>; +Cc: pgsql-performance

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)






^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: way to speed up a SELECT DISTINCT?
@ 2003-10-10 10:50  Seth Ladd <seth@picklematrix.net>
  parent: Peter Childs <blue.dragon@blueyonder.co.uk>
  0 siblings, 3 replies; 6+ messages in thread

From: Seth Ladd @ 2003-10-10 10:50 UTC (permalink / raw)
  To: pgsql-performance

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

Thanks for the tip, I'll give this a shot soon.  I am curious, your  
example above does not use GROUP BY yet you have an INDEX SCAN.  I am  
using a similar query, yet I get a full table scan.  I wonder how they  
are different?

I'll try the group by anyway.

Thanks,
Seth




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: way to speed up a SELECT DISTINCT?
@ 2003-10-10 11:00  Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  parent: Seth Ladd <seth@picklematrix.net>
  2 siblings, 0 replies; 6+ messages in thread

From: Shridhar Daithankar @ 2003-10-10 11:00 UTC (permalink / raw)
  To: Seth Ladd <seth@picklematrix.net>; +Cc: pgsql-performance

Seth Ladd wrote:

>> 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)
> 
> 
> Thanks for the tip, I'll give this a shot soon.  I am curious, your  
> example above does not use GROUP BY yet you have an INDEX SCAN.  I am  
> using a similar query, yet I get a full table scan.  I wonder how they  
> are different?

Have you tuned your shared buffers and effective cache correctly?

  Shridhar




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: way to speed up a SELECT DISTINCT?
@ 2003-10-10 11:05  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Seth Ladd <seth@picklematrix.net>
  2 siblings, 0 replies; 6+ messages in thread

From: Christopher Kings-Lynne @ 2003-10-10 11:05 UTC (permalink / raw)
  To: Seth Ladd <seth@picklematrix.net>; +Cc: pgsql-performance

> Thanks for the tip, I'll give this a shot soon.  I am curious, your
> example above does not use GROUP BY yet you have an INDEX SCAN.  I am
> using a similar query, yet I get a full table scan.  I wonder how they
> are different?

Please send us the results of EXPLAIN ANALYZE the query.  The EXPLAIN
results usually aren't too interesting for degenerate queries.

Also, make sure you have run ANALYZE on your database.

Chris





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: way to speed up a SELECT DISTINCT?
@ 2003-10-10 11:17  Peter Childs <blue.dragon@blueyonder.co.uk>
  parent: Seth Ladd <seth@picklematrix.net>
  2 siblings, 0 replies; 6+ messages in thread

From: Peter Childs @ 2003-10-10 11:17 UTC (permalink / raw)
  To: Seth Ladd <seth@picklematrix.net>; +Cc: pgsql-performance

On Fri, 10 Oct 2003, Seth Ladd wrote:

> >> 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)
> 
> Thanks for the tip, I'll give this a shot soon.  I am curious, your  
> example above does not use GROUP BY yet you have an INDEX SCAN.  I am  
> using a similar query, yet I get a full table scan.  I wonder how they  
> are different?
> 
> I'll try the group by anyway.
> 
	Its a guess but ANALYSE might help. `

Peter Childs




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2003-10-10 11:17 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-10 09:41 way to speed up a SELECT DISTINCT? Seth Ladd <seth@picklematrix.net>
2003-10-10 10:07 ` Peter Childs <blue.dragon@blueyonder.co.uk>
2003-10-10 10:50   ` Seth Ladd <seth@picklematrix.net>
2003-10-10 11:00     ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-10-10 11:05     ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-10-10 11:17     ` Peter Childs <blue.dragon@blueyonder.co.uk>

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