From: Marc Cousin <cousinmarc@gmail.com>
To: Kevin Grittner <kgrittn@ymail.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: query plan not optimal
Date: Fri, 20 Dec 2013 07:05:35 +0100
Message-ID: <52B3DE2F.10203@gmail.com> (raw)
In-Reply-To: <1387485379.74465.YahooMailNeo@web162902.mail.bf1.yahoo.com>
References: <52B311C4.1070108@gmail.com>
<1387485379.74465.YahooMailNeo@web162902.mail.bf1.yahoo.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cousinmarc@gmail.com, kgrittn@ymail.com
Subject: Re: query plan not optimal
In-Reply-To: <52B3DE2F.10203@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox