Okay, thanks. I'm not actually too worried about this since in my scenario, each row is about 1.5 kB, so the % overhead is negligible.
> It is probably not the lookup, but the *modification* of the index that is slow.
Yes that makes sense for the original 3 million inserts, but when I perform the update of the 3 million rows, the index doesn't change - they are all HOT updates.
Using "perf" I can see that the overhead is indeed due to index lookup when we do HOT updates.
> Then the best you can do is to use COPY rather than INSERT. It will perform better (but [not] vastly better).
I need to perform a merge (INSERT ... ON CONFLICT ... DO UPDATE) on the data, so sadly I cannot use COPY.
I have discovered that for some reason, performing the original insert without the ON CONFLICT statement is twice as fast as performing the original insert with an ON CONFLICT ... DO UPDATE clause, completing in 4 seconds instead of 8. That seems strange to me because I wouldn't have thought it would be doing any additional work since a unique constraint is on the primary key, so each inserted value would need to be checked in either case, and there is no extra work to be done in either case.
In the INSERT case, we do not check the unique constraint for each row. We run into an error when inserting a duplicate, aborting the operation.
INSERT INTO test (id, text1) SELECT generate_series, 'x' FROM generate_series(1, 3000000)
It remains 4 seconds even when adding a clause to not insert duplicates.
INSERT INTO test (id, text1) SELECT generate_series, 'x' FROM generate_series(1, 3000000) WHERE NOT EXISTS ( SELECT 1 FROM test4 WHERE id = generate_series )
In this case, we are not checking duplicates inside the input dataset. If you can guarantee, at the application level, that there are no duplicates, this seems a good speedup. Perhaps the MERGE clause...
Furthermore, I have found that performing an UPDATE rather than an INSERT ... ON CONFLICT ... DO UPDATE is twice as slow, completing in 16 seconds instead of 14 seconds.
UPDATE test SET text1 = 'x' FROM generate_series(1, 3000000) WHERE test4.id = generate_series
This also now means that updating 3 million rows takes 4x longer than inserting those rows. Do we expect updates to be 4x slower than inserts?
It is not the update that is slower. It is the attached where clause that makes it slower. Try: UPDATE test SET text1='x';
In my tests, the update of non-indexed columns is slightly faster than an insert.
