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.
