Re: Bug in predicate indexes? - Mailing list pgsql-hackers

"Jim C. Nasby" <jnasby@pervasive.com> writes:
> On Fri, Nov 18, 2005 at 09:58:24AM -0500, Tom Lane wrote:
>> This is a known (although perhaps not well documented) limitation of the
>> predicate testing logic.  You do not need a cast in the query, though,
>> only in the index's WHERE condition.

> I'm working on a docs patch for this (attached, but un-tested); is
> bigint the only datatype this applies to or are there others?

You should find out what the problem is before you start writing
documentation about it ;-).  This has nothing whatever to do with
bigint.

The problem is here in predtest.c:
    * Try to find a btree opclass containing the needed operators.    *    * We must find a btree opclass that contains
bothoperators, else the    * implication can't be determined.  Also, the pred_op has to be of    * default subtype
(implyingleft and right input datatypes are the same);    * otherwise it's unsafe to put the pred_const on the left
sideof the    * test.  Also, the opclass must contain a suitable test operator matching    * the clause_const's type
(whichwe take to mean that it has the same    * subtype as the original clause_operator).
 

What the code is trying to do is prove that "X op C1" implies "X op C2"
where the constants aren't necessarily the same and the operators are
drawn from the same btree opclass, but might themselves be different.
Some examples:
X = 4    implies X > 3, because 4 > 3X <= 7    implies X < 3, because 7 < 3X > 7    doesn't imply X < 14

To make the proof, we need to compare C1 to C2, which means we have to
find the required operator for that in the same btree opclass as the two
given operators.  If X, C1, and C2 are all of different datatypes then
this is not going to work in the current design of operator classes,
because *all* the operators in a given opclass have the same lefthand-side
datatype.  (I've been thinking about ways to relax that in future PG
versions, but don't yet have a proposal to make.)  The current code
requires X and C2 to be of the same type, which means that the needed
operator for "C2 op C1" will be in the same opclass in which we can find
the operator for X op C1.

The bottom line is that if you want the predicate prover to be at all
smart about a comparison in the index WHERE clause, the comparison can't
be cross-type.  Otherwise, the only way it will match it is with an
exact match to the query's WHERE clause.  Example: this will still work
query: WHERE bigintcol = 42index: WHERE bigintcol = 42

but not this:
query: WHERE bigintcol = 42index: WHERE bigintcol >= 4

The last case needs "bigintcol >= 4::bigint" in the index predicate in
order to be provable from a related-but-not-identical query condition.

This applies to anyplace where we have cross-type comparisons, which
in a quick look in pg_operator seems to be
<(integer,bigint)<(bigint,integer)<(smallint,integer)<(integer,smallint)<(real,double precision)<(double
precision,real)<(smallint,bigint)<(bigint,smallint)<(date,timestampwithout time zone)<(date,timestamp with time
zone)<(timestampwithout time zone,date)<(timestamp with time zone,date)<(timestamp without time zone,timestamp with
timezone)<(timestamp with time zone,timestamp without time zone)
 

I'm not sure this is worth documenting given that it's likely to change
by 8.2 anyway.
        regards, tom lane