Re: New thoughts about indexing cross-type comparisons

If this is only dealing with constants, why not just explicitly add a
cast to the constant of the column type at the planner level. It would
solve this problem as well ...

create table test (f int2);
select * from test where f=cast('1981928928921' as int2);
ERROR: pg_atoi: error reading "1981928928921": Numerical result out of
range

select * from test where f=1981928928921;
f
---
(0 rows)

Tom Lane wrote:
> We've spent much effort trying to solve the "int8col = 42 doesn't use
> an index" class of problems. AFAIR, all the recent tries have focused
> on trying to get the parser to choose an index-compatible operator
> initially. (In this example, that would mean promoting 42 to int8 so
> that int8 = int8 would be chosen at parse time.) While I have not
> completely given up hope on that approach, it's clearly fraught with
> potential for unwanted side-effects.
>
> The other place we could try to fix it is to improve either the planner
> or the index AMs themselves to cope with cross-type comparisons for
> themselves. I have shied away from that thought because it seemed
> unreasonably difficult, but with the parser-side solution looking harder
> and harder, maybe it's time to re-evaluate.
>
> There actually was code in the planner at one time to substitute
> index-compatible operators for non-index-compatible operators.
> The algorithm was basically "if I have indexcolumn OP somevalue, where
> OP isn't a member of the index opclass but also somevalue is of a
> different datatype than indexcolumn, then look to see if somevalue can
> be binary-coerced to the datatype of indexcolumn. If so, look for an
> operator of the same name as OP and accepting the indexcolumn datatype
> on both sides. If found, and it's a member of the index opclass, then
> use that operator instead of the original."
>
> This algorithm was wrong on both practical and theoretical levels;
> in the first place it's not very helpful to only be able to handle
> binary-compatible transformations, and in the second place there isn't
> any good guarantee that it's not changing the semantics when it replaces
> the operator. For instance int4 < and oid < do not act the same.
> Depending on equality of operator names was a bad idea even then, and
> would be quite unworkable now in the world of schema search paths.
>
> However, those objections really stem from the planner not having enough
> information to know when the transformation was safe to make. What if
> we gave it that information?
>
> After some thought I think the most practical approach is to make a new
> system catalog for "secondary members of index opclasses". A secondary
> member is not one of the operators that the index can handle directly,
> but it can be transformed into one of the primary members. The new
> catalog would be called, say, pg_amsecop, and would have columns like
> opclass operatorid replacementop ltransform rtransform
> where the first two are the primary key. The idea is when we have a
> WHERE expression "indexcol OP somevalue", and we can't find OP in the
> index's operator class (ie, there's no pg_amop entry for that operator
> and opclass), we next look to see if the opclass/operator combination
> appears in pg_amsecop. If so, then we are allowed to replace the
> "indexcol OP somevalue" expression by "ltransform(indexcol)
> REPLACEMENTOP rtransform(somevalue)". ltransform and rtransform are
> pg_proc OIDs of cast functions, or zero where no run-time cast is
> needed. replacementop is the OID of the substitute operator, which
> presumably is one of the primary members of the index opclass. Now we
> have an indexable expression. The presence of the entry in pg_amsecop
> represents the opclass designer's promise to us that this is a valid,
> semantics-preserving transformation.
>
> This design might be overly general --- for example, I doubt there can
> be any need to apply a cast function to the indexable column. We could
> eliminate the ltransform column and probably also halve the number of
> entries in the table if we insist that the indexable column be on the
> left before we start looking (in other words, "somevalue OP indexcol"
> must be commuted before we look for the operator in pg_amsecop, not after).
>
> This is not a 100% solution to our problems. I don't think we could use
> it to solve the problem for int2 columns ("int2col = 42") because it'd
> be unsafe to promise that an int4-to-int2 cast could be inserted into
> an expression without changing the behavior. So we'd still want to look
> at having small integer constants be initially typed as int2, which
> leaves us with a number of unsolved issues, as noted here:
> http://archives.postgresql.org/pgsql-hackers/2002-11/msg00468.php
> So maybe the "secondary operator" idea will help, or maybe it won't do
> much for us.
>
> Comments? Does this spur any better ideas?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>