Re: very slow left join

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.