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@sss.pgh.pa.us>
> To: pgsql-hackers@postgreSQL.org
> References: <28999.961374238@sss.pgh.pa.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
>