pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedperform 1 check vs exception when unique_violation
2+ messages / 2 participants
[nested] [flat]
* perform 1 check vs exception when unique_violation
@ 2008-12-30 10:41 Anton Bogdanovitch <poison.box@gmail.com>
2008-12-30 14:50 ` Re: perform 1 check vs exception when unique_violation Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Anton Bogdanovitch @ 2008-12-30 10:41 UTC (permalink / raw)
To: pgsql-performance
I have to insert rows to table with 95% primary key unique_violation.
I've tested 2 examples below:
1)
BEGIN
INSERT INTO main (name, created) VALUES (i_name, CURRENT_TIMESTAMP
AT TIME ZONE 'GMT');
EXCEPTION WHEN UNIQUE_VIOLATION THEN
RETURN 'error: already exists';
END;
RETURN 'ok: store';
2)
PERFORM 1 FROM main WHERE name = i_name;
IF NOT FOUND THEN
INSERT INTO main (name, created) VALUES (i_name, CURRENT_TIMESTAMP
AT TIME ZONE 'GMT');
RETURN 'ok: stored';
ELSE
RETURN 'error: already exists';
END IF;
The first one performs about 20% slower, have 5 times more disk i/o
write operations.
The second one uses 20% more cpu.
Is it because of raid1 and slow writes?
What is the better solution to fit best performance?
Pg version 8.3, table size will probably grow to 100M rows
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: perform 1 check vs exception when unique_violation
2008-12-30 10:41 perform 1 check vs exception when unique_violation Anton Bogdanovitch <poison.box@gmail.com>
@ 2008-12-30 14:50 ` Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Robert Haas @ 2008-12-30 14:50 UTC (permalink / raw)
To: Anton Bogdanovitch <poison.box@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2008-12-30 14:50 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-12-30 10:41 perform 1 check vs exception when unique_violation Anton Bogdanovitch <poison.box@gmail.com>
2008-12-30 14:50 ` Robert Haas <robertmhaas@gmail.com>
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