sqlフィールドは計算してからペアのフィールドを比較して効率を100倍に向上させる
10391 ワード
日付インデックスの使用については、計算してから比較しないでください.そうしないと、インデックスは使用できません.たとえば、次のようなインデックスは実行できません.時間がかかります.
以下に変更すると、インデックスを移動して百倍になるので、以下が正しいです.
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)