From: Robert Haas <robertmhaas@gmail.com>
To: Anton Bogdanovitch <poison.box@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: perform 1 check vs exception when unique_violation
Date: Tue, 30 Dec 2008 09:50:55 -0500
Message-ID: <603c8f070812300650i226f032co8db1f25eb352e4b8@mail.gmail.com> (raw)
In-Reply-To: <gjctsu$vcl$1@ger.gmane.org>
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
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: robertmhaas@gmail.com, poison.box@gmail.com
Subject: Re: perform 1 check vs exception when unique_violation
In-Reply-To: <603c8f070812300650i226f032co8db1f25eb352e4b8@mail.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