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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
