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@xxxxxxxxxxxxxxxxxxxxxxx>: > 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@xxxxxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > >
