title: How to speed up update query on massive table
body: <p>I am currently in the process of transferring one of our existing databases onto a new ontology. The database follows a star schema with observation_fact being the center of the star with concept_dimension being a lookup table. In order to transfer to the new ontology I need to replace the concept_cd in observation_fact with a slightly different code in order to match the concept_cds in the new ontology.</p>
  
  <p>I have tried writing an update query to accomplish this migration however it has been running for 5 days and I don't think it is going to finish anytime soon. I have indexed the two relevant tables on concept_cd.</p>
  
  <p>This is the query that I initially wrote: </p>
  
  <pre class="lang-sql prettyprint-override"><code>Update observation_fact ofact
  Set concept_cd = q.cd
  From (Select ofact2.ctid, Case 
      When split_part(ofact2.concept_cd, ':', 1) = 'ICD10-CM'  Then replace(ofact2.concept_cd, 'ICD10-CM:', 'ICD10CM:')
      When split_part(ofact2.concept_cd, ':', 1) = 'ICD10-PCS' Then replace(ofact2.concept_cd, 'ICD10-PCS:', 'ICD10PCS:')
      When split_part(ofact2.concept_cd, ':', 1) = 'ICD9' And cdim.concept_path like '\\i2b2\\Diagnoses\\%'  Then replace(ofact2.concept_cd, 'ICD9:', 'ICD9CM:')
      When split_part(ofact2.concept_cd, ':', 1) = 'ICD9' And cdim.concept_path like '\\i2b2\\Procedures\\%' Then replace(ofact2.concept_cd, 'ICD9:', 'ICD9PROC:')
    End as cd
    From observation_fact ofact2
    Left Outer Join concept_dimension_bak cdim
    On ofact2.concept_cd = cdim.concept_cd
  ) as q
  Where ofact.ctid = q.ctid;
  </code></pre>
  
  <p>It felt very awkward to write since observation_fact does not have a true primary key or composite key thus I had to use ctid. Also I used observation_fact twice which from this answer <a href="https://stackoverflow.com/questions/23611601/speed-up-postgres-update-on-large-table">Speed up Postgres Update on Large Table</a> I know that this is a bad idea and probably part of the problem. I used left outer join because some of the concept_cds in observation_fact do not exist in concept_dimension_bak. As you can see the ICD10 replacements are very easy however for ICD9 I need to lookup the code in the old concept_dimension table in order to figure out which type of code it is and replace it accordingly. </p>
  
  <p>I expect this update query to perform the appropriate replacement on any rows in observation_fact where the case statement matches and ignore everything else.</p>