From: Luke Lonergan <llonergan@greenplum.com>
To: Weslee Bilodeau <weslee.bilodeau@hypermediasystems.com>
To: John Major <major@cbio.mskcc.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Help w/speeding up range queries?
Date: Tue, 31 Oct 2006 16:10:54 -0800
Message-ID: <C16D260E.5940%llonergan@greenplum.com> (raw)
In-Reply-To: <4547E2D0.3070100@hypermediasystems.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: llonergan@greenplum.com, weslee.bilodeau@hypermediasystems.com, major@cbio.mskcc.org
Subject: Re: Help w/speeding up range queries?
In-Reply-To: <C16D260E.5940%llonergan@greenplum.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox