From: Mark Lewis <mark.lewis@mir3.com>
To: Evan Carroll <lists@evancarroll.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Fwd: 8.2 Query 10 times slower than 8.1 (view-heavy)
Date: Tue, 28 Aug 2007 09:51:31 -0700
Message-ID: <1188319891.22730.63.camel@archimedes> (raw)
In-Reply-To: <428b865e0708280924ye13ead1j5c198778cdea9c6@mail.gmail.com>
References: <428b865e0708280822u522391ecr5366bb5a6647f1aa@mail.gmail.com>
<dcc563d10708280832u4b0b9abbh1b5053bd1b2a9149@mail.gmail.com>
<428b865e0708280901x3776581qc424bb2c52531475@mail.gmail.com>
<dcc563d10708280907r4b67217eta560c8eab710ab3c@mail.gmail.com>
<428b865e0708280923k57b8cbe4l3dd01fca75975c3f@mail.gmail.com>
<428b865e0708280924ye13ead1j5c198778cdea9c6@mail.gmail.com>
It looks like your view is using a left join to look for rows in one
table without matching rows in the other, i.e. a SQL construct similar
in form to the query below:
SELECT ...
FROM A LEFT JOIN B ON (...)
WHERE B.primary_key IS NULL
Unfortunately there has been a planner regression in 8.2 in some cases
with these forms of queries. This was discussed a few weeks (months?)
ago on this forum. I haven't looked closely enough to confirm that this
is the problem in your case, but it seems likely. Is it possible to
refactor the query to avoid using this construct to see if that helps?
We've been holding back from upgrading to 8.2 because this one is a
show-stopper for us.
-- Mark Lewis
On Tue, 2007-08-28 at 11:24 -0500, Evan Carroll wrote:
> ---------- Forwarded message ----------
> From: Evan Carroll <me@evancarroll.com>
> Date: Aug 28, 2007 11:23 AM
> Subject: Re: [PERFORM] 8.2 Query 10 times slower than 8.1 (view-heavy)
> To: Scott Marlowe <scott.marlowe@gmail.com>
>
>
> On 8/28/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> > I looked through your query plan, and this is what stood out in the 8.2 plan:
> >
> > -> Nested Loop Left Join (cost=8830.30..10871.27 rows=1
> > width=102) (actual time=2148.444..236018.971 rows=62 loops=1)
> > Join Filter: ((public.contact.pkid =
> > public.contact.pkid) AND (public.event.ts_in > public.event.ts_in))
> > Filter: (public.event.pkid IS NULL)
> >
> > Notice the misestimation is by a factor of 62, and the actual time
> > goes from 2149 to 236018 ms.
> >
> > Again, have you analyzed your tables / databases?
> >
> contacts=# \o scott_marlowe_test
> contacts=# VACUUM FULL ANALYZE;
> contacts=# SELECT * FROM test_view WHERE U_ID = 8;
> Cancel request sent
> ERROR: canceling statement due to user request
> contacts=# EXPLAIN ANALYZE SELECT * FROM test_view WHERE U_ID = 8;
>
> output found at http://rafb.net/p/EQouMI82.html
>
> --
> Evan Carroll
> System Lord of the Internets
> me@evancarroll.com
> 832-445-8877
>
>
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: mark.lewis@mir3.com, lists@evancarroll.com
Subject: Re: Fwd: 8.2 Query 10 times slower than 8.1 (view-heavy)
In-Reply-To: <1188319891.22730.63.camel@archimedes>
* 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