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 18:01:16 -0400
Message-ID: <23669.1208210476@sss.pgh.pa.us> (raw)
In-Reply-To: <4803C8E9.2060907@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>
<11229.1208195166@sss.pgh.pa.us>
<4803C8E9.2060907@starsilk.net>
Adam Gundy <adam@starsilk.net> writes:
> Tom Lane wrote:
>> Oh, it coerces the type all right, just not in the direction you'd like.
> is there a reason it doesn't coerce to a type that's useful to the
> planner (ie varchar in my case),
In this case I think the choice is probably semantically correct:
shouldn't a comparison of varchar (trailing space sensitive) and
char (trailing space INsensitive) follow trailing-space-insensitive
semantics?
I wouldn't swear that the behavior is intentional ;-) as to going
that way rather than the other, but I'm disinclined to change it.
> or the planner doesn't accept any type
> of string as a valid match for index scan?
Can't. This equality operator doesn't have the same notion of equality
that that index does.
The long and the short of it is that mixing char and varchar is
hazardous.
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: <23669.1208210476@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