From: | Viktor Rosenfeld <rosenfel(at)informatik(dot)hu-berlin(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | UPDATE runs slow in a transaction |
Date: | 2008-07-14 16:27:57 |
Message-ID: | A60E77F1-901D-4B0E-A2EE-D9A07F5DD587@informatik.hu-berlin.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Fco. Mario Barcala Rodríguez | 2008-07-14 16:34:47 | NEAR operator |
Previous Message | Decibel! | 2008-07-14 16:25:21 | Re: Feature: FOR UPDATE SKIP LOCKED |