From: Scott Marlowe <scott.marlowe@gmail.com>
To: Ben <bench@silentmedia.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: very slow left join
Date: Fri, 16 May 2008 12:09:46 -0600
Message-ID: <dcc563d10805161109v575b3498w2f396af5af3673a2@mail.gmail.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0805131416080.3347@localhost.localdomain>
References: <Pine.LNX.4.64.0805131416080.3347@localhost.localdomain>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scott.marlowe@gmail.com, bench@silentmedia.com
Subject: Re: very slow left join
In-Reply-To: <dcc563d10805161109v575b3498w2f396af5af3673a2@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox