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

From Heikki Linnakangas
Subject Re: We need index-only scans
Date
Msg-id 4CDD48C3.2010300@enterprisedb.com
Whole thread Raw
In response to Re: We need index-only scans  (Alvaro Herrera <alvherre@commandprompt.com>)
Responses Re: We need index-only scans
List pgsql-hackers
On 12.11.2010 15:51, Alvaro Herrera wrote:
> Excerpts from Greg Stark's message of vie nov 12 10:33:28 -0300 2010:
>
>> 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.
>
> Eh?

It took me a while to understand what Greg meant as well. You can't scan 
a B-tree index in *physical order*, You have to first descend to the 
leftmost leaf, and follow the right pointers from there until you reach 
the rightmost leaf. That is a lot slower than seqscanning a file in 
physical order.

We solved that for VACUUM, taking advantage of the fact that there can 
only be one VACUUM on a table at a time. Maybe that mechanism could be 
generalized to all scans, but it would require some thinking..

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com


pgsql-hackers by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: We need index-only scans
Next
From: Bruce Momjian
Date:
Subject: Re: duplicate connection failure messages