DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=xbLOmqQJ+dI7x7CeB/eDMtG40mS7XaJxK3PzilTCSUstsEGIdnvDorZStMscplZBCS
	GoVj9lUwRdlLQIlHDXYE17a69TFfccHpAhp1AO+u9PkfWrnm5nJD1BtLjydIrHv9dE0y
	4FFFQMCBD46u7rwDjqE2Zf1/cWAywZ34Ble+M=
Message-ID: <603c8f070812300650i226f032co8db1f25eb352e4b8@mail.gmail.com>
Date: Tue, 30 Dec 2008 09:50:55 -0500
From: "Robert Haas" <robertmhaas@gmail.com>
To: "Anton Bogdanovitch" <poison.box@gmail.com>
Subject: Re: perform 1 check vs exception when unique_violation
Cc: pgsql-performance@postgresql.org
In-Reply-To: <gjctsu$vcl$1@ger.gmane.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <gjctsu$vcl$1@ger.gmane.org>

On Tue, Dec 30, 2008 at 5:41 AM, Anton Bogdanovitch
<poison.box@gmail.com> wrote:
> I have to insert rows to table with 95% primary key unique_violation.

If you're inserting a lot of rows at once, I think you're probably
better off loading all of the data into a side table that does not
have a primary key, and then writing one statement to remove the
duplicates and do all the inserts at once.

INSERT INTO main (name, created)
SELECT
   s.name, CURRENT_TIMESTAMP
FROM
   (SELECT DISTINCT ON (name) FROM sidetable) s  -- nuke duplicate
names within sidetable
   LEFT JOIN main m ON s.name = m.name
   WHERE m.name IS NULL;   -- nuke names in sidetable that are already in main

I've usually found that any kind of loop in PL/pgsql is mind-numbingly
slow compared to doing the same thing as a single query.

...Robert