Как ускорить update в postgresql?

Question

[c-darwin]

CREATE SEQUENCE ds_citizens_citizen_id_seq START WITH 1;
CREATE TABLE "ds_citizens" (
"citizen_id" bigint NOT NULL default nextval('ds_citizens_citizen_id_seq'),
"amount" decimal(30) NOT NULL DEFAULT '0'
);
ALTER SEQUENCE ds_citizens_citizen_id_seq owned by ds_citizens.citizen_id;
ALTER TABLE ONLY "ds_citizens" ADD CONSTRAINT ds_citizens_pkey PRIMARY KEY (citizen_id);

Вставляю 100к строк ~ 50 секунд.
В цикле делаю
UPDATE ds_citizens SET amount=$1 WHERE citizen_id=$2
56 секунд
Проц занимает на 12-15% все 4 ядра по чуть-чуть, диск почти не трогает, памяти много, своп не юзает.
ОС: Ubuntu 64bit, postgresql 9.4
fsync = off
synchronous_commit = off
full_page_writes = off
Подскажите, пожалуйста, как можно ускорить update?

udp
Если через тр-ии, то 20 сек.
Тоже самое в sqlite3 - 1 сек :)

Answer 1

[Андрей Буров]

Укажите fillfactor для таблицы около 70 (или меньше).
Делайте update пачками в транзакциях.

И сделайте индекс по citizen_id (я так понимаю это должен быть primary key)

Comments

[c-darwin]

Выполнил ALTER TABLE ds_citizens SET ( fillfactor = 70);VACUUM FULL ds_citizens;

А индекс был:

\d ds_citizens;
Table "public.ds_citizens"
Column | Type | Modifiers
------------+---------------+------------------------------------------------------------------
citizen_id | bigint | not null default nextval('ds_citizens_citizen_id_seq'::regclass)
amount | numeric(30,0) | not null default 0::numeric
Indexes:
"ds_citizens_pkey" PRIMARY KEY, btree (citizen_id)

Пачками попробую, спасибо.

[Андрей Буров]

и используйте prepare запросы.
У меня вся таблица влезла в shared memory и запрос

EXPLAIN (ANALYZE,VERBOSE,BUFFERS,TIMING) update ds_citizens set amount = 1 where citizen_id IN (SELECT generate_series(1, 1000000) ORDER by random());

выполняется за 4000ms+- (из них ~700ms уходит на подзапрос с order by random() )
https://explain.depesz.com/s/uYQ при этом fsync и т.п. включены (диск обычный HDD 7200rpm)

[c-darwin]

Сделал пачкой в тр-ии, получилось 20 сек.

[c-darwin]

Андрей Буров: У меня этот же запрос Execution time: 21817.559 ms
Но юзалось на 100% только одно из 4-х ядер