DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=subject:mime-version:content-type:from:in-reply-to:date:cc
	:content-transfer-encoding:message-id:references:to:x-mailer;
	b=Geq4O+2ZQBOYdQwHBZtn4Gdaek2oTYcrisvekgz84Hz/SmQ7KgzYTPjyJ06b/eyr7h
	FKMob4jjSsjOrJ/OKLgyfdmblKIXVKdOVLQ+MdFxCZxrnsrM3KZCHrbK6S+gL3Vjtioq
	SqVs+vMLd/RAMHuYMGkXW7sjP72JjY1BymV2Y=
Subject: Re: partitioning question 1
Mime-Version: 1.0 (Apple Message framework v1081)
Content-Type: text/plain; charset=us-ascii
From: Ben <midfield@gmail.com>
In-Reply-To: <1288287092.22359.23.camel@jd-desktop>
Date: Thu, 28 Oct 2010 11:44:41 -0700
Cc: pgsql-performance@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <E03148FE-26C6-47BA-9463-B97749BE3E8C@gmail.com>
References: <A9A748D8-A52F-4C16-A303-3A4AA1EC1BD2@gmail.com>
	<1288287092.22359.23.camel@jd-desktop>
To: jd@commandprompt.com

thanks for the prompt response.  some comments / questions below :

On Oct 28, 2010, at 10:31 AM, Joshua D. Drake wrote:
>> ...constraint exclusion is able to eliminate table partitions.  the =
I/O advantages of having queries target small subtables are the same as =
the I/O advantages of clustering the index : result pages in a small =
range are very close to each other on disk.
>=20
> Not entirely true. One a clustered index will not stay clustered if =
you
> are still updating data that is in the partition. You shouldn't
> underestimate the benefit of smaller relations in terms of maintenance
> either.

in my situation, the update come in-order (it is timeseries data and the =
clustered index is on time.)  so the table should remain relatively =
clustered.  updates also happen relatively infrequently (once a day in =
one batch.)  so it appears that we will continue to get the I/O benefits =
described above.

are there any other benefits which partitioning provides for query =
performance (as opposed to update performance) besides the ones which i =
have mentioned?


> Yes the constraints have to be static. Not sure about the operator
> question honestly.

this seems to severely restrict their usefulness -- our queries are data =
warehouse analytical -type  queries, so the constraints are usually =
data-driven (come from joining against other tables.)

>> is my intuition completely off on this?
>=20
> You may actually want to look into expression indexes, not clustered
> ones.


what would expression indexes give me?

thanks and best regards, ben