sql 字段先计算后再拿比对的字段进行比对 效率提升100倍
关于日期索引的使用，不要计算后再对比，否则使用不了索引
例如：以下执行不了索引，耗时很大
1 dywl=# explain analyze SELECT 2 car_bill.billno,car_bill.beginunit,car_bill.begincity,car_bill.endunit,car_bill.endcity,car_bill.pubtime,car_bill.goodstype,car_bill.mobile, 3 ST_Distance_Sphere(car_bill.point_geom,ST_GeometryFromText('POINT(113.91269 35.307258)',4326)) as leng, 4 sys_user.name,car_bill.totaltone,car_bill.weight,car_bill.carriage,car_bill.carriageunit,car_bill.remark 5 FROM 6 car_bill,sys_user 7 WHERE 8 EXTRACT(EPOCH FROM now()- car_bill.pubtime) < 60 9 and 10 car_bill.userid=sys_user.userid 11 and 12 ST_Distance_Sphere(car_bill.point_geom,ST_GeometryFromText('POINT(113.91269 35.307258)',4326))<=200000 13 ORDER BY 14 car_bill.point_geom <-> ST_GeometryFromText('POINT(113.91269 35.307258)',4326) ; 15 16 QUERY PLAN 17 18 ------------------------------------------------------------------ 19 Sort (cost=258916.19..259035.52 rows=47734 width=146) (actual time=565.754..565.754 rows=0 loops=1) 20 Sort Key: ((car_bill.point_geom <-> '0101000020E6100000B8585183697A5C4099B7EA3A54A74140'::geometry)) 21 Sort Method: quicksort Memory: 25kB 22 -> Hash Join (cost=59228.31..251615.60 rows=47734 width=146) (actual time=565.745..565.745 rows=0 loops=1) 23 Hash Cond: ((car_bill.userid)::text = (sys_user.userid)::text) 24 -> Seq Scan on car_bill (cost=0.00..173902.11 rows=47734 width=153) (actual time=565.743..565.743 rows=0 loops=1) 25 Filter: ((date_part('epoch'::text, (now() - (pubtime)::timestamp with time zone)) < 60::double precision) AND (_st_distance(geography(point_geom), '0101000020E6100000B8585183 26 697A5C4099B7EA3A54A74140'::geography, 0::double precision, false) <= 200000::double precision)) 27 Rows Removed by Filter: 423616 28 -> Hash (cost=49538.47..49538.47 rows=527747 width=17) (never executed) 29 -> Seq Scan on sys_user (cost=0.00..49538.47 rows=527747 width=17) (never executed) 30 Planning time: 1.064 ms 31 Execution time: 565.836 ms 32 (12 rows)
变更为以下后，可走索引，提升百倍，所以下面的才是正确的
1 dywl=# explain analyze SELECT 2 dywl-# car_bill.billno,car_bill.beginunit,car_bill.begincity,car_bill.endunit,car_bill.endcity,car_bill.pubtime,car_bill.goodstype,car_bill.mobile, 3 dywl-# ST_Distance_Sphere(car_bill.point_geom,ST_GeometryFromText('POINT(113.91269 35.307258)',4326)) as leng, 4 dywl-# sys_user.name,car_bill.totaltone,car_bill.weight,car_bill.carriage,car_bill.carriageunit,car_bill.remark 5 dywl-# FROM 6 dywl-# car_bill,sys_user 7 dywl-# WHERE 8 dywl-# car_bill.pubtime > now() - interval '60 seconds' 9 dywl-# and car_bill.userid=sys_user.userid 10 dywl-# and ST_Distance_Sphere(car_bill.point_geom,ST_GeometryFromText('POINT(113.91269 35.307258)',4326))<=200000 11 dywl-# ORDER BY 12 dywl-# car_bill.point_geom <-> ST_GeometryFromText('POINT(113.91269 35.307258)',4326) 13 dywl-# ; 14 15 QUERY PLAN 16 ---------------------------------------------------------------------------------------------------------- 17 Sort (cost=4424.85..4425.43 rows=235 width=146) (actual time=0.369..0.369 rows=0 loops=1) 18 Sort Key: ((car_bill.point_geom <-> '0101000020E6100000B8585183697A5C4099B7EA3A54A74140'::geometry)) 19 Sort Method: quicksort Memory: 25kB 20 -> Nested Loop (cost=0.85..4415.59 rows=235 width=146) (actual time=0.351..0.351 rows=0 loops=1) 21 -> Index Scan using car_bill_pubtime_idx on car_bill (cost=0.43..2388.74 rows=235 width=153) (actual time=0.351..0.351 rows=0 loops=1) 22 Index Cond: ((pubtime)::timestamp without time zone > (now() - '00:01:00'::interval)) 23 Filter: (_st_distance(geography(point_geom), '0101000020E6100000B8585183697A5C4099B7EA3A54A74140'::geography, 0::double precision, false) <= 200000::double precision) 24 Rows Removed by Filter: 1 25 -> Index Scan using sys_user_userid on sys_user (cost=0.42..8.36 rows=1 width=17) (never executed) 26 Index Cond: ((userid)::text = (car_bill.userid)::text) 27 Planning time: 1.161 ms 28 Execution time: 0.483 ms 29 (12 rows)
