Received: from maia.hub.org (maia-1.hub.org [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 72792633897 for ; Tue, 11 May 2010 04:15:18 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 35117-01-10 for ; Tue, 11 May 2010 07:15:07 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.postnewspapers.com.au (202-89-185-120.static.dsl.amnet.net.au [202.89.185.120]) by mail.postgresql.org (Postfix) with ESMTP id 07C2C633881 for ; Tue, 11 May 2010 04:15:10 -0300 (ADT) Received: from [10.0.0.53] (wallace.localnet [10.0.0.53]) by mail.postnewspapers.com.au (Postfix) with ESMTP id 4FABEAFB7F; Tue, 11 May 2010 15:15:05 +0800 (WST) Message-ID: <4BE903C0.4020203@postnewspapers.com.au> Date: Tue, 11 May 2010 15:14:08 +0800 From: Craig Ringer User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.9) Gecko/20100423 Thunderbird/3.0.4 MIME-Version: 1.0 To: Carlo Stonebanks CC: pgsql-performance@postgresql.org Subject: Re: Function scan/Index scan to nested loop References: In-Reply-To: X-Enigmail-Version: 1.0.1 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.8 tagged_above=-10 required=5 tests=BAYES_50=0.8 X-Spam-Level: X-Archive-Number: 201005/46 X-Sequence-Number: 38630 On 11/05/10 13:32, Carlo Stonebanks wrote: > Hello all, > > A query ran twice in succession performs VERY poorly the first time as > it iterates through the nested loop. The second time, it rips. Please > see SQL, SLOW PLAN and FAST PLAN below. I haven't looked at the details, but the comment you made about it being fast on the live server which hits this query frequently tends to suggest that this is a caching issue. Most likely, the first time Pg has to read the data from disk. The second time, it's in memory-based disk cache or even in Pg's shared_buffers, so it can be accessed vastly quicker. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.com/