title: Postgresql large table update slows down
body: <p>I run an update on a large table (e.g. 8 GB). It is a simple update of 3 fields in the table. I had no problems running it under postgresql 9.1, it would take 40-60 minutes but it worked. I run the same query in 9.4 database (freshly created, not upgraded) and it starts the update fine but then slows down. It uses only ~2% CPU, the level if IO is 4-5MB/s and it is sitting there. No locks, no other queries or connections, just this single update SQL on the server.</p>
  
  <p>The SQL is below. "lookup" table has 12 records. The lookup can return only one row, it breaks a discrete scale (SMALLINT, -32768 .. +32767) into non-overlapping regions. "src" and "dest" tables are ~60 million records.</p>
  
  <pre><code>UPDATE dest SET
      field1 = src.field1,
      field2 = src.field2,
      field3_id = (SELECT lookup.id FROM lookup WHERE src.value BETWEEN lookup.min AND lookup.max)
  FROM src
  WHERE dest.id = src.id;
  </code></pre>
  
  <p>I thought my disk slowed down but I can copy 1 GB files in parallel to query execution and it runs fast at >40MB/s and I have only one disk (it is a VM with ISCSI media). All other disk operations are not impacted, there is plenty of IO bandwidth. At the same time PostgreSQL is just sitting there doing very little, running very slowly.</p>
  
  <p>I have 2 virtualized linux servers, one runs postgresql 9.1 and another runs 9.4. Both servers have close to identical postgresql configuration.</p>
  
  <p>Has anyone else had similar experience? I am running out of ideas. Help.</p>
  
  <p><strong>Edit</strong>
  The query "ran" for 20 hours I had to kill the connections and restart the server. Surprisingly it didn't kill the connection via query:</p>
  
  <pre><code>SELECT pg_terminate_backend(pid)
  FROM pg_stat_activity
  WHERE pid &lt;&gt; pg_backend_pid() AND datname = current_database();
  </code></pre>
  
  <p>and sever produced the following log:</p>
  
  <pre><code>2015-05-21 12:41:53.412 EDT FATAL:  terminating connection due to administrator command
  2015-05-21 12:41:53.438 EDT FATAL:  terminating connection due to administrator command
  2015-05-21 12:41:53.438 EDT STATEMENT:  UPDATE &lt;... this is 60,000,000 record table update statement&gt;
  </code></pre>
  
  <p>Also server restart took long time, producing the following log:</p>
  
  <pre><code>2015-05-21 12:43:36.730 EDT LOG:  received fast shutdown request
  2015-05-21 12:43:36.730 EDT LOG:  aborting any active transactions
  2015-05-21 12:43:36.730 EDT FATAL:  terminating connection due to administrator command
  2015-05-21 12:43:36.734 EDT FATAL:  terminating connection due to administrator command
  2015-05-21 12:43:36.747 EDT LOG:  autovacuum launcher shutting down
  2015-05-21 12:44:36.801 EDT LOG:  received immediate shutdown request
  2015-05-21 12:44:36.815 EDT WARNING:  terminating connection because of crash of another server process
  2015-05-21 12:44:36.815 EDT DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
  </code></pre>
  
  <p>"The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and <strong>possibly corrupted shared memory</strong>" - is this an indication of a bug in PostgreSQL?</p>
  
  <p><strong>Edit</strong>
  I tested 9.1, 9.3 and 9.4. Both 9.1 and 9.3 don't experience the slow down. 9.4 consistently slows down on large transactions. I noticed that when a transaction starts htop monitor indicates high CPU and the process status is "R" (running). Then it gradually changes to low CPU usage and status "D" - disk (see screenshot <img src="https://i.stack.imgur.com/Bache.png" alt="Disk waiting">). My biggest question is why 9.4 is different from 9.1 and 9.3? I have a dozen of servers and this effect is observed across the board.</p>