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=NOQdhBHla0YMaM9czZNYsQ7d9sfPnu3yJTM61BhQD738dZCvSVSJtUskuG1dLEaVsi
	oScnxalyXl6kvCgBOWlOGJLA+q0EGO9VE5J7FZBqfSDVhktg3TBBx5mkid5h4bs5D1SR
	N6JwS9+cCoA0YbBzRGrHLCIryGt5AynKPoAwE=
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: <1288291814.22359.31.camel@jd-desktop>
Date: Thu, 28 Oct 2010 12:25:16 -0700
Cc: pgsql-performance@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <4486C61F-DC08-4DE4-8AF9-245FA60B0310@gmail.com>
References: <A9A748D8-A52F-4C16-A303-3A4AA1EC1BD2@gmail.com>
	<1288287092.22359.23.camel@jd-desktop>
	<E03148FE-26C6-47BA-9463-B97749BE3E8C@gmail.com>
	<1288291814.22359.31.camel@jd-desktop>
To: jd@commandprompt.com


On Oct 28, 2010, at 11:50 AM, Joshua D. Drake wrote:
>>> Yes the constraints have to be static. Not sure about the operator
>>> question honestly.
>>=20
>> 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.)
>=20
> Well it does and it doesn't. Keep in mind that the constraint can be:
>=20
> date >=3D '2010-10-01" and date <=3D '2010-10-31'
>=20
> What it can't be is something that contains date_part() or extract() =
(as
> an example)=20

i think we are talking about two different things here: the constraints =
on the table, and the where-clause constraints in a query which may or =
may not trigger constraint exclusion.  i understand that table =
constraints have to be constants -- it doesn't make much sense =
otherwise.  what i am wondering about is, will constraint exclusion be =
triggered for queries where the column that is being partitioned on is =
being constrained things that are not static constants, for instance, in =
a join.  (i'm pretty sure the answer is no, because i think constraint =
exclusion happens before real query planning.)  a concrete example :

create table foo (i integer not null, j float not null);
create table foo_1 (check ( i >=3D 0 and i < 10) ) inherits (foo);
create table foo_2 (check ( i >=3D 10 and i < 20) ) inherits (foo);
create table foo_3 (check ( i >=3D 20 and i < 30) ) inherits (foo);
etc..

create table bar (i integer not null, k float not null);

my understanding is that a query like

select * from foo, bar using (i);

can't use constraint exclusion, even if the histogram of i-values on =
table bar says they only live in the range 0-9, and so the query will =
touch all of the tables.  i think this is not favorable compared to a =
single foo table with a well-maintained btree index on i.

>>>> is my intuition completely off on this?
>>>=20
>>> You may actually want to look into expression indexes, not clustered
>>> ones.
>=20
> Take a look at the docs:
>=20
> =
http://www.postgresql.org/docs/8.4/interactive/indexes-expressional.html
>=20
> It "could" be considered partitioning without breaking up the table,
> just the indexes.

do you mean partial indexes?  i have to confess to not understanding how =
this is relevant -- how could partial indexes give any advantage over a =
full clustered index?

b=20=