CODEWITHSUNDEEP
sqlpostgresqlpostgresql-performance
Max Pinto
Max Pinto

Reputation: 1483

How to Increase Query Speed

i am trying to speed up some query in postgresql, currently i think is slow, considering that i want to get by date range, currently i have this:

select enc.inputdatetime::date dateMed, enc.transmissioncode,
   max(det.devicelevel) devicelevel, max(det.pressure) pressure,
   max(det.battery) battery,enc.remotelocationid,max(det.loop1con) loop1con 
from tl.tlinputdetail det 
inner join tl.tlinputtable enc on det.oldmedicionid = enc.oldmedicionid 
   where TRIM(enc.transmissioncode)= '005'
   and enc.inputdatetime::date between '2015-12-12' and '2016-11-12'                           
group by 
   enc.transmissioncode,enc.remotelocationid,enc.inputdatetime::date
order by 
   enc.inputdatetime::date asc;

Total query runtime: 47.6 secs 60 rows retrieved.

How can i increase my query?, i have index in both tables, in oldmedicionid column, transmissioncode, and inputdatetime

Query Explain

"Sort  (cost=105519.94..105519.96 rows=7 width=30)"
"  Sort Key: ((enc.inputdatetime)::date)"
"  ->  HashAggregate  (cost=105519.76..105519.85 rows=7 width=30)"
"        Group Key: (enc.inputdatetime)::date, enc.transmissioncode, enc.remotelocationid"
"        ->  Nested Loop  (cost=0.43..105517.50 rows=129 width=30)"
"              ->  Seq Scan on tlinputtable enc  (cost=0.00..104881.30 rows=64 width=31)"
"                    Filter: (((inputdatetime)::date >= '2015-12-12'::date) AND ((inputdatetime)::date <= '2016-11-12'::date) AND (btrim((transmissioncode)::text) = '005'::text))"
"              ->  Index Scan using tlinputdetail_oldmedicionididx on tlinputdetail det  (cost=0.43..9.90 rows=4 width=15)"
"                    Index Cond: (oldmedicionid = enc.oldmedicionid)"

Detailed explain, analyze, verbose

 "Sort  (cost=105519.94..105519.96 rows=7 width=30) (actual time=57948.774..57948.782 rows=61 loops=1)"
"  Output: ((enc.inputdatetime)::date), enc.transmissioncode, (max((det.devicelevel)::text)), (max((det.pressure)::text)), (max((det.battery)::text)), enc.remotelocationid, (max((det.loop1con)::text))"
"  Sort Key: ((enc.inputdatetime)::date)"
"  Sort Method: quicksort  Memory: 29kB"
"  ->  HashAggregate  (cost=105519.76..105519.85 rows=7 width=30) (actual time=57948.655..57948.717 rows=61 loops=1)"
"        Output: ((enc.inputdatetime)::date), enc.transmissioncode, max((det.devicelevel)::text), max((det.pressure)::text), max((det.battery)::text), enc.remotelocationid, max((det.loop1con)::text)"
"        Group Key: (enc.inputdatetime)::date, enc.transmissioncode, enc.remotelocationid"
"        ->  Nested Loop  (cost=0.43..105517.50 rows=129 width=30) (actual time=21.621..57708.114 rows=62181 loops=1)"
"              Output: (enc.inputdatetime)::date, enc.transmissioncode, enc.remotelocationid, det.devicelevel, det.pressure, det.battery, det.loop1con"
"              ->  Seq Scan on tl.tlinputtable enc  (cost=0.00..104881.30 rows=64 width=31) (actual time=0.143..1641.444 rows=20727 loops=1)"
"                    Output: enc.inputid, enc.inputdatetime, enc.packagesqty, enc.remotelocationid, enc.transmissioncode, enc.oldmedicionid"
"                    Filter: (((enc.inputdatetime)::date >= '2015-12-12'::date) AND ((enc.inputdatetime)::date <= '2016-11-12'::date) AND (btrim((enc.transmissioncode)::text) = '005'::text))"
"                    Rows Removed by Filter: 2556266"
"              ->  Index Scan using tlinputdetail_oldmedicionididx on tl.tlinputdetail det  (cost=0.43..9.90 rows=4 width=15) (actual time=2.467..2.699 rows=3 loops=20727)"
"                    Output: det.inputdetailid, det.inputid, det.devicelevel, det.pressure, det.battery, det.inputdatetime, det.devicecontrol, det.volumecon, det.pressurevolumecon, det.weightcon, det.decimalunit, det.weightunitcon, det.loop1con, det.loop2co (...)"
"                    Index Cond: (det.oldmedicionid = enc.oldmedicionid)"
"Planning time: 0.549 ms"
"Execution time: 57948.902 ms"

Upvotes: 0

Views: 124

Answers (1)

LongBeard_Boldy
LongBeard_Boldy

Reputation: 812

As I see from explain, tlinputdetail is a device table , tlinputtable is log like table which contains actual main data. Explain shows that only index used is on tlinputdetail and the reason that query is slow is that no filter condition does use any kind of index and database is forced to use sequence scan on the tlinputtable, which I assume is quite large table. To optimise this query you should create 2 indexes: on inputdatetime as 

CREATE INDEX ON tl.tlinputtable ((inputdatetime::date) ASC NULLS LAST);

and on transmissioncode as 

CREATE INDEX ON tl.tlinputtable ((trim(transmissioncode)) ASC NULLS LAST);

Edit:

for timestamp with timezone as 

CREATE INDEX ON tl.tlinputtable  (DATE(inputdatetime AT TIME ZONE 'UTC') ASC NULLS LAST);

and use instead something like this this 

 (date(timezone('UTC'::text, inputdatetime )) between '2015-01-01'::date and ....)

use timezone you need instead of UTC

might help link

Upvotes: 2

Related Questions