Re: We need index-only scans - Mailing list pgsql-hackers

From Bruce Momjian
Subject Re: We need index-only scans
Date
Msg-id 201011121417.oACEHK400807@momjian.us
Whole thread Raw
In response to Re: We need index-only scans  (Greg Stark <gsstark@mit.edu>)
Responses Re: We need index-only scans
List pgsql-hackers
Greg Stark wrote:
> On Wed, Nov 10, 2010 at 4:04 AM, Bruce Momjian <bruce@momjian.us> wrote:
> > We last researched index-only scans, also called covering indexes, in
> > September of 2008, but have made little progress on it since. ?Many have
> > been waiting for Heikki to implement this but I talked to him and he
> > doesn't have time.
> >
> > I believe it is time for the community to move forward and I would like
> > to assemble a team to work on this feature. ?We might not be able to
> > implement it for Postgres 9.1, but hopefully we can make some progress
> > on this.
> 
> 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.
> 
> In Oracle "Fast Full Index" scans are particularly useful for things
> like unconstrained select count(*). Since the scan can scan through
> the index sequentially and the index is much smaller than the table it
> can count all the values fairly quickly even on a very wide table.
> 
> In Postgres, aside from the visibility issues we have a separate
> problem. In order to achieve high concurrency we allow splits to occur
> without locking the index. And the new pages can be found anywhere in
> the index, even to the left of the existing page. So a sequential scan
> could miss some data if the page it's on is split and some of the data
> is moved to be to the left of where our scan is.
> 
> It's possible this is a non-issue in the future due to large RAM sizes
> and SSDs. Large amounts of RAM mean perhaps indexes will be in memory
> much of the time and SSDs might mean that scanning the btree in index
> order might not really be that bad.

Agreed.  I updated the index-only scans wiki for this:
http://wiki.postgresql.org/wiki/Index-only_scanstest speed improvement for scans of the entire index (this
involvesrandomI/O)    * we can't scan the index in physical order like vacuum does 
 

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +


pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: Re: [BUGS] BUG #5650: Postgres service showing as stopped when in fact it is running
Next
From: "Kevin Grittner"
Date:
Subject: Re: multi-platform, multi-locale regression tests