Subject: Re: partitioning question 1
From: "Joshua D. Drake" <jd@commandprompt.com>
Reply-To: jd@commandprompt.com
To: Ben <midfield@gmail.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <E03148FE-26C6-47BA-9463-B97749BE3E8C@gmail.com>
References: <A9A748D8-A52F-4C16-A303-3A4AA1EC1BD2@gmail.com>
	<1288287092.22359.23.camel@jd-desktop>
	<E03148FE-26C6-47BA-9463-B97749BE3E8C@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Organization: Command Prompt, Inc.
Date: Thu, 28 Oct 2010 11:50:14 -0700
Message-ID: <1288291814.22359.31.camel@jd-desktop>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Thu, 2010-10-28 at 11:44 -0700, Ben wrote:

> > 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.)

Well it does and it doesn't. Keep in mind that the constraint can be:

date >= '2010-10-01" and date <= '2010-10-31'

What it can't be is something that contains date_part() or extract() (as
an example) 

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

Take a look at the docs:

http://www.postgresql.org/docs/8.4/interactive/indexes-expressional.html

It "could" be considered partitioning without breaking up the table,
just the indexes.

Sincerely,

Joshua D. Drake


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt