Обсуждение: 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.

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