Re: We need index-only scans

Greg Stark <gsstark(at)mit(dot)edu> writes:

> Just so everyone is on the same page.... Even once we have index-only
> scans they won't be anywhere near as useful with Postgres as they are
> with Oracle and other databases. At least not unless we find a
> solution for a different problem -- our inability to scan btree
> indexes sequentially.

True, however I would not be too pessimistic about this.

For OLTP like typical web applications, index-only scans are a killer feature
for being able to read N rows with 1 I/O (for some small N), when the data no
longer fits in the buffer pool, or after cold start.

Eg. read all account settings for one user account, or subjects of all
messages, etc. A composite index with user_id in the first column allows to
fetch all N rows from one (or a few) disk pages with an index-only scan, as
opposed to N disk pages.

So for this, index-only scans can make a _big_ difference, even without
support for Oracle-type index fast-full-scans.

- Kristian.