An older question covers some reasons why the performance of multiple INSERTS within a single transaction is non-linear as the INSERT count grows.
Following some of the advice there, I've been trying to optimise running many UPDATEs in a single transaction. In the real scenario, we're batch processing data from another system, but I've got a smaller scenario for testing.
Given this table on postgresql 9.5.1:
\d+ foo
Table "public.foo"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+--------------------------------------------------+---------+--------------+-------------
id | bigint | not null default nextval('foo_id_seq'::regclass) | plain | |
count | integer | not null | plain | |
I have the following test files: 100.sql
, 1000.sql
, 10000.sql
, 50000.sql
and 100000.sql
. Each contains the following lines, with UPDATE
repeated according to the filename:
BEGIN;
UPDATE foo SET count=count+1 WHERE id=1;
...
UPDATE foo SET count=count+1 WHERE id=1;
COMMIT;
When I benchmark loading each file, the results look like this:
user system total real ms/update
100 0.000000 0.010000 0.040000 ( 0.044277) 0.44277
1000 0.000000 0.000000 0.040000 ( 0.097175) 0.09717
10000 0.020000 0.020000 0.230000 ( 1.717170) 0.17171
50000 0.160000 0.130000 1.840000 ( 30.991350) 0.61982
100000 0.440000 0.380000 5.320000 (149.199524) 1.49199
The average time per UPDATE increases as the transaction includes more rows, suggesting that the performance is non-linear.
The earlier question I linked to suggests that indexes can be an issue, however this table has no indexes and a single row.
Is this just a case of "that's how it works", or are there some settings I can tune to improve the situation?
Update
Based on a theory in the current answer, I've run an additional test. The table structure is the same, but the UPDATEs all change different rows. The input files now look like this:
BEGIN;
UPDATE foo SET count=count+1 WHERE id=1;
UPDATE foo SET count=count+1 WHERE id=2;
...
UPDATE foo SET count=count+1 WHERE id=n;
COMMIT;
When I benchmark loading these files, the results look like this:
user system total real ms/update
100 0.000000 0.000000 0.030000 ( 0.044876) 0.44876
1000 0.010000 0.000000 0.050000 ( 0.102998) 0.10299
10000 0.000000 0.040000 0.140000 ( 0.666050) 0.06660
50000 0.070000 0.140000 0.550000 ( 3.150734) 0.06301
100000 0.130000 0.280000 1.110000 ( 6.458655) 0.06458
From 10,000 UPDATEs and up (once setup costs are amortised) the performance is linear.