Erik Jones <erik@xxxxxxxxxx> 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
