From: Anton Bogdanovitch <poison.box@gmail.com>
To: pgsql-performance@postgresql.org
Subject: perform 1 check vs exception when unique_violation
Date: Tue, 30 Dec 2008 13:41:42 +0300
Message-ID: <gjctsu$vcl$1@ger.gmane.org> (raw)
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
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: poison.box@gmail.com
Subject: Re: perform 1 check vs exception when unique_violation
In-Reply-To: <gjctsu$vcl$1@ger.gmane.org>
* 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