Re: Expression indexes and casts

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Tue, 9 Mar 2004, Tom Lane wrote:
>> I think the problem is that explicit and implicit casts are marked
>> differently in the cast parse node, causing equal() to consider the two
>> expressions different.
>>
>> A narrower patch would be to change the cast type field to don't-care in
>> the copy of the parse tree that is made for planner uses.

I have applied a patch to CVS tip that does this.  It's fairly small and
would be safe to back-patch into 7.4 if you are feeling in need of a
near-term solution.  So for example:

regression=# create table foo (f1 int);
CREATE TABLE
regression=# create index fooi on foo ((f1::numeric));
CREATE INDEX
regression=# explain select * from foo where f1 = 44.44;
                           QUERY PLAN
-----------------------------------------------------------------
 Index Scan using fooi on foo  (cost=0.00..17.07 rows=5 width=4)
   Index Cond: ((f1)::numeric = 44.44)
(2 rows)

whereas 7.4 would fail to recognize that the index is applicable.

>> [ thinks some more... ]  On the other hand, there are cases where
>> explicit and implicit casting are actually semantically different (think
>> varchar() and char() length constraints).  Maybe the don't-care business
>> is itself a bug, and you're just stuck.

This concern was a red herring --- any semantic impact of the coercion
type is reflected in the parameters of the associated function call.
The CoercionForm itself doesn't affect much of anything except
reverse-listing in ruleutils.c.

> Is it possible to make an index on the implicitly cast version (or what
> would that take - I'm not sure how to syntactically note that in any
> case)?

I can't see a good way to do that either :-(

            regards, tom lane