Improve the speed of a BETWEEN search I have a query witch is selecting some data between two dates:

SELECT * FROM rk.p_reports
WHERE project_id = 1 AND engine_id = 1 AND type_id = 1 
  AND created_at::date BETWEEN now()::date - 14 AND now()::date


I have tried creating various indexes to speedup the queries but without any luck

First index: project_id, engine_id, type_id, created_at ASC, created_at DESC

Second index: project_id, engine_id, type_id, created_at ASC AND project_id, engine_id, type_id, created_at DESC

Third index: project_id, engine_id, type_id, created_at ASC

And other combinations, and non of the index was picked without turning enable_seqscan = off

At the moment it will take around 3 sec to return my rows, this partition has around 1.5 mil rows. I am using Postgresql 9.4 on the DB server has 8 Gb ram and 6 cores.

Is there any way I can reduce this to under a sec?

Seq Scan on p_reports_1  (cost=0.00..57649.04 rows=4322 width=40) (actual time=614.221..696.583 rows=92607 loops=1)
Filter: ((project_id = 1) AND (engine_id = 1) AND (type_id = 1) AND ((created_at)::date &lt;= (now())::date) AND ((created_at)::date &gt;= ((now())::date - 14)))
Rows Removed by Filter: 1063344
Planning time: 0.200 ms
Execution time: 703.493 ms


Update, query modified as @ypercube sugested:

Bitmap Heap Scan on p_reports_1  (cost=2952.28..17478.45 rows=89005 width=40) (actual time=17.485..34.569 rows=87712 loops=1)
Recheck Cond: ((project_id = 1) AND (engine_id = 1) AND (type_id = 1) AND (created_at &gt;= ((now())::date - 14)) AND (created_at &lt;= (now())::date))
Heap Blocks: exact=1180
 -&gt;  Bitmap Index Scan on p_reports_1_asc_created_at  (cost=0.00..2930.03 rows=89005 width=0) (actual time=17.236..17.236 rows=87712 loops=1)
    Index Cond: ((project_id = 1) AND (engine_id = 1) AND (type_id = 1) AND (created_at &gt;= ((now())::date - 14)) AND (created_at &lt;= (now())::date))
Planning time: 0.319 ms
Execution time: 41.569 ms
I suspect the indexes are not chosen by the optimizer because they have the column created_at (which is a timestamp?) but the query is converting it to date.

I suggest you either create an index on (project_id, engine_id, type_id, created_at::date) or just keep the index on (project_id, engine_id, type_id, created_at) and change the condition to:

 AND created_at &gt;= now()::date - 14 
 AND created_at &lt; now()::date + 1





You can use current_date instead of now()::date.
I can't think of any situation where the (project_id, engine_id, type_id, created_at ASC, created_at DESC) would be useful. Drop this one.
About the two other indexes, (..., created_at ASC) and (..., created_at DESC), either one would be ok for the query (with the changed condition). I suggest you keep only one of them, unless you have other queries that need both.