Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 844589F9EC6 for ; Tue, 28 Aug 2007 13:25:04 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 56760-01 for ; Tue, 28 Aug 2007 13:25:00 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from fk-out-0910.google.com (fk-out-0910.google.com [209.85.128.189]) by postgresql.org (Postfix) with ESMTP id 64BA39F9567 for ; Tue, 28 Aug 2007 13:24:59 -0300 (ADT) Received: by fk-out-0910.google.com with SMTP id b27so1907045fka for ; Tue, 28 Aug 2007 09:24:58 -0700 (PDT) Received: by 10.82.160.19 with SMTP id i19mr1030504bue.1188318297821; Tue, 28 Aug 2007 09:24:57 -0700 (PDT) Received: by 10.82.158.11 with HTTP; Tue, 28 Aug 2007 09:24:57 -0700 (PDT) Message-ID: <428b865e0708280924ye13ead1j5c198778cdea9c6@mail.gmail.com> Date: Tue, 28 Aug 2007 11:24:57 -0500 From: "Evan Carroll" To: pgsql-performance@postgresql.org Subject: Fwd: 8.2 Query 10 times slower than 8.1 (view-heavy) In-Reply-To: <428b865e0708280923k57b8cbe4l3dd01fca75975c3f@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <428b865e0708280822u522391ecr5366bb5a6647f1aa@mail.gmail.com> <428b865e0708280901x3776581qc424bb2c52531475@mail.gmail.com> <428b865e0708280923k57b8cbe4l3dd01fca75975c3f@mail.gmail.com> X-Google-Sender-Auth: 4b7799c09f829f16 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.818 tagged_above=0 required=5 tests=AWL=0.432, BAYES_05=-1.11, NO_DNS_FOR_FROM=1.496 X-Spam-Level: X-Archive-Number: 200708/357 X-Sequence-Number: 26487 ---------- Forwarded message ---------- From: Evan Carroll Date: Aug 28, 2007 11:23 AM Subject: Re: [PERFORM] 8.2 Query 10 times slower than 8.1 (view-heavy) To: Scott Marlowe On 8/28/07, Scott Marlowe 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 -- Evan Carroll System Lord of the Internets me@evancarroll.com 832-445-8877