Re: Bug in predicate indexes?

On Mon, Nov 21, 2005 at 08:40:38PM -0500, Tom Lane wrote:
> You should find out what the problem is before you start writing
> documentation about it ;-). This has nothing whatever to do with
> bigint.

Damn, there's 5 minutes of my life that I won't get back! ;P

<snip>
> 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 > 3
> X <= 7 implies X < 3, because 7 < 3
Erm... shouldn't that be because 3 < 7 ? :)
> X > 7 doesn't imply X < 14
<snip>
> 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 = 42
> index: WHERE bigintcol = 42
>
> but not this:
>
> query: WHERE bigintcol = 42
> index: 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.

I assume part of this is due to how we cast bare numbers?

> 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,timestamp without time zone)
> <(date,timestamp with time zone)
> <(timestamp without time zone,date)
> <(timestamp with time zone,date)
> <(timestamp without time zone,timestamp with time zone)
> <(timestamp with time zone,timestamp without time zone)

I think it's more than that, but my query might be off...
decibel=# select count(*) from (select distinct l.typname,r.typname from
pg_opclass c join pg_operator o on (c.opcintype=o.oprleft) join pg_type
l on (o.oprleft=l.oid) join pg_type r on (o.oprright=r.oid)) a;
88

(that's 8.0.3, btw)

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

I agree with Josh that this should be documented backwards... assuming
that my count of 88 is correct, I think it's best to just specify that
it's recommended to always explicitely cast any constants in a
predicate.

Let me know if I'm on the wrong track with any of this, otherwise I'll
work on a set of patches.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461