Re: very slow left join

On Fri, May 16, 2008 at 11:56 AM, Ben <bench@silentmedia.com> wrote:
> I've inherited an Oracle database that I'm porting to Postgres, and this has
> been going quite well until now. Unfortunately, I've found one view (a
> largish left join) that runs several orders of magnitude slower on Postgres
> than it did on Oracle.
>
> => select version();
>                                                 version
> ----------------------------------------------------------------------------------------------------------
>  PostgreSQL 8.2.4 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)
> 4.1.1 20070105 (Red Hat 4.1.1-52)
> (1 row)

1: Update to 8.2.7.  It's pretty painless, and who knows what
performance bugs you might be fighting that you don't really need to.

> After analyzing the database, the explain analyze output for the query is:
>
>  Nested Loop Left Join  (cost=133.51..15846.99 rows=1 width=312) (actual
> time=109.131..550711.374 rows=1248 loops=1)
>   Join Filter: (log.logkey = ln.logkey)
>   ->  Nested Loop  (cost=133.51..267.44 rows=1 width=306) (actual
> time=15.316..74.074 rows=1248 loops=1)
SNIP
>  Total runtime: 550712.393 ms

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.

>        (case when instr(lognotetext,';') = 0 then instr(lognotetext,' has
> modified')
>              else instr(lognotetext,';') end) = 0 then NULL

Try creating indexes on the functions above, and make sure you're
running the db in the C local if you can. Note you may need to dump /
initdb --locale=C / reload your data if you're not in the C locale
already.  text_pattern_ops may be applicable here, but I'm not sure
how to use it in the above functions.

>                         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).

Don't see anything else, but who knows what someone else might see.