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 [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster