title: Improving performance for simple left join in postgreSQL
body: <p>I am trying to do a left join between two tables in a postgreSQL database and finding it takes about 14 minutes to run. From existing SO posts, it seems like this type of join should be on the order of seconds, so I'd like to know how to improve the performance of this join. I'm running <code>64-bit</code> <code>postgreSQL version 9.4.4</code> on a <code>Windows 8</code> machine w/ <code>8 GB RAM</code>, using <code>pgAdmin III</code>. The table structures are as follows:</p>
  
  <p>Table A: "parcels_qtr":</p>
  
  <blockquote>
    <p>parcel (text) | yr (int) | qtr (text) | lpid (pk, text) |</p>
  </blockquote>
  
  <p>Has 15.5 million rows, each column is indexed, and "lpid" is the primary key. I also ran this table through a standard vacuum process.</p>
  
  <p>Table B: "postalvac_qtr":</p>
  
  <blockquote>
    <p>parcel (text) | yr (int) | qtr (text) | lpid (pk, text) | vacCountY (int) |</p>
  </blockquote>
  
  <p>Has 618,000 records, all fields except "vacCountY" are indexed and "lpid" is the primary key. This also has gone through a standard vacuum process.</p>
  
  <p>When running with data output, it takes about 14 min. When running with <code>explain (analyze, buffers)</code> it takes a little over a minute. First question - is this difference in performance wholly attributable to printing the data or is something else going on here?</p>
  
  <p>And second question, can I get this run time down to a few seconds?</p>
  
  <p>Here is my SQL code:</p>
  
  <pre><code>EXPLAIN (ANALYZE, BUFFERS)
  select a.parcel,
     a.lpid,
     a.yr,
     a.qtr,
     b."vacCountY"
  from parcels_qtr as a
  left join postalvac_qtr as b
  on a.lpid = b.lpid;
  </code></pre>
  
  <p>And here are the results of my explain statement: <a href="https://explain.depesz.com/s/uKkK" rel="noreferrer">https://explain.depesz.com/s/uKkK</a></p>
  
  <p>I'm pretty new to postgreSQL so patience and explanations would be greatly appreciated!</p>