title: Postgres Explain Plans Are DIfferent
body: <p>I have 2 databases running on the same server.
  Both are development databases with the same structure, but different amounts of data.
  Running Postgres 9.2 on Linux (Centos/Redhat)</p>
  
  <p>I'm running the following SQL on each database, but getting very different results in the performance.</p>
  
  <p><strong>Note:</strong> the write_history table structure is identical in each DB and have the same indexes/constraints.</p>
  
  <p>Here is the SQL:</p>
  
  <pre><code> explain analyze SELECT * FROM write_history WHERE fk_device_rw_id =
   'd2c969b8-2609-11e3-80ca-0750cff1e96c'  AND fk_write_history_status_id
   = 5  ORDER BY update_time DESC LIMIT 1 ;
  </code></pre>
  
  <p>And the Explain Plans for each DB:</p>
  
  <p><strong>DB1 - PreProd</strong></p>
  
  <pre><code> Limit  (cost=57902.39..57902.39 rows=1 width=103) (actual
   time=0.056..0.056 rows=0 loops=1)'   -&gt;  Sort 
   (cost=57902.39..57908.69 rows=2520 width=103) (actual
   time=0.053..0.053 rows=0 loops=1)'
           Sort Key: update_time'
           Sort Method: quicksort  Memory: 25kB'
           -&gt;  Bitmap Heap Scan on write_history  (cost=554.04..57889.79 rows=2520 width=103) (actual time=0.033..0.033 rows=0 loops=1)'
                 Recheck Cond: (fk_device_rw_id = 'd2c969b8-2609-11e3-80ca-0750cff1e96c'::uuid)'
                 Filter: (fk_write_history_status_id = 5)'
                 -&gt;  Bitmap Index Scan on idx_write_history_fk_device_rw_id  (cost=0.00..553.41 rows=24034
   width=0) (actual time=0.028..0.028 rows=0 loops=1)'
                       Index Cond: (fk_device_rw_id = 'd2c969b8-2609-11e3-80ca-0750cff1e96c'::uuid)' Total runtime: 0.112 ms'
  </code></pre>
  
  <p><strong>DB2 - QA</strong></p>
  
  <pre><code> Limit  (cost=50865.41..50865.41 rows=1 width=108) (actual
   time=545.521..545.523 rows=1 loops=1)'   -&gt;  Sort 
   (cost=50865.41..50916.62 rows=20484 width=108) (actual
   time=545.518..545.518 rows=1 loops=1)'
           Sort Key: update_time'
           Sort Method: top-N heapsort  Memory: 25kB'
           -&gt;  Bitmap Heap Scan on write_history  (cost=1431.31..50762.99 rows=20484 width=108) (actual time=21.931..524.034 rows=22034
   loops=1)'
                 Recheck Cond: (fk_device_rw_id = 'd2cd81a6-2609-11e3-b574-47328bfa4c38'::uuid)'
                 Rows Removed by Index Recheck: 1401986'
                 Filter: (fk_write_history_status_id = 5)'
                 Rows Removed by Filter: 40161'
                 -&gt;  Bitmap Index Scan on idx_write_history_fk_device_rw_id  (cost=0.00..1426.19 rows=62074
   width=0) (actual time=19.167..19.167 rows=62195 loops=1)'
                       Index Cond: (fk_device_rw_id = 'd2cd81a6-2609-11e3-b574-47328bfa4c38'::uuid)' Total runtime: 545.588 ms'
  </code></pre>
  
  <p><strong>So a few questions:</strong></p>
  
  <ol>
  <li><p>what is the difference between "Sort Method: quicksort  Memory: 25kB" and "Sort Method: top-N heapsort  Memory: 25kB" ?</p></li>
  <li><p>What could be the cause for the total runtime to be so different?</p></li>
  </ol>
  
  <p>Table Row Count:</p>
  
  <p>DB1 : write_history row count: 5,863,565</p>
  
  <p>DB2 : write_history row count: 2,670,888</p>
  
  <p>Please let me know if further information is required.
  Thanks for the help.</p>