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

Re: very slow left join

From: Ben <bench(at)silentmedia(dot)com>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: very slow left join
Date: 2008-05-16 18:21:04
Message-ID: Pine.LNX.4.64.0805161113520.3347@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-performance
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.


>>                         Table "public.event"
>>     Column     |            Type             |       Modifiers
>> ----------------+-----------------------------+------------------------
>>  clientkey      | character(30)               | not null
>>  premiseskey    | character(30)               | not null
>>  eventkey       | character(30)               | not null
>>  severitykey    | character(30)               |
>
> Do these really need to be character and not varchar?  varchar / text
> are better optimized in pgsql, and character often need to be cast
> anyway, so you might as well start with varchar.  Unless you REALLY
> need padding in your db, avoid char(x).

Unfortuantely, the people who created this database made all keys 30 
character strings, and we're not near a place in our release cycle where 
we can fix that.

In response to

Responses

pgsql-performance by date

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

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