Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 6D4319FB380 for ; Tue, 12 Jun 2007 15:31:16 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 83087-10 for ; Tue, 12 Jun 2007 15:31:01 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.226]) by postgresql.org (Postfix) with ESMTP id CD4CF9FB329 for ; Tue, 12 Jun 2007 15:31:02 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id s16so1768412wxc for ; Tue, 12 Jun 2007 11:31:02 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=FuoalCuskQq7fJ09yTB0LaPt/wYN1uPVgYsIfyOKmZJxsIZuI24hR4MbFZVmPtZdsbE4i6oN6T1lrZpvmU8KhGyJXMMAF+nHJiWYcYramT+JbAphBVPiLaQ4fmx5ZBBN3X2tFzTFAHLld4/oZXdvoBNthgg72oCr3QzbYx32TMI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=o+VabaSn397KGZ2/rgVLtReX8/bDChFLljPFef9l+26eES+jfvs7eCfcAgzBJoKxPfIqFaw+g9qbSMbgb3gG+Mgnn5tH3cgTagHt0GpX/xMvF13FkSPKkBASM2+EDMf69o3G6GU99hSEkzL9li4TeXjg2A3GPxPV6EA8rY0qZUU= Received: by 10.70.42.20 with SMTP id p20mr10733469wxp.1181673061672; Tue, 12 Jun 2007 11:31:01 -0700 (PDT) Received: by 10.70.109.14 with HTTP; Tue, 12 Jun 2007 11:31:01 -0700 (PDT) Message-ID: <357fa7590706121131t5adf3795ld006955d6f77e175@mail.gmail.com> Date: Tue, 12 Jun 2007 14:31:01 -0400 From: "Steven Flatt" To: "Tom Lane" Subject: Re: performance drop on 8.2.4, reverting to 8.1.4 Cc: "Alvaro Herrera" , "Steinar H. Gunderson" , pgsql-performance@postgresql.org In-Reply-To: <11710.1181266274@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_8418_20466361.1181673061291" References: <20070518104646.GA13811@uio.no> <008201c7993c$76c88a10$653e10ac@ilgvaio> <20070518111455.GA13978@uio.no> <28604.1179503689@sss.pgh.pa.us> <357fa7590706051430h5db6760dx9d4a46c7a8a36205@mail.gmail.com> <10645.1181081285@sss.pgh.pa.us> <357fa7590706071011g4e337937rc622ffed39856425@mail.gmail.com> <11349.1181265079@sss.pgh.pa.us> <20070608012257.GB20828@alvh.no-ip.org> <11710.1181266274@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/224 X-Sequence-Number: 25257 ------=_Part_8418_20466361.1181673061291 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Thanks Tom and Alvaro. To follow up on this, I re-wrote and tweaked a number of queries (including the one provided) to change "LEFT OUTER JOIN ... WHERE col IS NULL" clauses to "WHERE col NOT IN (...)" clauses. This has brought performance to an acceptable level on 8.2. Thanks for your time, Steve On 6/7/07, Tom Lane wrote: > > Alvaro Herrera writes: > > Tom Lane escribi=F3: > >> I was hoping that the auto plan invalidation code in CVS HEAD would ge= t > >> it out of this problem, but it seems not to for the problem-as-given. > >> The trouble is that it won't change plans until autovacuum analyzes th= e > >> tables, and that won't happen until the transaction commits and sends > >> off its I-inserted-lotsa-rows report to the stats collector. > > > I think there is something we can do about this -- drop the default > > value for analyze threshold. > > Maybe worth doing, but it doesn't help for Steve's example. > > regards, tom lane > ------=_Part_8418_20466361.1181673061291 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Thanks Tom and Alvaro.
 
To follow up on this, I re-wrote and tweaked a number of queries (incl= uding the one provided) to change "LEFT OUTER JOIN ... WHERE col IS NU= LL" clauses to "WHERE col NOT IN (...)" clauses.
 
This has brought performance to an acceptable level on 8.2.
 
Thanks for your time,
Steve

 
On 6/7/07, T= om Lane <tgl@sss.pgh.pa.us&= gt; wrote:
Alvaro Herrera <alvherre@commandprompt.com> writes:=
> Tom Lane escribi=F3:
>> I was hoping that the auto plan inval= idation code in CVS HEAD would get
>> it out of this problem, but = it seems not to for the problem-as-given.
>> The trouble is that i= t won't change plans until autovacuum analyzes the
>> tables, and that won't happen until the transaction commit= s and sends
>> off its I-inserted-lotsa-rows report to the stats c= ollector.

> I think there is something we can do about this -- dr= op the default
> value for analyze threshold.

Maybe worth doing, but it does= n't help for Steve's example.

     =             &nb= sp;     regards, tom lane

------=_Part_8418_20466361.1181673061291--