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.29 rows=1 width=32)
Join Filter: (("inner".vf)::text = "outer".tf)
-> Index Scan using ttpk on tt (cost=0.00..3.01 rows=1 width=16)
Index Cond: (id = 12345)
-> Seq Scan on vt (cost=100000000.00..100000531.68 rows=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 using vfi 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 |/
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
