PostgreSQL 使用心得(1)

在where条件里面尽量不要使用函数，使用函数会进行遍历操作，

如下：

--不使用时间函数
explainanalyse
selectc_index_code,sum(off_line_time)asoff_line_timefromresource_camera_info_merger
wheremerger_timebetween'2015-11-03'and'2015-11-11'
groupbyc_index_code;

HashAggregate(cost=9872.43..9906.05rows=3362width=28)(actualtime=64.240..67.006rows=12243loops=1)
GroupKey:c_index_code
->SeqScanonresource_camera_info_merger(cost=0.00..9368.59rows=100766width=28)(actualtime=0.011..37.755rows=99377loops=1)
Filter:((merger_time>='2015-11-03'::date)AND(merger_time<='2015-11-11'::date))
RowsRemovedbyFilter:270996
Planningtime:0.158ms
Executiontime:67.342ms

--使用时间转换函数
explainanalyze
selectc_index_code,sum(off_line_time)asoff_line_timefromresource_camera_info_merger
wheremerger_timebetweento_date('2015-11-03','yyyy-mm-dd')andto_date('2015-11-11','yyyy-mm-dd')
groupbyc_index_code;

HashAggregate(cost=11724.29..11757.91rows=3362width=28)(actualtime=519.805..522.717rows=12243loops=1)
GroupKey:c_index_code
->SeqScanonresource_camera_info_merger(cost=0.00..11220.46rows=100766width=28)(actualtime=0.017..490.643rows=99377loops=1)
Filter:((merger_time>=to_date('2015-11-03'::text,'yyyy-mm-dd'::text))AND(merger_time<=to_date('2015-11-11'::text,'yyyy-mm-dd'::text)))
RowsRemovedbyFilter:270996
Planningtime:0.119ms
Executiontime:523.208ms

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!