Viktor Rosenfeld <rosenfel@informatik.hu-berlin.de> writes:
> the script below runs very fast when executed alone. But when I call
> it from within a transaction block it's so slow that I have to abort
> it after a while. Specifically the second-to-last UPDATE seems to
> take forever within a transaction while it completes in about 3
> seconds outside a transaction.
Since the table you're working on was just created in the same
transaction, there's been no opportunity for autovacuum to run an
ANALYZE on it; that's probably preventing selection of a good plan.
Try throwing in an "ANALYZE tmp" after you load the table.
regards, tom lane
