From: Tom Lane <tgl@sss.pgh.pa.us>
To: Adam Gundy <adam@starsilk.net>
Cc: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: varchar index joins not working?
Date: Mon, 14 Apr 2008 13:46:06 -0400
Message-ID: <11229.1208195166@sss.pgh.pa.us> (raw)
In-Reply-To: <48038E21.50606@starsilk.net>
References: <6f55f1270804092013n73ee1f81t2c6d72cee49655a3@mail.gmail.com>
<47FDD3D1.9070208@archonet.com>
<47FE29AF.6010906@starsilk.net>
<6f55f1270804101154t3d4a01eco2b7bff7a0053af38@mail.gmail.com>
<48030439.9040309@archonet.com>
<48038E21.50606@starsilk.net>
Adam Gundy <adam@starsilk.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, adam@starsilk.net, dev@archonet.com
Subject: Re: varchar index joins not working?
In-Reply-To: <11229.1208195166@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox