Re: UPDATE runs slow in a transaction

Hi Tom,

Postgres is indeed selecting a bad plan. Turns out that the index I
created to speed up the UPDATE isn't used inside a transaction block.

Here's the plan for "UPDATE _struct SET left_token = (SELECT DISTINCT
left_token FROM tmp WHERE _struct.id = tmp.id)" outside of a
transaction:

QUERY PLAN
-------------------------------------------------------------------------------------------
Seq Scan on _struct (cost=0.00..826643.13 rows=98149 width=1083)
SubPlan
-> Unique (cost=8.38..8.40 rows=1 width=4)
-> Sort (cost=8.38..8.39 rows=4 width=4)
Sort Key: tmp.left_token
-> Index Scan using idx_tmp__id on tmp
(cost=0.00..8.34 rows=4 width=4)
Index Cond: ($0 = id)

And inside a transaction:

QUERY PLAN
---------------------------------------------------------------------------------------
Seq Scan on _struct (cost=100000000.00..3230175260746.00 rows=32300
width=70)
SubPlan
-> Unique (cost=100002329.99..100002330.01 rows=1 width=4)
-> Sort (cost=100002329.99..100002330.00 rows=4 width=4)
Sort Key: tmp.left_token
-> Seq Scan on tmp
(cost=100000000.00..100002329.95 rows=4 width=4)
Filter: ($0 = id)

The high cost of the seqscan on tmp are because I tried disabling
sequential scans inside the transaction to force an index scan, which
Postgres decided to ignore in this case.

Putting an ANALYZE tmp and ANALYZE _struct right before the UPDATE
didn't help either. (Also shouldn't the creation of an index on tmp
(id) take care of analyzing that column?)

Thanks,
Viktor

Am 14.07.2008 um 20:52 schrieb Tom Lane:

> Viktor Rosenfeld <rosenfel(at)informatik(dot)hu-berlin(dot)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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general