From: Adam Gundy <adam@starsilk.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: varchar index joins not working?
Date: Mon, 14 Apr 2008 15:13:13 -0600
Message-ID: <4803C8E9.2060907@starsilk.net> (raw)
In-Reply-To: <11229.1208195166@sss.pgh.pa.us>
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>
<11229.1208195166@sss.pgh.pa.us>
Tom Lane wrote:
> 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)
yeah. not terribly helpful.. you'd have to assume I'm not the only one
this has bitten..
is there a reason it doesn't coerce to a type that's useful to the
planner (ie varchar in my case), or the planner doesn't accept any type
of string as a valid match for index scan? I would think the benefits of
being able to index scan always outweigh the cost of type conversion...
hmm. I only saw this with stored procs, but it's obviously generic. I
think the reason I didn't see it with straight SQL or views is that it
seems to work correctly with string constants.. coercing them to the
correct type for the index scan. with a stored proc, all the constants
are passed in as args, with char() type (until I fixed it, obviously!)
Attachments:
smime.p7s (application/x-pkcs7-signature, 3.2K) download
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: adam@starsilk.net, tgl@sss.pgh.pa.us, dev@archonet.com
Subject: Re: varchar index joins not working?
In-Reply-To: <4803C8E9.2060907@starsilk.net>
* 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