Skip site navigation (1) Skip section navigation (2)

Re: very slow left join

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: Ben <bench(at)silentmedia(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: very slow left join
Date: 2008-05-16 18:27:09
Message-ID: dcc563d10805161127g6a1634eu9a3fed7607cc41d1@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, May 16, 2008 at 12:21 PM, Ben <bench(at)silentmedia(dot)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...

In response to

Responses

pgsql-performance by date

Next:From: BenDate: 2008-05-16 18:43:12
Subject: Re: very slow left join
Previous:From: Simon RiggsDate: 2008-05-16 18:23:55
Subject: Re: I/O on select count(*)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group