Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M51020=pgsql-performance-ar@postgresql.org>)
 id 1VttDS-0004qs-OW
 for pgsql-performance@arkaria.postgresql.org; Fri, 20 Dec 2013 06:05:42 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M51020=pgsql-performance-ar@postgresql.org>)
 id 1VttDS-0004Mm-77
 for pgsql-performance@arkaria.postgresql.org; Fri, 20 Dec 2013 06:05:42 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <cousinmarc@gmail.com>) id 1VttDQ-0004Ky-Cp
 for pgsql-performance@postgresql.org; Fri, 20 Dec 2013 06:05:40 +0000
Received: from mail-we0-x22e.google.com ([2a00:1450:400c:c03::22e])
 by magus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <cousinmarc@gmail.com>) id 1VttDO-0001jl-LH
 for pgsql-performance@postgresql.org; Fri, 20 Dec 2013 06:05:40 +0000
Received: by mail-we0-f174.google.com with SMTP id q58so2052970wes.33
 for <pgsql-performance@postgresql.org>; Thu, 19 Dec 2013 22:05:38 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=message-id:date:from:user-agent:mime-version:to:subject:references
 :in-reply-to:content-type:content-transfer-encoding;
 bh=rVhadAyMX3lyiI6XTTfRviACq3Wzbk6t3VdCi0mRHrY=;
 b=qC4pwkoUrdBCg1W0qh5J3fPy5AwPK1CGwMC2wYwEGfie0vgY+x42jNRW5YuqeUzxVN
 TlYfZH8guTw/X2nsdOpustbdkHXap+RwHC8PUxG7rtbVGaLCPp4QrhGXABfs3w2MZ6nT
 dnDS3mWRuvIKQ2dILVKfEgInyL1Fp7OQjJ0hepdbmwzbdSPGfiOW/CfdCC8b0fkMmmb5
 pAZRSVWWSYFBJVLggTNnZwGxGhb/vh1bmqVNiuw1Ynz3BROJLd+CdQ38L8K8njaFlQJY
 cFyOb3/wC3Saf1DKER4g00QHYK6BIrMl8xLBbBVkT142ug9HM/rb01qBEXRI1VyIuQMQ
 6/kw==
X-Received: by 10.194.176.163 with SMTP id cj3mr5192798wjc.8.1387519538259;
 Thu, 19 Dec 2013 22:05:38 -0800 (PST)
Received: from [192.168.0.2] (gev44-1-78-228-108-65.fbx.proxad.net.
 [78.228.108.65])
 by mx.google.com with ESMTPSA id xl18sm15660138wib.9.2013.12.19.22.05.36
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Thu, 19 Dec 2013 22:05:37 -0800 (PST)
Message-ID: <52B3DE2F.10203@gmail.com>
Date: Fri, 20 Dec 2013 07:05:35 +0100
From: Marc Cousin <cousinmarc@gmail.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.2.0
MIME-Version: 1.0
To: Kevin Grittner <kgrittn@ymail.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: query plan not optimal
References: <52B311C4.1070108@gmail.com>
 <1387485379.74465.YahooMailNeo@web162902.mail.bf1.yahoo.com>
In-Reply-To: <1387485379.74465.YahooMailNeo@web162902.mail.bf1.yahoo.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit
X-Pg-Spam-Score: -2.0 (--)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 19/12/2013 21:36, Kevin Grittner wrote:
> Marc Cousin <cousinmarc@gmail.com> wrote:
>
>> Then we insert missing paths. This is one of the plans that fail
>> insert into path (path)
>>     select path from batch
>>       where not exists
>>             (select 1 from path where path.path=batch.path)
>>       group by path;
> I know you said you wanted to focus on a different query, but this
> one can easily be optimized.  Right now it is checking for an
> existing row in path for each row in batch; and it only needs to
> check once for each path.  One way to write it would be:
>
> insert into path (path)
>    select path from (select distinct path from batch) b
>      where not exists
>            (select 1 from path p where p.path = b.path);
Yeah, I know, that's why I said I didn't want to focus on this one… we 
already do this optimization :)

Thanks anyway :)


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance