Harry Mantheakis <harry(dot)mantheakis(at)riskcontrollimited(dot)com> wrote:
>> It will be a lot faster if you can drop all indices...
>
> This is counter-intuitive - because the WHERE clause is matching
> the only two indexed fields, and my understanding is that querying
> on indexed fields is faster than querying on fields that are not
> indexed.
Because your UPDATE requires reading every tuple in every page of
both tables, it would be counter-productive to use the indexes.
Random access is much slower than sequential, so it's fastest to
just blast through both tables sequentially. The plan you showed
has it scanning through table_B and loading the needed data into RAM
in a hash table, then scanning through table_A and updating each row
based on what is in the RAM hash table.
For each row updated, if it isn't a HOT update, a new entry must be
inserted into every index on table_A, so dropping the indexes before
the update and re-creating them afterward would probably be a very
good idea if you're going to do the whole table in one go, and
possibly even if you're working in smaller chunks.
One thing which might help run time a lot, especially since you
mentioned having a lot of unused RAM, is to run the update with a
very hight work_mem setting in the session running the UPDATE.
> (Note also, that the indexed field is NOT being updated.)
That's one of the conditions for a HOT update. The other is that
there is space available on the same page for a new version of the
row, in addition to the old version.
-Kevin