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 <[EMAIL PROTECTED]> > Date: Aug 28, 2007 11:23 AM > Subject: Re: [PERFORM] 8.2 Query 10 times slower than 8.1 (view-heavy) > To: Scott Marlowe <[EMAIL PROTECTED]> > > > On 8/28/07, Scott Marlowe <[EMAIL PROTECTED]> 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 > [EMAIL PROTECTED] > 832-445-8877 > > ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
