Viktor Rosenfeld <rosenfel@xxxxxxxxxxxxxxxxxxxxxxx> writes: > 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. That doesn't make any sense to me, and in fact I cannot replicate any such behavior here. What PG version are you running, exactly? The exact test case I tried is attached --- it's just your original incomplete example with some dummy data created beforehand. I get a plan using the tmp_id index in all supported PG versions. regards, tom lane drop table _struct, _rank; create table _struct(token_index int, id int); create table _rank(struct_ref int, pre int, post int); insert into _struct select i, i from generate_series(1,1000) g(i); insert into _rank select i, i, i from generate_series(1,1000) g(i); analyze _struct; analyze _rank; begin; -- add columns left_token, right_token and copy values from token_index ALTER TABLE _struct ADD left_token integer; ALTER TABLE _struct ADD right_token integer; UPDATE _struct SET left_token = token_index; UPDATE _struct SET right_token = token_index; -- set left, right values for non-terminals -- (use temporary table to get rid of joins between struct and rank) CREATE TABLE tmp AS SELECT r.pre, r.post, s.id, s.left_token, s.right_token FROM _rank r, _struct s WHERE r.struct_ref = s.id; CREATE INDEX idx_tmp_pre_post ON tmp (pre, post); UPDATE tmp SET left_token = (SELECT min(t2.left_token) FROM tmp t2 WHERE t2.pre >= tmp.pre AND t2.pre <= tmp.post); UPDATE tmp SET right_token = (SELECT max(t2.right_token) FROM tmp t2 WHERE t2.pre >= tmp.pre AND t2.pre <= tmp.post); -- copy left, right values for everything CREATE INDEX tmp_id ON tmp (id); --analyze tmp; explain UPDATE _struct SET left_token = (SELECT DISTINCT left_token FROM tmp WHERE _struct.id = tmp.id); -- the UPDATE above takes ages when called within a transaction UPDATE _struct SET right_token = (SELECT DISTINCT right_token FROM tmp WHERE _struct.id = tmp.id); -- clean up DROP TABLE tmp; rollback;