title: Very slow Postgres UPDATE on large table
body: <p>I have a Postgres 9.1.3 table with 2.06 million rows after <strong>WHERE Y=1</strong> as per below (it only has a few ten thousand more rows total without any <strong>WHERE</strong>). I am trying to add data to an empty field with a query like this:</p>
  
  <pre><code>WITH B AS (
      SELECT Z,
             rank() OVER (ORDER BY L, N, M, P) AS X
      FROM   A
      WHERE  Y=1
  )
  
  UPDATE A
  SET A.X = B.X
  FROM B
  WHERE A.Y=1
    AND B.Z = A.Z;
  </code></pre>
  
  <p>This query runs for hours and appears to progress very slowly. In fact, the second time I tried this, I had a power outage after the query ran for ~3 hours. After restoring power, I analyzed the table and got this:</p>
  
  <pre><code>INFO:  analyzing "consistent.master"
  INFO:  "master": scanned 30000 of 69354 pages, containing 903542 live rows and 153552 dead rows; 30000 rows in sample, 2294502 estimated total rows
  Total query runtime: 60089 ms.
  </code></pre>
  
  <p>Is it correct to interpret that the query had barely progressed in those hours?</p>
  
  <p>I have done a <strong>VACUUM FULL</strong> and <strong>ANALYZE</strong> before running the long query.</p>
  
  <p>The query within the <strong>WITH</strong> only takes 40 seconds.</p>
  
  <p>All fields referenced above except A.X, and by extension B.X, are indexed: L, M, N, P, Y, Z.</p>
  
  <p>This is being run on a laptop with 8 GB RAM, a Core i7 Q720 1.6 GHz quad core processor, and Windows 7 x64. I am running Postgres 32 bit for compatibility with PostGIS 1.5.3. 64 bit PostGIS for Windows isn't available yet. (32 bit Postgres means it can't use more than 2 GB RAM in Windows, but I doubt that's an issue here.)</p>
  
  <p>Here's the result of EXPLAIN:</p>
  
  <pre><code>Update on A  (cost=727684.76..945437.01 rows=2032987 width=330)
    CTE B
      -&gt;  WindowAgg  (cost=491007.50..542482.47 rows=2058999 width=43)
            -&gt;  Sort  (cost=491007.50..496155.00 rows=2058999 width=43)
                  Sort Key: A.L, A.N, A.M, A.P
                  -&gt;  Seq Scan on A  (cost=0.00..85066.80 rows=2058999 width=43)
                        Filter: (Y = 1)
    -&gt;  Hash Join  (cost=185202.29..402954.54 rows=2032987 width=330)
          Hash Cond: ((B.Z)::text = (A.Z)::text)
          -&gt;  CTE Scan on B  (cost=0.00..41179.98 rows=2058999 width=88)
          -&gt;  Hash  (cost=85066.80..85066.80 rows=2058999 width=266)
                -&gt;  Seq Scan on A  (cost=0.00..85066.80 rows=2058999 width=266)
                      Filter: (Y = 1)
  </code></pre>