From: | "Timothy H(dot) Keitt" <keitt(at)nceas(dot)ucsb(dot)edu> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | select distinct |
Date: | 2000-07-28 23:36:50 |
Message-ID: | 39821912.22643EF3@nceas.ucsb.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
I've noticed that adding an index on a column has no effect on "select
distinct" (index or no index, the entire table is sequence scanned; see
example below). Couldn't you just traverse the index to get the
distinct values?
Tim
keitt=# vacuum analyze test;
VACUUM
keitt=# select distinct a from test;
a
---
a
b
c
d
f
s
z
(7 rows)
keitt=# explain select distinct a from test;
NOTICE: QUERY PLAN:
Unique (cost=1.67..1.72 rows=2 width=12)
-> Sort (cost=1.67..1.67 rows=21 width=12)
-> Seq Scan on test (cost=0.00..1.21 rows=21 width=12)
EXPLAIN
keitt=# create index test_index on test (a);
CREATE
keitt=# explain select distinct a from test;
NOTICE: QUERY PLAN:
Unique (cost=1.67..1.72 rows=2 width=12)
-> Sort (cost=1.67..1.67 rows=21 width=12)
-> Seq Scan on test (cost=0.00..1.21 rows=21 width=12)
EXPLAIN
--
Timothy H. Keitt
National Center for Ecological Analysis and Synthesis
735 State Street, Suite 300, Santa Barbara, CA 93101
Phone: 805-892-2519, FAX: 805-892-2510
http://www.nceas.ucsb.edu/~keitt/
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-07-29 02:55:00 | Re: select distinct |
Previous Message | Tom Lane | 2000-07-28 22:47:24 | Re: Fwd: Postgres update |