Skip site navigation (1) Skip section navigation (2)

Re: partitioning question 1

From: Ben <midfield(at)gmail(dot)com>
To: jd(at)commandprompt(dot)com
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: partitioning question 1
Date: 2010-10-28 19:25:16
Message-ID: 4486C61F-DC08-4DE4-8AF9-245FA60B0310@gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.
>> 
>> 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) 

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 >= 0 and i < 10) ) inherits (foo);
create table foo_2 (check ( i >= 10 and i < 20) ) inherits (foo);
create table foo_3 (check ( i >= 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?
>>> 
>>> 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.

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 

In response to

Responses

pgsql-performance by date

Next:From: Joshua D. DrakeDate: 2010-10-28 19:44:12
Subject: Re: partitioning question 1
Previous:From: Joshua D. DrakeDate: 2010-10-28 18:50:14
Subject: Re: partitioning question 1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group