DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=oTkyPlrSlNtFHE9UoPASAj0d7fWSuV8PYYdNokyCR+mC4onkxoOg/Bw1DxceWhE8b5KF6mBegiHMpGVj6fiesZeqGy3UY/V2NuK3KUFr5TzXchT4bPyK1W3B5MBA8AEtouInBmFjS2XS9y158H5WuKa+7VMMHdj6XCBVQej/Mlk=
Message-ID: <dcc563d10805161127g6a1634eu9a3fed7607cc41d1@mail.gmail.com>
Date: Fri, 16 May 2008 12:27:09 -0600
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: Ben <bench@silentmedia.com>
Subject: Re: very slow left join
Cc: pgsql-performance@postgresql.org
In-Reply-To: <Pine.LNX.4.64.0805161113520.3347@localhost.localdomain>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <Pine.LNX.4.64.0805131416080.3347@localhost.localdomain>
	<dcc563d10805161109v575b3498w2f396af5af3673a2@mail.gmail.com>
	<Pine.LNX.4.64.0805161113520.3347@localhost.localdomain>

On Fri, May 16, 2008 at 12:21 PM, Ben <bench@silentmedia.com> wrote:
> On Fri, 16 May 2008, Scott Marlowe wrote:
>
>> Just for giggles, try running the query like so:
>>
>> set enable_nestloop = off;
>> explain analyze ...
>>
>> and see what happens.  I'm guessing that the nested loops are bad choices
>> here.
>
> You guess correctly, sir! Doing so shaves 3 orders of magnitude off the
> runtime. That's nice. :) But that brings up the question of why postgres
> thinks nested loops are the way to go? It would be handy if I could make it
> guess correctly to begin with and didn't have to turn nested loops off each
> time I run this.

Well, I'm guessing that you aren't in locale=C and that the text
functions in your query aren't indexed.  Try creating an index on them
something like:

create index abc_txtfield_func on mytable (substring(textfield,1,5));

etc and see if that helps.

As for the char type, I totally understand the issue, having inherited
oracle dbs before...