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 16:16:41 -0600
Message-ID: <4803D7C9.1070803@starsilk.net> (raw)
In-Reply-To: <23669.1208210476@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>
<4803C8E9.2060907@starsilk.net>
<23669.1208210476@sss.pgh.pa.us>
Tom Lane wrote:
> 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.
ahh. I forgot about the trailing spaces. but you can always coerce a
char to a varchar safely, which would have fixed my issue. you can't
coerce the other way, as you say, because you'll lose the trailing spaces...
alternatively, can the planner give warnings somehow? or suggestions? eg
some messages from 'explain analyze' like:
'I could make your query go much faster IF ...'
or
'suggestion: create an index on ...'
'suggestion: convert this index to ...'
or
'warning: I'd really like to use this index, BUT ...'
>> 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.
no kidding.
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: <4803D7C9.1070803@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