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