On Mon, 2004-10-25 at 17:17, Curt Sampson wrote:
> When you select all the columns, you're going to force it to go to the
> table. If you select only the indexed column, it ought to be able to use
> just the index, and never read the table at all.
Perhaps in other database systems, but not in PostgreSQL. MVCC
information is only stored in the heap, not in indexes: therefore,
PostgreSQL cannot determine whether a given index entry refers to a
valid tuple. Therefore, it needs to check the heap even if the index
contains all the columns referenced by the query.
While it would be nice to be able to do index-only scans, there is good
reason for this design decision. Check the archives for past discussion
about the tradeoffs involved.
> You could also use more
> standard and more set-oriented SQL while you're at it:
>
> SELECT DISTINCT(col) FROM mytable WHERE col = 'myval'
This is likely to be less efficient though.
-Neil