Hi,
could somebody explain me please why following select
SELECT docid FROM prod.guids 
	GROUP BY docid HAVING( COUNT(docid) > 1 )

taking 15 min on 2 Proc Box on 1M rows, where number of duplicates
around 300K,
and docid indexed and not null and char(16).

May be I am doing something wrong?
Thank you.

Tue, 10 Jun 2003 17:37:31 -0700
"Maksim Likharev" <mlikharev@aurigin.com>
SELECT TAKES A LOOOONG TIME
> Hi,
> could somebody explain me please why following select
> SELECT docid FROM prod.guids 
> 	GROUP BY docid HAVING( COUNT(docid) > 1 )
> 
> taking 15 min on 2 Proc Box on 1M rows, where number of duplicates
> around 300K,
> and docid indexed and not null and char(16).
> 
> May be I am doing something wrong?
> Thank you.

I would first perform an "explain" on the query to have the database show
you where it is spending its time.

Second, you are performing a full table scan. Try this:

select count(docid) from prod.guids

See how long that takes, that's about as fast as your system will handle
that query. If that query is fast, you may need to play with the tuning
parameters of PostgreSQL like "sort memory." Have you adjusted any
parameters in your postgresql.conf file?


Wed, 11 Jun 2003 11:21:37 -0400 (EDT)
pgsql@mohawksoft.com
Re: SELECT TAKES A LOOOONG TIME