title: Postgresql - How to speed up for updating huge table(100 million rows)?
body: <p>I have two huge tables:</p>
  
  <pre><code>Table "public.tx_input1_new" (100,000,000 rows) 
  
       Column     |            Type             | Modifiers
  ----------------|-----------------------------|----------
   blk_hash       | character varying(500)      |
   blk_time       | timestamp without time zone |
   tx_hash        | character varying(500)      |
   input_tx_hash  | character varying(100)      |
   input_tx_index | smallint                    |
   input_addr     | character varying(500)      |
   input_val      | numeric                     |
  
  Indexes:
      "tx_input1_new_h" btree (input_tx_hash, input_tx_index) 
  </code></pre>
  
  <hr>
  
  <pre><code>Table "public.tx_output1_new" (100,000,000 rows)
  
      Column    |          Type          | Modifiers
  --------------+------------------------+-----------
   tx_hash      | character varying(100) |
   output_addr  | character varying(500) |
   output_index | smallint               |
   input_val    | numeric                |
  
  Indexes:
      "tx_output1_new_h" btree (tx_hash, output_index)
  </code></pre>
  
  <p>I want to update table1 by the other table:</p>
  
  <pre><code>UPDATE tx_input1 as i
  SET 
    input_addr = o.output_addr,
    input_val = o.output_val
  FROM tx_output1 as o
  WHERE 
    i.input_tx_hash = o.tx_hash
    AND i.input_tx_index = o.output_index;
  </code></pre>
  
  <p>Before I execute this SQL command, I already created the index for this two table:</p>
  
  <pre><code>CREATE INDEX tx_input1_new_h ON tx_input1_new (input_tx_hash, input_tx_index);
  
  CREATE INDEX tx_output1_new_h ON tx_output1_new (tx_hash, output_index);
  </code></pre>
  
  <p>I use <code>EXPLAIN</code> command to see the query plan, but it didn't use the index I created. </p>
  
  <p>It took about 14-15 hours to complete this <code>UPDATE</code>. </p>
  
  <p>What is the problem within it?</p>
  
  <p>How can I shorten the execution time, or tune my database/table? </p>
  
  <p>Thank you.</p>