0

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 ) < 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
  ->  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)) < 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
  ->  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 > (('now'::cstring)::date - 5))
Planning time: 0.181 ms
Execution time: 8.332 ms

I'll time the overall execution and keep you posted.

4
  • 1
    Does analyze avl_historico_354676052451142 change anything? What are the indexes defined on that table? If you have an index on fecha you could rewrite the condition to: where fecha > current_date - 5 so that Postgres can make use of that index – a_horse_with_no_name Feb 22 '18 at 16:07
  • @a_horse_with_no_name I guess using analyze won't be an option as it would need to be run before the query execution so the overall time wouldn't decrease as the tables hold 1 year of historical information with 1467004 for that specific table. – Gonzalo Vasquez Feb 22 '18 at 16:49
  • @a_horse_with_no_name regarding indexes it does have one based on fecha, as follow: CREATE INDEX reports_avl_historico_354676052451142_index_fecha ON reports.avl_historico_354676052451142 USING btree (fecha) I'll try your suggestion, but how can I compare the results using explain so if it improves? – Gonzalo Vasquez Feb 22 '18 at 16:52
  • @a_horse_with_no_name your suggestion works amazing: it went from 60% down to 7% now. I'll try and apply that suggestion to other queries too that I have around. Great to learn from the gurus! – Gonzalo Vasquez Feb 22 '18 at 17:03
1

The expression where DATE_PART('day', now() - fecha ) < 5 can not make use of an index on the column fecha.

Rewrite the expression so that it can use an index:

where fecha > current_date - 5
1

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.