I just noticed that comparing a text to a varchar results in an index not
being used without a cast in some circumstances. Given that they have
identical internal representations (or so I believe), that seems a little odd.
Can anyone shed some light on this for me? I assume it has something to do
with believing text->varchar is a down-cast due to the size limitation on
varchar, but don't we collect size stats of text fields (stawidth)?
The following illustrates the behaviour:
create table tt(id serial, tf text);
create unique index tfi on tt(tf);
create unique index ttpk on tt(id);
insert into tt(tf) values('fred' || currval('tt_id_seq'));
insert into tt(tf) select 'fred' || currval('tt_id_seq') from tt;
create table vt(id serial, vf varchar(31));
create unique index vfi on vt(vf);
create unique index vtpk on vt(id);
insert into vt(vf) select 'fred' || currval('vt_id_seq') from tt;
analyze;
explain select * from tt,vt where tt.id=12345 and vt.vf = tt.tf; QUERY PLAN
----------------------------------------------------------------------------- Nested Loop
(cost=100000000.00..100000944.29rows=1 width=32) Join Filter: (("inner".vf)::text = "outer".tf) -> Index Scan
usingttpk on tt (cost=0.00..3.01 rows=1 width=16) Index Cond: (id = 12345) -> Seq Scan on vt
(cost=100000000.00..100000531.68rows=32768 width=16)
(5 rows)
rt3=# explain select * from tt,vt where tt.id=12345 and vt.vf = cast(tt.tf
as varchar); QUERY PLAN
---------------------------------------------------------------------- Nested Loop (cost=0.00..7.92 rows=1 width=32)
-> Index Scan using ttpk on tt (cost=0.00..3.01 rows=1 width=16) Index Cond: (id = 12345) -> Index Scan
usingvfi on vt (cost=0.00..4.89 rows=1 width=16) Index Cond: (vt.vf = ("outer".tf)::character varying)
(5 rows)
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \| | --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
