Skip site navigation (1) Skip section navigation (2)

Re: We need index-only scans

From: Kristian Nielsen <knielsen(at)knielsen-hq(dot)org>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: We need index-only scans
Date: 2010-12-01 11:53:02
Message-ID: 87wrntemld.fsf@knielsen-hq.org (view raw or flat)
Thread:
Lists: pgsql-hackers
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.

In response to

Responses

pgsql-hackers by date

Next:From: Heikki LinnakangasDate: 2010-12-01 12:23:32
Subject: Re: Hi- How frequently Postgres Poll for trigger file
Previous:From: Magnus HaganderDate: 2010-12-01 11:47:13
Subject: Re: [HACKERS] Improved JDBC driver part 2

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group