Suppose I have a table as follows:
testdb=> \d person
Table "public.person"
Column | Type | Modifiers
------------+-------------------------+-----------
id | integer | not null
age | integer |
other_info | character varying(1000) |
Indexes: person_pkey primary key btree (id),
idx_person_age btree (age)
Here is the execution plan for a not very selective query on age:
testdb=> explain select * from person where age between 30 and 40;
QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using idx_person_age on person (cost=0.00..17.08 rows=5 width=524)
Index Cond: ((age >= 30) AND (age <= 40))
(2 rows)
What is the pattern of access to data pages? I can think of two likely
answers:
1) The index is scanned for ages 30 through 40. As each index entry is
scanned, a row is retrieved.
2) The index is scanned for ages 30 and 40. The index entries are
sorted so that rows on the same page are grouped together, increasing
the odds that a needed page will be present in the shared buffers.
I'm using 7.3.4, and will be upgrading to 7.4.2 soon.
Jack Orenstein
