"Kynn Jones" <kynnjo@gmail.com> writes:
> I can't begin to guess why the update statement above caused the processing
> to stall, but ran quickly when I executed it by itself. How can I
> understand this situation better?
Perhaps it was blocked on a lock rather than actually doing anything.
Did you look into pg_locks, or use top or vmstat or similar tool to see
if any work was getting done?
Another likely theory is that the script case was using a bad plan
for lack of up-to-date statistics. Putting an EXPLAIN into the script
and comparing it with the manual case would tell the tale.
What's causing the difference is not clear, though if you have
autovacuum enabled that might explain a problem of the second kind
(autovac might've updated the statistics before you tried the command
manually).
regards, tom lane