title: PostgreSQL: speed up SELECT query in table with millions of rows
body: <p>I have a table with > 4.5 million rows and my <code>SELECT</code> query is far too slow for my needs.</p>
  
  <p>The table is created with:</p>
  
  <pre><code>CREATE TABLE all_legs (
                  carrier TEXT,
                  dep_hub TEXT,
                  arr_hub TEXT,
                  dep_dt TIMESTAMP WITH TIME ZONE,
                  arr_dt TIMESTAMP WITH TIME ZONE,
                  price_ct INTEGER,
                  ... 5 more cols ...,
                  PRIMARY KEY (carrier, dep_hub, arr_hub, dep_dt, arr_dt, ...3 other cols...)
                  )
  </code></pre>
  
  <p>When I want to <code>SELECT</code> all rows for a certain date, the query is too slow; it takes between 12sec and 20 seconds. My aim is that it takes max 1 sec. I expect the query to return between 0.1% and 1% of the rows contained in the table.</p>
  
  <p>The query is quite simple:</p>
  
  <pre><code>SELECT * FROM all_legs WHERE dep_dt::date = '2017-08-15' ORDER BY price_ct ASC
  </code></pre>
  
  <p><code>EXPLAIN ANALYZE</code> returns:</p>
  
  <pre><code>Sort  (cost=197154.69..197212.14 rows=22982 width=696) (actual time=14857.300..14890.565 rows=31074 loops=1)
    Sort Key: price_ct
    Sort Method: external merge  Disk: 5256kB
    -&gt;  Seq Scan on all_legs  (cost=0.00..188419.85 rows=22982 width=696) (actual time=196.738..14581.143 rows=31074 loops=1)
          Filter: ((dep_dt)::date = '2017-08-15'::date)
          Rows Removed by Filter: 4565249
  Planning time: 0.572 ms
  Execution time: 14908.274 ms
  </code></pre>
  
  <p><em>Note: I learned yesterday about this command, so I am still not able to fully understand all that is returned.</em></p>
  
  <p>I have tried using <code>index-only scans</code>, as suggested <a href="https://stackoverflow.com/a/13236089/5802289">here</a>, by running the command: <code>CREATE index idx_all_legs on all_legs(dep_dt);</code> but I did not notice any difference in running time. I also tried creating the index for all columns, as I want all columns return.</p>
  
  <p>Another thought was sorting all rows by <code>dep_dt</code>, so then the search of all rows fulfilling the condition should be much faster as they would not be scattered. Unfortunately, I don't know how to implement this.</p>
  
  <p>Is there a way to make it as fast as I am aiming to?</p>
  
  <hr>
  
  <h1>Solution</h1>
  
  <p>As suggested in the <a href="https://stackoverflow.com/a/45321419/5802289">Laurenz' answer</a>, by adding an index <code>CREATE INDEX IF NOT EXISTS idx_dep_dt_price ON all_legs(dep_dt, price_ct);</code> and adapting the condition in the <code>SELECT</code> to <code>WHERE dep_dt &gt;= '2017-08-15 00:00:00' AND dep_dt &lt; '2017-08-16 00:00:00'</code> has reduced the running time to 1/4. Even if it is a very good improvement, that means running times between 2 and 6 seconds.</p>
  
  <p>Any additional idea to reduce the running time even further would be appreciated.</p>