我试图加快postgresql中的查询速度,考虑到我想按日期范围查询,目前我认为速度很慢,目前我有以下内容:
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;总计查询运行时:检索了47.6秒60行。
如何增加我的查询?,我在两个表中,在oldmedicionid列、transmissioncode和inputdatetime中都有索引。
查询解释
"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)"详细解释,分析,详细的
"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"发布于 2016-02-10 09:19:35
正如我从解释中看到的,tlinputdetail是一个设备表,tlinputtable是包含实际主数据的类似日志的表。解释显示,只有使用的索引在tlinputdetail上,而查询速度慢的原因是没有过滤器条件使用任何类型的索引,而且数据库被迫在tlinputtable上使用序列扫描,我假设这是相当大的表。要优化这个查询,您应该创建2个索引:在inputdatetime上
CREATE INDEX ON tl.tlinputtable ((inputdatetime::date) ASC NULLS LAST);以及在传输代码上
CREATE INDEX ON tl.tlinputtable ((trim(transmissioncode)) ASC NULLS LAST);编辑:
对于带有时区的时间戳,如
CREATE INDEX ON tl.tlinputtable (DATE(inputdatetime AT TIME ZONE 'UTC') ASC NULLS LAST);用这样的东西代替
(date(timezone('UTC'::text, inputdatetime )) between '2015-01-01'::date and ....)使用您需要的时区,而不是UTC。
可能对链接有帮助
https://stackoverflow.com/questions/35308570
复制相似问题