Re: UPDATE runs slow in a transaction
| От | Viktor Rosenfeld |
|---|---|
| Тема | Re: UPDATE runs slow in a transaction |
| Дата | |
| Msg-id | 06E2D05D-5EA3-4A7B-9483-8B0AA20F8F00@informatik.hu-berlin.de обсуждение исходный текст |
| Ответ на | Re: UPDATE runs slow in a transaction (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: UPDATE runs slow in a transaction
("Pavel Stehule" <pavel.stehule@gmail.com>)
Re: UPDATE runs slow in a transaction (Tom Lane <tgl@sss.pgh.pa.us>) Re: UPDATE runs slow in a transaction (Tom Lane <tgl@sss.pgh.pa.us>) |
| Список | pgsql-general |
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 callit from within a transaction block it's so slow that I have to abortit after a while. Specifically the second-to-last UPDATE seems totake forever within a transaction while it completes in about 3seconds 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
В списке pgsql-general по дате отправления: