[HACKERS] SELECT TAKES A LOOOONG TIME

[Maksim Likharev]

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.

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] SELECT TAKES A LOOOONG TIME

[pgsql]

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


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]