我想检索另一组点的给定范围内的所有点.比方说,找到距离任何地铁站500米范围内的所有商店.
我写了这个查询,这很慢,并且想要优化它:
SELECT DISCTINCT ON(locations.id) locations.id FROM locations,pois WHERE pois.poi_kind = 'subway' AND ST_DWithin(locations.coordinates,pois.coordinates,500,false);
我正在运行最新版本的Postgres和PostGis(Postgres 9.5,PostGis 2.2.1)
这是表元数据:
Table "public.locations" Column | Type | Modifiers --------------------+-----------------------------+-------------------------------------------------------- id | integer | not null default nextval('locations_id_seq'::regclass) coordinates | geometry | Indexes: "locations_coordinates_index" gist (coordinates) Table "public.pois" Column | Type | Modifiers -------------+-----------------------------+--------------------------------------------------- id | integer | not null default nextval('pois_id_seq'::regclass) coordinates | geometry | poi_kind_id | integer | Indexes: "pois_pkey" PRIMARY KEY,btree (id) "pois_coordinates_index" gist (coordinates) "pois_poi_kind_id_index" btree (poi_kind_id) Foreign-key constraints: "pois_poi_kind_id_fkey" FOREIGN KEY (poi_kind_id) REFERENCES poi_kinds(id)
这是EXPLAIN(ANALYZE,BUFFERS)的结果:
Unique (cost=2407390.71..2407390.72 rows=2 width=4) (actual time=3338.080..3338.252 rows=918 loops=1) Buffers: shared hit=559 -> Sort (cost=2407390.71..2407390.72 rows=2 width=4) (actual time=3338.079..3338.145 rows=963 loops=1) Sort Key: locations.id Sort Method: quicksort Memory: 70kB Buffers: shared hit=559 -> Nested Loop (cost=0.00..2407390.71 rows=2 width=4) (actual time=2.466..3337.835 rows=963 loops=1) Join Filter: (((pois.coordinates)::geography && _st_expand((locations.coordinates)::geography,500::double precision)) AND ((locations.coordinates)::geography && _st_expand((pois.coordinates)::geography,500::double precision)) AND _st_dwithin((pois.coordinates)::geography,(locations.coordinates)::geography,500::double precision,false)) Rows Removed by Join Filter: 4531356 Buffers: shared hit=559 -> Seq Scan on locations (cost=0.00..791.68 rows=24168 width=36) (actual time=0.005..3.100 rows=24237 loops=1) Buffers: shared hit=550 -> Materialize (cost=0.00..10.47 rows=187 width=32) (actual time=0.000..0.009 rows=187 loops=24237) Buffers: shared hit=6 -> Seq Scan on pois (cost=0.00..9.54 rows=187 width=32) (actual time=0.015..0.053 rows=187 loops=1) Filter: (poi_kind_id = 3) Rows Removed by Filter: 96 Buffers: shared hit=6 Planning time: 0.184 ms Execution time: 3338.304 ms (20 rows)