perform 1 check vs exception when unique_violation

Поиск
Список
Период
Сортировка
От Anton Bogdanovitch
Тема perform 1 check vs exception when unique_violation
Дата
Msg-id gjctsu$vcl$1@ger.gmane.org
обсуждение исходный текст
Ответы Re: perform 1 check vs exception when unique_violation  ("Robert Haas" <robertmhaas@gmail.com>)
Список 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 по дате отправления:

Предыдущее
От: Guillaume Lelarge
Дата:
Сообщение: Re: Big index sizes
Следующее
От: "Robert Haas"
Дата:
Сообщение: Re: Big index sizes