Thread: slow update

slow update

From
Lionel Barth
Date:
Hello,

I use a Postgres 6.3.2 whith the btree_adj-980730 patch on a Sun
UltraSparc 1 under Solaris 2.5.1

I create a table

CREATE TABLE dlf (lft int4 NOT NULL, id varchar(20), rgt int4 NOT NULL,
niv int4, tag varchar(32), type varchar(32));

I feed the table with about 5600 tuples

COPY dlf FROM '....../dlf.import';

I create an index

CREATE INDEX dlf_lft_index on dlf using btree (lft int4_ops );

I make updates

       explain  update dlf set lft = lft+2 where lft>7;
       Index Scan on dlf  (cost=119.50 size=1870 width=54)
Why is the update so slow?

       explain  update dlf set lft = lft+2;
       Seq Scan on dlf  (cost=244.13 size=5610 width=54)
Why do I have a Seq Scan instead of an Index Scan?

       vacuum dlf;

       explain  update dlf set lft = lft+2 where lft>7;
       Index Scan on dlf  (cost=151.53 size=1871 width=54)

       explain  update dlf set lft = lft+2;
       Seq Scan on dlf  (cost=244.20 size=5612 width=54)
Why are the updates slower after the vacuum?

Thank you
Lionel Barth