Подскажите с запросом, данный код по длительности 17 сек , как его исправить что б улучшить время?
У меня есть две точки(координаты), между ними надо найти кратчайший путь и показать км, с ограничительной рамкой, без нее запрос за 2 минуты уходит ожидание.
Код: Выделить всё
with source_tmp as (SELECT source
FROM pgr_2po_4pgr order by st_distance(geom_way, ST_SetSRID(ST_MakePoint(30.386287, 59.805176), 4326)) limit 1),
target_tmp as (SELECT target
FROM pgr_2po_4pgr order by st_distance(geom_way, ST_SetSRID(ST_MakePoint(34.348629, 61.783777), 4326)) limit 1)
SELECT id, node, edge, cost_x as cost, agg_cost, geom_way
FROM pgr_dijkstra(
'SELECT id, source, target, st_length(geom_way, true) as cost FROM pgr_2po_4pgr as r,
(SELECT ST_Expand(ST_Extent(geom_way),0.05) as box
FROM pgr_2po_4pgr as l1
WHERE l1.source = (SELECT source
FROM pgr_2po_4pgr order by st_distance(geom_way, ST_SetSRID(ST_MakePoint(30.386287, 59.805176), 4326)) limit 1)
OR l1.target = (SELECT target
FROM pgr_2po_4pgr order by st_distance(geom_way, ST_SetSRID(ST_MakePoint(34.348629, 61.783777), 4326)) limit 1)) as box
WHERE r.geom_way && box.box',
(SELECT source FROM source_tmp),
(SELECT target FROM target_tmp),
false
) as r INNER JOIN pgr_2po_4pgr as g ON r.edge = g.id;
Код: Выделить всё
---------------+---------------------------+--------------------+-------------------+--------------
id | integer | | not null |
osm_id | bigint | | |
osm_name | character varying | | |
osm_meta | character varying | | |
osm_source_id | bigint | | |
osm_target_id | bigint | | |
clazz | integer | | |
flags | integer | | |
source | integer | | |
target | integer | | |
km | double precision | | |
kmh | integer | | |
cost_x | double precision | | |
reverse_cost | double precision | | |
x1 | double precision | | |
y1 | double precision | | |
x2 | double precision | | |
y2 | double precision | | |
geom_way | geometry(LineString,4326) | | |
Индексы:
"pkey_pgr_2po_4pgr" PRIMARY KEY, btree (id)
"idx_pgr_2po_4pgr_source" btree (source)
"idx_pgr_2po_4pgr_target" btree (target)
"pgr_2po_4pgr_geom_way_idx" gist (geom_way)
"pgr_2po_4pgr_source_idx" btree (source)
"pgr_2po_4pgr_target_idx" btree (target)