Re: UPDATE runs slow in a transaction

hello

second query:

why without transactions are not called triggers constraint
_FK_struct_2_collection and constraint _FK_struct_2_text?

Regards
Pavel Stehule

2008/7/16 Viktor Rosenfeld <rosenfel@informatik.hu-berlin.de>:
> Hi Pavel,
>
> thanks for the advice on how to uncorrelate the query.  I must admit I
> didn't know about the UPDATE ... SET ... FROM ... syntax.
>
> Now the UPDATE runs in an acceptable time inside a transaction, however the
> query plan still differs when I run it outside.
>
> Outside a transaction:
>
>                                                                 QUERY PLAN
>
--------------------------------------------------------------------------------------------------------------------------------------------
>  Merge Join  (cost=0.00..11481.84 rows=65756 width=1087) (actual
> time=0.151..323.856 rows=65756 loops=1)
>   Merge Cond: (_struct.id = tmp.id)
>   ->  Index Scan using "_PK_struct" on _struct  (cost=0.00..7084.50
> rows=98149 width=1083) (actual time=0.028..137.463 rows=32300 loops=1)
>   ->  Index Scan using idx_tmp__id on tmp  (cost=0.00..3330.02 rows=65756
> width=12) (actual time=0.115..58.601 rows=65756 loops=1)
>  Total runtime: 2905.580 ms
>
> This looks like an optimal plan and average run time over 5 runs is 2660 ms.
>
> Inside a transaction:
>
>                                                                 QUERY PLAN
>
--------------------------------------------------------------------------------------------------------------------------------------------
>  Merge Join  (cost=7427.63..16159.84 rows=65756 width=1087) (actual
> time=315.570..574.075 rows=65756 loops=1)
>   Merge Cond: (_struct.id = tmp.id)
>   ->  Index Scan using "_PK_struct" on _struct  (cost=0.00..7500.50
> rows=98149 width=1083) (actual time=0.020..129.915 rows=32300 loops=1)
>   ->  Sort  (cost=7427.63..7592.02 rows=65756 width=12) (actual
> time=315.538..333.359 rows=65756 loops=1)
>         Sort Key: tmp.id
>         Sort Method:  quicksort  Memory: 4617kB
>         ->  Seq Scan on tmp  (cost=0.00..2165.56 rows=65756 width=12)
> (actual time=10.070..37.411 rows=65756 loops=1)
>  Trigger for constraint _FK_struct_2_collection: time=1105.892 calls=32300
>  Trigger for constraint _FK_struct_2_text: time=1468.009 calls=32300
>  Total runtime: 4955.784 ms
>
> Again, the planner does not use the index on tmp (id) although I put an
> "ANALYZE tmp" right before the UPDATE.  Average run time over 5 runs is 4610
> ms.
>
> Thanks,
> Viktor
>
> Am 16.07.2008 um 15:33 schrieb Pavel Stehule:
>
>> Hello
>>
>> my advice is little bit offtopic, I am sorry. Why you use correlated
>> subquery? Your update statement should be
>>
>> update _struct set left_token = tmp.left_token from tmp where
>> _struct.id = tmp.id;
>>
>> send output of explain analyze statement, please. etc
>> explain analyze UPDATE _struct SET left_token = (SELECT DISTINCT
>> left_token FROM tmp WHERE _struct.id = tmp.id)
>>
>> regards
>> Pavel Stehule
>>
>>
>>
>> 2008/7/16 Viktor Rosenfeld <rosenfel@informatik.hu-berlin.de>:
>>>
>>> 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@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
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>>
>
>