Re: int24_ops and int42_ops are bogus

>

> ------------------------------------------------------------------------
>
> Subject: Re: int24_ops and int42_ops are bogus
> Date: Mon, 19 Jun 2000 00:52:28 -0400
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> To: pgsql-hackers(at)postgreSQL(dot)org
> References: <28999(dot)961374238(at)sss(dot)pgh(dot)pa(dot)us>
>
> I wrote:
> > I think we ought to assume that index manipulation deals with only
> > one datatype for any given index, and therefore these two opclasses
> > are broken by design and must be removed.
>
> I have removed these two opclasses from the system. I had a further
> thought on the issue, which I just want to record in the archives
> in case anyone ever comes back and wants to resurrect
> int24_ops/int42_ops.
>
> The real design problem with these two opclasses is that if you want
> to have an int4 column that you might want to compare against either
> int2 or int4 constants, you have to create *two* indexes to handle
> the two cases. The contents of the two indexes will be absolutely
> identical, so this approach is inherently silly. The right way to
> attack it is to extend the opclass/amop information so that the
> system could understand that a plain-vanilla int4 index might be
> used with int4 vs int2 operators to compare against int2 constants
> --- or with int4 vs int8 operators to compare against int8 constants,
> etc.
>
> It would not be real difficult to extend the opclass representation
> to show these relationships, I think. The hard part is that btree
> (and probably the other index types) is sloppy about whether it is
> comparing index entries or externally-supplied values and which side
> of the comparison is which. Cleaning that up would be painful and
> maybe impractical --- but if it could be done it'd be nifty.
>
> The path I think we will actually pursue, instead, is teaching the
> planner to coerce constants to the same type as the compared-to
> column. For instance, given "int2var < int4constant" the planner
> will try to coerce the constant to int2 so that it can apply
> int2-vs-int2 operators with an int2 index. This falls down on
> cases like "int2var < 100000" because it won't be possible to
> reduce the constant to int2, whereas the above-sketched idea could

But since ALL int2var values in the table are in fact less than 100000, this expression is easily optimized to TRUE. And, I think, similar optimizations can be
found for other out of range values.

>
> still handle that case as an indexscan. But in terms of actual
> everyday usefulness, I doubt this is a serious limitation.
>
> regards, tom lane
>