Expression indexes and casts

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Expression indexes and casts
Date: 2004-03-09 15:28:49
Message-ID: 20040309071810.X98597@megazone.bigpanda.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron St-Pierre 2004-03-09 16:03:52 Re: [NOVICE] JDBC driver & local server
Previous Message Peter Eisentraut 2004-03-09 15:02:43 Re: a group of superuser