Page access pattern in query plan using index scan

Поиск
Список
Период
Сортировка
От Jack Orenstein
Тема Page access pattern in query plan using index scan
Дата
Msg-id 40BE7322.1060103@geophile.com
обсуждение исходный текст
Ответы Re: Page access pattern in query plan using index scan  (Alvaro Herrera <alvherre@dcc.uchile.cl>)
Список 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



В списке pgsql-general по дате отправления:

Предыдущее
От: "Dann Corbit"
Дата:
Сообщение: Re: Running Totals and stuff...
Следующее
От: "TroyGeek"
Дата:
Сообщение: Re: PostgreSQL Tablespaces