Skip site navigation (1) Skip section navigation (2)

Re: varchar index joins not working?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Adam Gundy <adam(at)starsilk(dot)net>
Cc: Richard Huxton <dev(at)archonet(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: varchar index joins not working?
Date: 2008-04-14 17:46:06
Message-ID: 11229.1208195166@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Adam Gundy <adam(at)starsilk(dot)net> writes:
> hmm. unfortunately it did turn out to be (part) of the issue. I've 
> discovered that mixing char and varchar in a stored procedure does not 
> coerce the types, and ends up doing seq scans all the time.

Oh, it coerces the type all right, just not in the direction you'd like.

regression=# create table v (f1 varchar(32) primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "v_pkey" for table "v"
CREATE TABLE
regression=# explain select * from v where f1 = 'abc'::varchar;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Index Scan using v_pkey on v  (cost=0.00..8.27 rows=1 width=34)
   Index Cond: ((f1)::text = 'abc'::text)
(2 rows)

regression=# explain select * from v where f1 = 'abc'::char(3);
                    QUERY PLAN                     
---------------------------------------------------
 Seq Scan on v  (cost=0.00..25.88 rows=1 width=34)
   Filter: ((f1)::bpchar = 'abc'::character(3))
(2 rows)

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: Richard HuxtonDate: 2008-04-14 17:54:20
Subject: Re: varchar index joins not working?
Previous:From: Adam GundyDate: 2008-04-14 17:02:25
Subject: Re: varchar index joins not working?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group