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

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@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461