Subject: Re: Fwd: 8.2 Query 10 times slower than 8.1 (view-heavy)
From: Mark Lewis <mark.lewis@mir3.com>
To: Evan Carroll <lists@evancarroll.com>
Cc: pgsql-performance@postgresql.org
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>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Organization: MIR3, Inc.
Date: Tue, 28 Aug 2007 09:51:31 -0700
Message-Id: <1188319891.22730.63.camel@archimedes>
Mime-Version: 1.0

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