perform 1 check vs exception when unique_violation - Mailing list pgsql-performance

From Anton Bogdanovitch
Subject perform 1 check vs exception when unique_violation
Date
Msg-id gjctsu$vcl$1@ger.gmane.org
Whole thread Raw
Responses Re: perform 1 check vs exception when unique_violation  ("Robert Haas" <robertmhaas@gmail.com>)
List 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


pgsql-performance by date:

Previous
From: Guillaume Lelarge
Date:
Subject: Re: Big index sizes
Next
From: "Robert Haas"
Date:
Subject: Re: Big index sizes