Page access pattern in query plan using index scan

From: Jack Orenstein <jao(at)geophile(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Page access pattern in query plan using index scan
Date: 2004-06-03 00:38:58
Message-ID: 40BE7322.1060103@geophile.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message TroyGeek 2004-06-03 01:10:18 Re: PostgreSQL Tablespaces
Previous Message Bruce Momjian 2004-06-02 19:53:20 Re: Building Thread-Safe on OS X