Just to close this up and give some guidance to future googlers...
There are no foreign keys. The table is empty when I start. I copy the
data into it from a text file.
Removing indexes didn't help much (made a very slight difference).
running a query CREATE TEMP TABLE tt AS SELECT .... using a massive
join takes about 8 seconds. I presume that's the baseline for the disk
and RAM given my current postgres configuration. Note that this is
not a satisfactory option for me because I can't do what I want in one
step (the update I specified is one of many).
running a very simple update "UPDATE imports set make_id = null"
takes over 50 seconds so that's the minimum amount of time any update
is going to take.
Running a complex update where I join all the tables together and
update all the fields takes about 106 seconds.
Just running a complex select with the joins takes no time at all.
I tried chunking the updates using chunks of 100 records and 1000
records (where ID between X and Y repeatedly) and it was even slower.
Conclusion. Updates on postgres are slow (given the default
postgresql.conf). I presume this is due to MVCC or the WAL or
something and there are probably some things I can do to tweak the
conf file to make them go faster but out of the box running an update
on a table with lots of rows is going to cost you a lot. Removing the
indexes doesn't help that much.
Suggestion for the PG team. Deliver a more realistic postgres.conf by
default. The default one seems to be aimed at ten year old PCs with
very little RAM and disk space. At least deliver additional conf files
for small, medium, large, huge setups.
