Erik Jones <erik@myemma.com> writes: > You beat me to the punch on this one. I was wanting to use modulo > operations for bin style partitioning as well, but this makes things > pretty awkward as well as unintuitive. So, to the postgres gurus: > What are the limitations of check constraints when used with > constraint exclusion? Is this really the intended behavior? Don't hold your breath. predtest.c has some intelligence about btree-indexable comparison operators, but none about modulo. In the particular case here, the reason that WHERE (foo % 10) = 3 is seen to be incompatible with a check constraint (foo % 10) = 9 is that the "=" is btree indexable, so predtest knows something about its semantics; and given that % is an immutable operator, the code is able to see that these could only both be true if 3 = 9. This deduction involves exactly zero %-specific knowledge. In particular it doesn't require assuming that "a=b" implies "(a % c) = (b % c)", which would involve much more knowledge about the specific operators involved than is available to the planner. (The fact that an operator is a btree equality member doesn't mean that it might not consider two values to be equal that are distinct to some other operators of the data type. See plus and minus zero in IEEE float arithmetic for one handy example ... and that's not even considering nonstandard versions of equality.) regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Erik Jones <erik@myemma.com> writes: > Forgive me if I'm nagging on this, I just want to understand this > better. Why does evaluating a CHECK constraint like 'CHECK some_id % > 100 = 32' against WHERE clause like 'WHERE some_id=1132' need to know > anything about equality properites of %? Or, rather, why does it > stop there? Can't it just substitute the given value for some_id in > to the check expression, execute it and check the result value for > TRUE/FALSE? What "given value"? What you're missing is that the condition "a = b" does not mean that "f(a) = f(b)" for every function f. It is possible to define constraints on equality that would make that true, but such constraints would be far stronger than what is required to make btree (or even hash) indexes work. In the example I gave, we are able to conclude that 3 is unequal to 9 not because of any a-priori knowledge, but because we apply the specific operator to the specific constants and find out that it yields false. Our knowledge of the consistency requirements that are imposed on btree equality operators then allows us to determine that the two original conditions can't be true at the same time. This does *not* imply assuming that the two constants are really "the same" in the sense that no other operator in the system could tell them apart. This isn't mere academic hairsplitting: there actually are standard equality operators in the system for which such a conclusion would fail. I already mentioned float comparison, and numeric comparison has similar behaviors --- for instance, regression=# select '0.00'::numeric = '0.0'::numeric; ?column? ---------- t (1 row) regression=# select text('0.00'::numeric) = text('0.0'::numeric); ?column? ---------- f (1 row) regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Erik Jones <erik@myemma.com> writes: > I guess what I don't understand is that given the query > SELECT COUNT(*) > FROM table > WHERE some_id=34; > on a table with the much discussed constraint (34 % 100) = 32 isn't > simply evaluated as a one-time filter whenever whatever constraint > exclusion code examines child partition tables' constraints. I'm not sure how else to explain it: the fact that the WHERE clause asserts that some operator named "=" will succeed on some_id and 34 is not sufficient grounds to assume that "some_id % 100" and "34 % 100" will give the same result. Knowing that the "=" operator is a btree equality operator gives us latitude to make certain conclusions, but not that one, because there is no way to know whether the semantics of the particular btree operator class have anything to do with the behavior of "%". If you dig in the PG archives you will find some discussions of inventing a "real equality" flag for operators, which would authorize the planner to make such deductions for any immutable operator/function. The idea hasn't gone anywhere, partly because it's not clear that it would really help in very many common cases. The fact that we could *not* set the flag on such common cases as float and numeric equality is a bit discouraging in that connection. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

"Trevor Talbot" <quension@gmail.com> writes: > Erik is questioning is why it has to assume anything. Why can't it > just execute the expression and find out? Because the whole point of the problem is to *not* execute the expression, but to assume that it must yield false, for every row of a given partition. Without a solid logical basis for that assumption, you're just building a house of cards. The bottom line here is that we have built a partitioning facility out of spare parts, ie, a very generalized contradiction-proving section of the planner. It's been an interesting exercise, and it's certainly resulted in a better contradiction-prover than we would have had otherwise, but it's got obvious limitations both in planning performance and in the sorts of partitioning rules we can support. My feeling is that trying to push the current approach to do bin or hash partitioning transparently is likely not reasonable. Eventually we'll have to push an understanding of partitioning down to some lower level of the system --- that is, if we think it's critical enough to justify that much effort. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

Vivek Khera <khera@kcilink.com> writes: > I, along with at least Erik, was thinking that the constraint > expression would be evaluated to determine whether to include the > partition in the final plan. Based on Tom's description, it is not > the case: the planner basically proves that the constraint will be > false. Until this was clarified, Tom's points totally confused the > heck out of me. > It would be amazingly wonderful if this distinction could be posted to > the online docs. It will surely help future generations :-) Feel free to send in a proposed doc patch. I'm not very clear on where you think this should go or what it should say instead of what it does say. BTW, I always think of it the other way around: we're proving that the WHERE condition must be false for any row meeting the check constraint. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings