On Tue, Jun 25, 2013 at 11:06 AM, Bruce Momjian <br...@momjian.us> wrote: >> Not really. Constraint exclusion won't kick in for a constraint like >> CHECK (hashme(a) % 16 == 3) and a WHERE clause of the form a = 42. > > Uh, I thought we checked the constant against every CHECK constraint and > only scanned partitions that matched. Why does this not work?
That's a pretty fuzzy description of what we do. For this to work, we'd have to be able to use the predicate a = 42 to prove that hashme(a) % 16 = 3 is false. But we can't actually substitute 42 in for a and then evaluate hashme(42) % 16 = 3, because we don't know that the a = 42 in the WHERE clause means exact equality for all purposes, only that it means "has the numerically same value". For integers, equality under = is sufficient to prove equivalence. But for numeric values, for example, it is not. The values '42'::numeric and '42.0'::numeric are equal according to =(numeric, numeric), but they are not the same. If the hashme() function did something like length($1::text), it would get different answers for those two values. IOW, the theorem prover has no way of knowing that the hash function provided has semantics that are compatible with the opclass of the operator used in the query. >> Of course, since partitioning generally doesn't improve performance in >> PostgreSQL anyway, it's not clear why you'd want to do this in the > > I think partitioning does improve performance by reducing index depth. Generally, I think traversing an extra level of the index is cheaper than opening extra relations and going through the theorem-prover machinery. There are benefits to partitioning, but they have to do with management - e.g. each partition can be vacuumed independently; old partitions can be dropped more efficiently than you can bulk-delete rows spread throughout a table - rather than performance. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
