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


Fri, 21 Mar 2003 00:20:15 +1100
Philip Warner <pjw@rhyme.com.au>
Varchar Vs. Text index matching - why different?
Philip Warner <pjw@rhyme.com.au> writes:
> 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.

But they're not the same datatype.

I have been wondering whether we couldn't eliminate the datatype
distinction between text and varchar.  Haven't gotten around to thinking
about it though; the mess in the numeric-datatype world seems higher
priority.

			regards, tom lane

Thu, 20 Mar 2003 10:18:12 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Varchar Vs. Text index matching - why different? 
At 10:18 AM 20/03/2003 -0500, Tom Lane wrote:
>I have been wondering whether we couldn't eliminate the datatype
>distinction between text and varchar.

That would be great if done magically in the backend. If all text columns 
were effectively wrapped in a Cast(X to text), life from a users PoV would 
be a little simpler. I assume Varchar(20) would still exist and be limited 
to 20 chars.



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


Fri, 21 Mar 2003 10:36:58 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Varchar Vs. Text index matching - why different? 