SELECT DISTINCT too slow

From: Miroslav Šulc <miroslav(dot)sulc(at)startnet(dot)cz>
To: pgsql-sql(at)postgresql(dot)org
Subject: SELECT DISTINCT too slow
Date: 2006-06-01 13:26:09
Message-ID: 447EEAF1.7040305@startnet.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hello,

I have a table with cca 400,000 rows. The table contains column "key" of
varchar(20) type containing 10 distinct values. I want to get out what
distinct values are present in the column. I use this simple query,
which is very slow:

SELECT DISTINCT Key FROM MRTPContactValue

Here is the query plan:

QUERY PLAN

Unique (cost=64882.26..66964.59 rows=9 width=9) (actual time=26139.972..29593.164 rows=10 loops=1)

-> Sort (cost=64882.26..65923.43 rows=416466 width=9) (actual time=26139.964..27975.944 rows=416466 loops=1)

Sort Key: "key"

-> Seq Scan on mrtpcontactvalue (cost=0.00..8669.66 rows=416466 width=9) (actual time=0.026..2460.535 rows=416466 loops=1)

Total runtime: 29603.159 ms

I've tried index on the "key" column but no improvement.

Is there a way to speed the SELECT up?

Thank you for any suggestions.

--
Miroslav Šulc

Attachment Content-Type Size
miroslav.sulc.vcf text/x-vcard 349 bytes

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Rod Taylor 2006-06-01 13:41:56 Re: Table design question
Previous Message David Clarke 2006-06-01 11:05:54 Table design question