title: Why does the following Postgres SQL query take so long?
body: <p>The raw query is as follows</p>
  
  <pre><code>SELECT "TIME", "TRADEPRICE"
    FROM "YEAR" where "DATE"='2010-03-01' 
    and "SECURITY"='STW.AX' 
    AND "TIME" &lt; '10:16:00' 
    AND "TYPE" = 'TRADE'
    ORDER BY "TIME" ASC LIMIT 3
  </code></pre>
  
  <p>I have built three indices as follows</p>
  
  <pre><code>Columns "DATE" DESC NULLS LAST
  Columns "SECURITY" DESC NULLS LAST
  Columns "TIME" DESC NULLS LAST
  </code></pre>
  
  <p>I do not index TYPE because it only takes one of two possible values</p>
  
  <p>Explain analyze produces the following</p>
  
  <pre><code>"Limit  (cost=50291.28..50291.28 rows=3 width=16) (actual time=1794484.566..1794484.567 rows=3 loops=1)"
  "  -&gt;  Sort  (cost=50291.28..50291.29 rows=4 width=16) (actual time=1794484.562..1794484.563 rows=3 loops=1)"
  "        Sort Key: "TIME""
  "        Sort Method:  top-N heapsort  Memory: 25kB"
  "        -&gt;  Bitmap Heap Scan on "YEAR"  (cost=48569.54..50291.24 rows=4 width=16) (actual time=1794411.662..1794484.498 rows=20 loops=1)"
  "              Recheck Cond: (("SECURITY" = 'STW.AX'::bpchar) AND ("DATE" = '2010-03-01'::date))"
  "              Filter: (("TIME" &lt; '10:16:00'::time without time zone) AND ("TYPE" = 'TRADE'::bpchar))"
  "              -&gt;  BitmapAnd  (cost=48569.54..48569.54 rows=430 width=0) (actual time=1794411.249..1794411.249 rows=0 loops=1)"
  "                    -&gt;  Bitmap Index Scan on security_desc  (cost=0.00..4722.94 rows=166029 width=0) (actual time=1793917.506..1793917.506 rows=1291933 loops=1)"
  "                          Index Cond: ("SECURITY" = 'STW.AX'::bpchar)"
  "                    -&gt;  Bitmap Index Scan on date_desc  (cost=0.00..43846.35 rows=2368764 width=0) (actual time=378.698..378.698 rows=2317130 loops=1)"
  "                          Index Cond: ("DATE" = '2010-03-01'::date)"
  "Total runtime: 1794485.224 ms"
  </code></pre>
  
  <p>The database is about 1 billion rows running on Core2Quad with 8gig RAM on Ubuntu 64bit. Surely this query should not take half an hour</p>