Hi,
we recently found a bug in one of our applications which was doing exactly the same UPDATE operation a few thousand times inside a transaction. This caused the UPDATEs to become slower and slower from some milliseconds to some seconds. We already fixed the application but I am wondering if this might be a PostgreSQL bug, too.
Here is a simple test case that performs and benchmarks 100,000 UPDATEs (benchmarking only every 10,000th to reduce output):
BEGIN;
CREATE TEMP TABLE test (id integer PRIMARY KEY, flag boolean DEFAULT false);
INSERT INTO test (id) SELECT generate_series(1, 100000);
DO $$
DECLARE
s timestamp;
e timestamp;
BEGIN
FOR i IN 1..100000 LOOP
SELECT clock_timestamp() INTO s;
UPDATE test SET flag = true WHERE id = 12345;
SELECT clock_timestamp() INTO e;
IF i%10000 = 0 THEN
RAISE NOTICE '%', e-s;
END IF;
END LOOP;
END $$;
ROLLBACK;
The output looks like this:
NOTICE: 00:00:00.000525
NOTICE: 00:00:00.000992
NOTICE: 00:00:00.001404
NOTICE: 00:00:00.001936
NOTICE: 00:00:00.002374
NOTICE: 00:00:00.002925
NOTICE: 00:00:00.003525
NOTICE: 00:00:00.004015
NOTICE: 00:00:00.00453
NOTICE: 00:00:00.004976
The problem only occurs inside a transaction and if the same dataset is updated. I´m using PostgreSQL 9.1.15.
Jan