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