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 <[EMAIL PROTECTED]> 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
