X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id E6C6B9FA5DC
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  1 Jun 2006 10:26:31 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 99358-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  1 Jun 2006 10:26:21 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from titan.startnet.cz (unknown [82.113.63.73])
	by postgresql.org (Postfix) with ESMTP id 0745D9FA040
	for <pgsql-sql@postgresql.org>; Thu,  1 Jun 2006 10:26:20 -0300 (ADT)
Received: (qmail 11399 invoked from network); 1 Jun 2006 15:26:18 +0200
Received: from unknown (HELO ?192.168.1.2?)
	(miroslav.sulc@startnet.cz@10.15.192.25)
	by titan.local.startnet.cz with ESMTPA; 1 Jun 2006 15:26:18 +0200
Message-ID: <447EEAF1.7040305@startnet.cz>
Date: Thu, 01 Jun 2006 15:26:09 +0200
From: =?ISO-8859-2?Q?Miroslav_=A9ulc?= <miroslav.sulc@startnet.cz>
Organization: StartNet s.r.o.
User-Agent: Thunderbird 1.5.0.2 (X11/20060501)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: SELECT DISTINCT too slow
Content-Type: multipart/mixed; boundary="------------020901070903070507080502"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200606/2
X-Sequence-Number: 25238

This is a multi-part message in MIME format.
--------------020901070903070507080502
Content-Type: multipart/alternative;
	boundary="------------000107060507000306010200"


--------------000107060507000306010200
Content-Type: text/plain; charset=ISO-8859-2
Content-Transfer-Encoding: 8bit

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


--------------000107060507000306010200
Content-Type: text/html; charset=ISO-8859-2
Content-Transfer-Encoding: 8bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-2" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Hello,<br>
<br>
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:<br>
<br>
SELECT DISTINCT Key FROM MRTPContactValue<br>
<br>
Here is the query plan:<br>
<br>
<table>
  <tbody>
    <tr>
      <th class="data">QUERY PLAN</th>
    </tr>
    <tr>
      <td class="data1" nowrap="nowrap">
      <pre class="data">Unique  (cost=64882.26..66964.59 rows=9 width=9) (actual time=26139.972..29593.164 rows=10 loops=1)</pre>
      </td>
    </tr>
    <tr>
      <td class="data2" nowrap="nowrap">
      <pre class="data">  -&gt;  Sort  (cost=64882.26..65923.43 rows=416466 width=9) (actual time=26139.964..27975.944 rows=416466 loops=1)</pre>
      </td>
    </tr>
    <tr>
      <td class="data1" nowrap="nowrap">
      <pre class="data">        Sort Key: "key"</pre>
      </td>
    </tr>
    <tr>
      <td class="data2" nowrap="nowrap">
      <pre class="data">        -&gt;  Seq Scan on mrtpcontactvalue  (cost=0.00..8669.66 rows=416466 width=9) (actual time=0.026..2460.535 rows=416466 loops=1)</pre>
      </td>
    </tr>
    <tr>
      <td class="data1" nowrap="nowrap">Total runtime: 29603.159 ms</td>
    </tr>
  </tbody>
</table>
<br>
I've tried index on the "key" column but no improvement.<br>
<br>
Is there a way to speed the SELECT up?<br>
<br>
Thank you for any suggestions.<br>
<pre class="moz-signature" cols="72">-- 
Miroslav Šulc</pre>
</body>
</html>

--------------000107060507000306010200--

--------------020901070903070507080502
Content-Type: text/x-vcard; charset=utf-8;
 name="miroslav.sulc.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="miroslav.sulc.vcf"

begin:vcard
fn;quoted-printable:Miroslav =C5=A0ulc
n;quoted-printable:=C5=A0ulc;Miroslav
org:StartNet s.r.o.
adr;quoted-printable;quoted-printable:;;Schodov=C3=A1 309/10;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika
email;internet:miroslav.sulc@startnet.cz
tel;cell:+420 603 711 413
x-mozilla-html:TRUE
url:http://www.startnet.cz
version:2.1
end:vcard


--------------020901070903070507080502--