User-Agent: Microsoft-Entourage/11.2.5.060620
Date: Tue, 31 Oct 2006 16:10:54 -0800
Subject: Re: Help w/speeding up range queries?
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Weslee Bilodeau" <weslee.bilodeau@hypermediasystems.com>,
	"John Major" <major@cbio.mskcc.org>
cc: pgsql-performance@postgresql.org
Message-ID: <C16D260E.5940%llonergan@greenplum.com>
Thread-Topic: [PERFORM] Help w/speeding up range queries?
Thread-Index: Acb9SjJNcOUqRGk9EduBWQAWy4o9DA==
In-Reply-To: <4547E2D0.3070100@hypermediasystems.com>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

Weslee,

On 10/31/06 3:57 PM, "Weslee Bilodeau"
<weslee.bilodeau@hypermediasystems.com> wrote:

> Basic question - What version, and what indexes do you have?

I'd expect the problem with this is that unless the indexed column is
correlated with the loading order of the rows over time, then the index will
refer to rows distributed non-sequentially on disk, in which case the index
can be worse than a sequential scan.

You can cluster the table on the index (don't use the "CLUSTER" command! Do
a CREATE TABLE AS SELECT .. ORDER BY instead!), but the index won't refer to
sequential table data when there's more data added.  What this does is
analogous to the partitioning option though, and you don't have the problem
of the table being de-clustered on the constraint column.

The problem with the current support for partitioning is that you have to
implement rules for inserts/updates/deletes so that you can do them to the
parent and they will be implemented on the children.  As a result,
partitioning is not transparent.  OTOH, it achieves great performance gains.

BTW - If you have a date column and your data is loaded in date order, then
an index is all that's necessary, you will get sequential access.
 
- Luke