Re: Re: Adding additional index causes 20,000x slowdown for certain select queries - postgres 9.0.3

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Claudio Freire" <klaussfreire(at)gmail(dot)com>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Re: Adding additional index causes 20,000x slowdown for certain select queries - postgres 9.0.3
Date: 2011-03-16 19:42:07
Message-ID: 4D80CC3F020000250003B9BB@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:

> Forgive the naive question...
> but...
>
> Aren't all index scans, forward or backward, random IO?

No. Some could approach that; but, for example, an index scan
immediately following a CLUSTER on the index would be totally
sequential on the heap file access and would tend to be fairly close
to sequential on the index itself. It would certainly trigger OS
level read-ahead for the heap, and quite possibly for the index. So
for a lot of pages, the difference might be between copying a page
from the OS cache to the database cache versus a random disk seek.

To a lesser degree than CLUSTER you could get some degree of
sequencing from a bulk load or even from normal data insert
patterns. Consider a primary key which is sequentially assigned, or
a timestamp column, or receipt numbers, etc.

As Tom points out, some usage patterns may scramble this natural
order pretty quickly. Some won't.

-Kevin

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Tech Madhu 2011-03-16 21:27:17 Re: pg_xlog size
Previous Message Derrick Rice 2011-03-16 19:41:55 Re: Updating histogram_bounds after a delete