New query taking over 60% of running time As part of a daily automated reporting system, we run a series of queries to check different operating parameters from our customers' assets tables.

Last Tuesday I added a new check that works, but triggered a few performance alarms that led me to analyze what might be wrong and I ended up realizing that the new check actually increased the overall checks time about 160%.

Drilling down this new check I narrowed it to actual DB query that is affecting the whole process:

select case COALESCE(max(driverid),-1) when 0 then false when -1 then null else true end 
from reports.avl_historico_354676052451142 
where DATE_PART('day', now() - fecha ) &lt; 5;


And when doing an explain analyze verbose, I get:

Aggregate  (cost=75004.56..75004.58 rows=1 width=4) (actual time=544.460..544.461 rows=1 loops=1)
  Output: CASE COALESCE(max(driverid), (-1)) WHEN 0 THEN false WHEN (-1) THEN NULL::boolean ELSE true END
  -&gt;  Seq Scan on reports.avl_historico_354676052451142  (cost=0.00..73782.38 rows=488873 width=4) (actual time=535.565..541.467 rows=9661 loops=1)
        Output: fecha, latitud, longitud, altitud, velocidad, cog, nsat, tipo, utc_hora, fix_fecha, imei, registro, input1, input2, input3, input4, hdop, adc, ignicion, adc2, power, driverid, ibutton2, ibutton3, ibutton4, trailerid, adc3, adc4, horometro, odometro, panico, bateria, bateriaint
        Filter: (date_part('day'::text, (now() - avl_historico_354676052451142.fecha)) &lt; 5::double precision)
        Rows Removed by Filter: 1457143
Planning time: 0.078 ms
Execution time: 544.582 ms


So I'm guessing that this query might need some kind of help / improvement, but I'm completely clueless about it. So I'm asking: what improvements would you recommend for this scenario? Indexes? Query rephrasing? Others? What else should I provide for deeper understanding of the model?

Please consider that the sample table in the query has a large number in its name, which stands for a deviceId and we have around 30k tables of those which are queried regularly...it's some kind of a table partitioning model.

Update!

New results from the indexing suggested by @a_horse_with_no_name :

Aggregate  (cost=5478.72..5478.74 rows=1 width=4) (actual time=8.302..8.302 rows=1 loops=1)
  Output: CASE COALESCE(max(driverid), (-1)) WHEN 0 THEN false WHEN (-1) THEN NULL::boolean ELSE true END
  -&gt;  Index Scan using reports_avl_historico_354676052451142_index_fecha on reports.avl_historico_354676052451142  (cost=0.43..5473.68 rows=2017 width=4) (actual time=0.026..4.644 rows=12249 loops=1)
        Output: fecha, latitud, longitud, altitud, velocidad, cog, nsat, tipo, utc_hora, fix_fecha, imei, registro, input1, input2, input3, input4, hdop, adc, ignicion, adc2, power, driverid, ibutton2, ibutton3, ibutton4, trailerid, adc3, adc4, horometro, odometro, panico, bateria, bateriaint
        Index Cond: (avl_historico_354676052451142.fecha &gt; (('now'::cstring)::date - 5))
Planning time: 0.181 ms
Execution time: 8.332 ms


I'll time the overall execution and keep you posted.
The expression where DATE_PART('day', now() - fecha ) &lt; 5 can not make use of an index on the column fecha. 

Rewrite the expression so that it can use an index:

where fecha &gt; current_date - 5