title: Very slow update on a relatively small table in PostgreSQL
body: <p>Well i have the following table(info from pgAdmin):</p>
  
  <pre><code>    CREATE TABLE comments_lemms
  (
    comment_id integer,
    freq integer,
    lemm_id integer,
    bm25 real
  )
  WITH (
    OIDS=FALSE
  );
  ALTER TABLE comments_lemms OWNER TO postgres;
  
  -- Index: comments_lemms_comment_id_idx
  
  -- DROP INDEX comments_lemms_comment_id_idx;
  
  CREATE INDEX comments_lemms_comment_id_idx
    ON comments_lemms
    USING btree
    (comment_id);
  
  -- Index: comments_lemms_lemm_id_idx
  
  -- DROP INDEX comments_lemms_lemm_id_idx;
  
  CREATE INDEX comments_lemms_lemm_id_idx
    ON comments_lemms
    USING btree
    (lemm_id);
  </code></pre>
  
  <p>And one more table:</p>
  
  <pre><code>CREATE TABLE comments
  (
    id serial NOT NULL,
    nid integer,
    userid integer,
    timest timestamp without time zone,
    lemm_length integer,
    CONSTRAINT comments_pkey PRIMARY KEY (id)
  )
  WITH (
    OIDS=FALSE
  );
  ALTER TABLE comments OWNER TO postgres;
  
  -- Index: comments_id_idx
  
  -- DROP INDEX comments_id_idx;
  
  CREATE INDEX comments_id_idx
    ON comments
    USING btree
    (id);
  
  -- Index: comments_nid_idx
  
  -- DROP INDEX comments_nid_idx;
  
  CREATE INDEX comments_nid_idx
    ON comments
    USING btree
    (nid);
  </code></pre>
  
  <p>in comments_lemms there are 8 million entries, in comments - 270 thousands.
  Im performing the following sql query:</p>
  
  <pre><code>update comments_lemms set bm25=(select lemm_length from comments where id=comment_id limit 1)
  </code></pre>
  
  <p>And it takes more than 20 minutes of running and i stop it because pgAdmin looks like its about to crash.
  Is there any way to modify this query or indexes or whatever in my database to speed up things a bit? I have to run some similar queries in future and it's quite painful to wait more than 30 minutes for each one.</p>