Hi,
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.
The Postgres manual states that statements should ran faster within a
transaction.
I wonder if I have to up a configuration parameter to give Postgres
more resources or if the problem lies elsewhere.
Thanks for your help,
Viktor
The script:
-- 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);
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;
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
