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.