I have a following query SELECT * FROM table_name WHERE #3_column BETWEEN start_date AND end_date which is taking about 10 seconds on my computer to execute and returns about 180k rows. Table schema is shown below:

Column      |  Type
#1  column  |  boolean
#2  column  |  double precision
#3  column  |  date
#4  column  |  date
#5  column  |  double precision
#6  column  |  double precision
#7  column  |  double precision
#8  column  |  integer
#9  column  |  double precision
#10 column  |  integer
#11 column  |  character varying(6)

Additional info: indexes are set on #3_column but query is doing sequential scan because of this fact.

My question is how to optimize this query? (I presume that reading from disk takes too long and that this cannot be optimized on query level). Thank you!

EDIT: this is result of explain (buffers, analyze) select * ... (in this scenario there is more than 150K rows returned)

Seq Scan on table_name  (cost=0.00..62504.01 rows=1298033 width=61) (actual time=0.015..1689.019 rows=1310109 loops=1)
  Filter: ((#3_column >= start_date::date) AND (#3_column <= end_date::date))
  Rows Removed by Filter: 926092
  Buffers: shared hit=20845 read=8116
Planning time: 0.124 ms
Execution time: 1899.839 ms

More info: table will have (when data is fully imported) around 20 milion rows, I am using postgres version 9.4.4

  • 1
    Show explain (buffers, analyze) select ... output. If you insist on "anonymizing" the schema and query, make sure you do so consistently so the explain analyze output matches the table definition. Edit the question to add the relevant info (see wiki.postgresql.org/wiki/Slow_Query_Questions) then comment here when done. Table size and index size is also important, so show that. You note that the proportion of data that matches the query is important... but what proportion is it? – Craig Ringer Oct 12 '15 at 11:03
  • 1
    Also, you SELECT *. Do you really need all columns? If not, you might be able to create an index that can be used for an index-only scan. – Craig Ringer Oct 12 '15 at 11:06
  • And what are you doing with 180k rows? It's very rare one really wants to process that much data. – dezso Oct 12 '15 at 12:07
  • So you are returning more than 90% of the whole table - no wonder your index is not being used. @CraigRinger is right here about the index-only scan - but then you should not select every column. – dezso Oct 12 '15 at 12:37
  • I have updated my question, please take a look again if I missed something. – Petar Oct 13 '15 at 8:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.