Thread: Expression indexes and casts

    I'm playing with a type that has no equality operator, but
does provide an implicit cast to text.  While working with this, I found
that the in some cases an expression index on the cast value doesn't
seem to be considered.

sszabo=# create index foo1i on foo1((a::text));
CREATE INDEX

sszabo=# set enable_seqscan=off;
SET

sszabo=# explain select * from foo1 where a='bbb';
                             QUERY PLAN
---------------------------------------------------------------------
 Seq Scan on foo1  (cost=100000000.00..100000001.05 rows=1 width=32)
   Filter: ((a)::text = 'bbb'::text)
(2 rows)

sszabo=# explain select * from foo1 where a::text='bbb'::text;
                            QUERY PLAN
-------------------------------------------------------------------
 Index Scan using foo1i on foo1  (cost=0.00..4.68 rows=1 width=32)
   Index Cond: ((a)::text = 'bbb'::text)

I haven't done any looking around yet (about to head off to work), but it
looks like in the case where the system decides to cast a to text in order
to get a working equality, the index isn't used, whereas in the case where
I explicitly cast it, it can.

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> I haven't done any looking around yet (about to head off to work), but it
> looks like in the case where the system decides to cast a to text in order
> to get a working equality, the index isn't used, whereas in the case where
> I explicitly cast it, it can.

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.

There is currently a hack involving a "don't care" setting for this
field, but it doesn't help you.  I wonder if it would be better to make
equal() explicitly ignore the cast-type field.  It seems like that could
break other things though :-(.

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 user.

[ 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.

            regards, tom lane

On Tue, 9 Mar 2004, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > I haven't done any looking around yet (about to head off to work), but it
> > looks like in the case where the system decides to cast a to text in order
> > to get a working equality, the index isn't used, whereas in the case where
> > I explicitly cast it, it can.
>
> 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.
>
> There is currently a hack involving a "don't care" setting for this
> field, but it doesn't help you.  I wonder if it would be better to make
> equal() explicitly ignore the cast-type field.  It seems like that could
> break other things though :-(.
>
> 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 user.
>
> [ 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.

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 don't really care about the explicit cast case actually.

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