Re: Sequential Scan with LIMIT - Mailing list pgsql-performance

From Neil Conway
Subject Re: Sequential Scan with LIMIT
Date
Msg-id 1098689094.13261.445.camel@localhost.localdomain
Whole thread Raw
In response to Re: Sequential Scan with LIMIT  (Curt Sampson <cjs@cynic.net>)
List pgsql-performance
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



pgsql-performance by date:

Previous
From: Curt Sampson
Date:
Subject: Re: Sequential Scan with LIMIT
Next
From: Andrew Sullivan
Date:
Subject: Re: Performance Anomalies in 7.4.5