Re: UPDATE runs slow in a transaction

Viktor Rosenfeld <rosenfel(at)informatik(dot)hu-berlin(dot)de> 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;