| From: | William Garrison <postgres(at)mobydisk(dot)com> |
|---|---|
| To: | Postgres General List <pgsql-general(at)postgresql(dot)org> |
| Subject: | Simple query not using index: why? |
| Date: | 2008-09-03 17:17:53 |
| Message-ID: | 48BEC6C1.3050303@mobydisk.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
I am looking for records with duplicate keys, so I am running this query:
SELECT
fileid, COUNT(*)
FROM
file
GROUP BY
fileid
HAVING
COUNT(*)>1
The table has an index on fileid (non-unique index) so I am surprised
that postgres is doing a table scan. This database is >15GB, and there
are a number of fairly large string columns in the table. I am very
surprised that scanning the index is not faster than scanning the
table. Any thoughts on that? Is scanning the table faster than
scanning the index? Is there a reason that it needs anything other than
the index?
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2008-09-03 17:21:25 | Re: hash partitioning |
| Previous Message | Oleg Bartunov | 2008-09-03 17:10:55 | Re: Case sensitive full text searching |