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