|
Что можно сделать для сокращения времени обработки запроса?
#39259400
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Запрос:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select *,(dist_start+dist_end) as sum_dist from
(select osm_id as id_start, way as start_way, ST_Distance_Spheroid(ST_Transform(way,4326), ST_GeomFromText('POINT(28.2789393 57.8155523)',4326), 'SPHEROID["WGS 84",6378137,298.257223563]') as dist_start
from ru_psk_point where public_transport='stop_position' and tags -> 'bus' = 'yes') t1,
(select osm_id as id_end, way as end_way, ST_Distance_Spheroid(ST_Transform(way,4326), ST_GeomFromText('POINT(28.2951125 57.8141805)',4326), 'SPHEROID["WGS 84",6378137,298.257223563]') as dist_end
from ru_psk_point where public_transport='stop_position' and tags -> 'bus' = 'yes') t2,
opengeo.ru_psk_rels rels
where array[id_start,id_end]<@rels.parts
and array['route','ref']<@rels.tags
and bus_idx_rels_parts(rels.id,id_start)<bus_idx_rels_parts(rels.id,id_end) limit 1
order by sum_dist limit 1;
Анализ:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
"Limit (cost=3176.27..3176.27 rows=1 width=657) (actual time=5735.833..5735.839 rows=1 loops=1)"
" -> Sort (cost=3176.27..3176.27 rows=1 width=657) (actual time=5735.799..5735.799 rows=1 loops=1)"
" Sort Key: ((st_distance_spheroid(st_transform(opengeo.ru_psk_point.way, 4326), '0101000020E6100000B311E39068473C40BB568C0464E84C40'::geometry, 'SPHEROID("WGS 84",6378137,298.257223562997)'::spheroid) + st_distance_spheroid(st_transform(opengeo.ru_psk_point.way, 4326), '0101000020E61000000B24287E8C4B3C403D450E1137E84C40'::geometry, 'SPHEROID("WGS 84",6378137,298.257223562997)'::spheroid)))"
" Sort Method: top-N heapsort Memory: 19kB"
" -> Nested Loop (cost=32.82..3176.26 rows=1 width=657) (actual time=9.605..5682.064 rows=4731 loops=1)"
" -> Nested Loop (cost=0.00..3137.90 rows=1 width=272) (actual time=0.086..1831.338 rows=32761 loops=1)"
" -> Seq Scan on ru_psk_point (cost=0.00..1568.94 rows=1 width=136) (actual time=0.045..8.347 rows=181 loops=1)"
" Filter: ((public_transport = 'stop_position'::text) AND ((tags -> 'bus'::text) = 'yes'::text))"
" Rows Removed by Filter: 24330"
" -> Seq Scan on ru_psk_point (cost=0.00..1568.94 rows=1 width=136) (actual time=0.014..7.718 rows=181 loops=181)"
" Filter: ((public_transport = 'stop_position'::text) AND ((tags -> 'bus'::text) = 'yes'::text))"
" Rows Removed by Filter: 24330"
" -> Bitmap Heap Scan on ru_psk_rels rels (cost=32.82..37.34 rows=1 width=385) (actual time=0.091..0.095 rows=0 loops=32761)"
" Recheck Cond: ((ARRAY[opengeo.ru_psk_point.osm_id, opengeo.ru_psk_point.osm_id] <@ parts) AND ('{route,ref}'::text[] <@ tags))"
" Filter: (bus_idx_rels_parts(id, opengeo.ru_psk_point.osm_id) < bus_idx_rels_parts(id, opengeo.ru_psk_point.osm_id))"
" Rows Removed by Filter: 0"
" -> BitmapAnd (cost=32.82..32.82 rows=1 width=0) (actual time=0.067..0.067 rows=0 loops=32761)"
" -> Bitmap Index Scan on idx_ru_psk_rels_gin_parts (cost=0.00..12.23 rows=31 width=0) (actual time=0.017..0.017 rows=0 loops=32761)"
" Index Cond: (ARRAY[opengeo.ru_psk_point.osm_id, opengeo.ru_psk_point.osm_id] <@ parts)"
" -> Bitmap Index Scan on idx_ru_psk_rels_gin_tags (cost=0.00..20.33 rows=44 width=0) (actual time=0.191..0.191 rows=499 loops=5735)"
" Index Cond: ('{route,ref}'::text[] <@ tags)"
"Total runtime: 5736.551 ms"
Если убрать ORDER, то время сразу устраивает в отличии от результата:
1.
2.
3.
4.
5.
6.
7.
8.
9.
select *,(dist_start+dist_end) as sum_dist from
(select osm_id as id_start, way as start_way, ST_Distance_Spheroid(ST_Transform(way,4326), ST_GeomFromText('POINT(28.2789393 57.8155523)',4326), 'SPHEROID["WGS 84",6378137,298.257223563]') as dist_start
from ru_psk_point where public_transport='stop_position' and tags -> 'bus' = 'yes') t1,
(select osm_id as id_end, way as end_way, ST_Distance_Spheroid(ST_Transform(way,4326), ST_GeomFromText('POINT(28.2951125 57.8141805)',4326), 'SPHEROID["WGS 84",6378137,298.257223563]') as dist_end
from ru_psk_point where public_transport='stop_position' and tags -> 'bus' = 'yes') t2,
opengeo.ru_psk_rels rels
where array[id_start,id_end]<@rels.parts
and array['route','ref']<@rels.tags
and bus_idx_rels_parts(rels.id,id_start)<bus_idx_rels_parts(rels.id,id_end) limit 1
Анализ:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
"Limit (cost=32.82..3176.26 rows=1 width=657) (actual time=11.365..11.371 rows=1 loops=1)"
" -> Nested Loop (cost=32.82..3176.26 rows=1 width=657) (actual time=11.343..11.343 rows=1 loops=1)"
" -> Nested Loop (cost=0.00..3137.90 rows=1 width=272) (actual time=0.082..0.525 rows=13 loops=1)"
" -> Seq Scan on ru_psk_point (cost=0.00..1568.94 rows=1 width=136) (actual time=0.041..0.041 rows=1 loops=1)"
" Filter: ((public_transport = 'stop_position'::text) AND ((tags -> 'bus'::text) = 'yes'::text))"
" -> Seq Scan on ru_psk_point (cost=0.00..1568.94 rows=1 width=136) (actual time=0.016..0.252 rows=13 loops=1)"
" Filter: ((public_transport = 'stop_position'::text) AND ((tags -> 'bus'::text) = 'yes'::text))"
" Rows Removed by Filter: 313"
" -> Bitmap Heap Scan on ru_psk_rels rels (cost=32.82..37.34 rows=1 width=385) (actual time=0.312..0.312 rows=0 loops=13)"
" Recheck Cond: ((ARRAY[opengeo.ru_psk_point.osm_id, opengeo.ru_psk_point.osm_id] <@ parts) AND ('{route,ref}'::text[] <@ tags))"
" Filter: (bus_idx_rels_parts(id, opengeo.ru_psk_point.osm_id) < bus_idx_rels_parts(id, opengeo.ru_psk_point.osm_id))"
" Rows Removed by Filter: 0"
" -> BitmapAnd (cost=32.82..32.82 rows=1 width=0) (actual time=0.090..0.090 rows=0 loops=13)"
" -> Bitmap Index Scan on idx_ru_psk_rels_gin_parts (cost=0.00..12.23 rows=31 width=0) (actual time=0.028..0.028 rows=1 loops=13)"
" Index Cond: (ARRAY[opengeo.ru_psk_point.osm_id, opengeo.ru_psk_point.osm_id] <@ parts)"
" -> Bitmap Index Scan on idx_ru_psk_rels_gin_tags (cost=0.00..20.33 rows=44 width=0) (actual time=0.257..0.257 rows=499 loops=2)"
" Index Cond: ('{route,ref}'::text[] <@ tags)"
"Total runtime: 11.731 ms"
Нужна одна любая строчка с минимальной SUM_DIST. |
|
|
