Skip site navigation (1) Skip section navigation (2)

Peripheral Links

Header And Logo

PostgreSQL
| The world's most advanced open source database.

Site Navigation

Search archives
  Advanced Search

perform 1 check vs exception when unique_violation


  • From: Anton Bogdanovitch <poison(dot)box(at)gmail(dot)com>
  • To: pgsql-performance(at)postgresql(dot)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> <text/plain>

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





Home | Main Index | Thread Index

Privacy Policy | About PostgreSQL
Copyright © 1996 – 2012 PostgreSQL Global Development Group