ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Что можно сделать для сокращения времени обработки запроса?
69 сообщений из 69, показаны все 3 страниц
  все  
Что можно сделать для сокращения времени обработки запроса?
    #39259400
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
Запрос:

Код: sql
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;



Анализ:

Код: plaintext
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, то время сразу устраивает в отличии от результата:

Код: sql
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



Анализ:

Код: plaintext
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.
...
Рейтинг: 0 / 0
21.06.2016, 10:52
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39259549
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
its_me,

Вы статистику давно пересобирали? Во всех узлах ожидание 1 записи.

SeqScan-ы по `ru_psk_point` с фильтрацией — может индексы сделать?
...
Рейтинг: 0 / 0
21.06.2016, 12:41
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39259627
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
vyegorovits_me,
SeqScan-ы по `ru_psk_point` с фильтрацией — может индексы сделать?

там копейки.
потом он берёт декартов квадрат 181*181 = 32761 (нельзя ли усечь до треугольника), умножает его декартово ещё на табличку -- и начинает фильтровать по функциональным критериям. потом сортирует опять по ф-ии, и берет первое.
ессно, всё это дорого.
...
Рейтинг: 0 / 0
21.06.2016, 14:04
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39259697
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
qwwq,

не совсем копейки, есть смысл индексы сделать, чтобы на секунду ускорить:

Код: sql
1.
"                    ->  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)"
...
Рейтинг: 0 / 0
21.06.2016, 15:04
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39259705
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Alexiusqwwq,

не совсем копейки, есть смысл индексы сделать, чтобы на секунду ускорить:

неа, взять 1 цте, с обеими расстояниями, и квадратить уже его декартом. (а лучше -- таки треуголить, если по логике возможно).
...
Рейтинг: 0 / 0
21.06.2016, 15:09
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39259708
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
А можно поприземлённей немного выдать рекомендацию?)) Я уж точно ничего не декартил - просто написал запрос.))

По поводу индексов ru_psk_point, есть только 2:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
CREATE INDEX ru_psk_point_index
  ON ru_psk_point
  USING gist
  (way);

CREATE INDEX ru_psk_point_pkey
  ON ru_psk_point
  USING btree
  (osm_id);
...
Рейтинг: 0 / 0
21.06.2016, 15:14
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39259787
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
its_me,

декартите,, да ещё и со свистом

Код: sql
1.
2.
3.
4.
(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') t



-- это одна и та же выборка, но с разными расчетными расстояниями.
возьмите её как одно цте. и 2 раза попользуйте.


 
если не боитесь писать рукамидалее -- вы знаете, что расстояния неотрицательны (ну вот так вот оно)
и вы знаете, что сумма неотрицательных упорядочена где--то по слагаемым, с максимумами по диагонали слагаемых.

т.е. можете взять руками цте, и по нему рекурсивно например побежать от минимальных расстояний, проверяя фильтры по--ходу. а не сортируя итоговое произведение.

если рекурсивный запрос для вас сложен -- распишите в plpgsql двумя вложенными циклами. (там надо выскакивать не сразу, а когда одна из координат превзойдет минимальную ранее найденную сумму -- т.е. ограниченный сорт у вас останется)

но это суровый хенджоб тащемто, проще попытаться запинать вдвое, зная как у вас ориентировано это нечто (с какого конца по ид у вас старт, а с какого енд) -- вместо квадрата получите его половину . в 10 раз не выиграете -- но 2--ку наберете. ну и т.п.
...
Рейтинг: 0 / 0
21.06.2016, 17:06
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39259791
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
qwwq,

для начала что такое "цте"?
...
Рейтинг: 0 / 0
21.06.2016, 17:08
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39259795
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
и еще, изначально был вариант через циклы в функции(меньше предыдущего) - дольше, в итоге я у этого запроса сижу.
...
Рейтинг: 0 / 0
21.06.2016, 17:11
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39259974
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
its_meqwwq,

для начала что такое "цте"?
ЦТЕ == CTE == Common Table Expression
Имеют эффект материализации (гарантированно исполняются только один раз).
...
Рейтинг: 0 / 0
21.06.2016, 22:26
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39260477
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
qwwqits_me,

т.е. можете взять руками цте, и по нему рекурсивно например побежать от минимальных расстояний, проверяя фильтры по--ходу. а не сортируя итоговое произведение.


Этот момент можете прояснить, у меня в голове несколько вариантов возникло, но что именно Вы имели ввиду(взять весь мой результат и прорекурсиветь или взять отдельную часть запроса прорекурсиветь и далее ее "соединить" и тп)?
...
Рейтинг: 0 / 0
22.06.2016, 15:42
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39260508
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
its_me,

у вас 181 запись с 2-- мя расстояниями, вы их декартово умножается на себя, расстояния складываются. получается 181*181 вариантов, которые вы фильтруете умножая эти 181*181 ещё на таблицу -- для фильтра. (вместо скажем екзистса)

потом результат сортируете по сумме расстояний, и берете 1 запись вдоль сортировки.

ясно, что вы можете бежать в двойном цикле по 2-м сортированным (по разному) экземплярам (по 191 записи), и найти первое попавшееся под итоговый фильтр.

вернее пробежаться чуть дальше -- до размера любой координаты в квадранте "не больше" первого найденного суммарного расстояния [далее => текущего минимального суммарного расстояния], и там немного посортировать (на самом -- на лету вычислять least), но гораздо меньше чем 181*181.

как--то так.

короче -- ищущий да обрящет.
...
Рейтинг: 0 / 0
22.06.2016, 16:00
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39260524
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
qwwq,

Я только вчера узнал про рекурсивную выборку, а тут такое объяснение, зря спросил))))
...
Рейтинг: 0 / 0
22.06.2016, 16:06
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39260729
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
its_me,

Можете где-то дампы табличек выложить, поиграться?
...
Рейтинг: 0 / 0
22.06.2016, 19:43
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39260885
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
vyegorov,

Честно говоря даже не знаю как подойти к этому вопросу)
...
Рейтинг: 0 / 0
23.06.2016, 11:39
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39261047
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
vyegorov,

есть такая ссылка:
https://www.dropbox.com/s/pbtn1qhgioruqh3/backup_tables.7z?dl=0

пойдёт?
...
Рейтинг: 0 / 0
23.06.2016, 14:56
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39261602
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
qwwqits_me,

у вас 181 запись с 2-- мя расстояниями, вы их декартово умножается на себя, расстояния складываются. получается 181*181 вариантов, которые вы фильтруете умножая эти 181*181 ещё на таблицу -- для фильтра. (вместо скажем екзистса)

потом результат сортируете по сумме расстояний, и берете 1 запись вдоль сортировки.

ясно, что вы можете бежать в двойном цикле по 2-м сортированным (по разному) экземплярам (по 191 записи), и найти первое попавшееся под итоговый фильтр.

вернее пробежаться чуть дальше -- до размера любой координаты в квадранте "не больше" первого найденного суммарного расстояния [далее => текущего минимального суммарного расстояния], и там немного посортировать (на самом -- на лету вычислять least), но гораздо меньше чем 181*181.

как--то так.

короче -- ищущий да обрящет.

Вы тут имеете ввиду через функцию(циклы) или ЦТЕ?
...
Рейтинг: 0 / 0
24.06.2016, 10:48
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39265356
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
qwwq
ясно, что вы можете бежать в двойном цикле по 2-м сортированным (по разному) экземплярам (по 191 записи), и найти первое попавшееся под итоговый фильтр.

вернее пробежаться чуть дальше -- до размера любой координаты в квадранте "не больше" первого найденного суммарного расстояния [далее => текущего минимального суммарного расстояния], и там немного посортировать (на самом -- на лету вычислять least), но гораздо меньше чем 181*181.

Кто-нибудь может перевести это на человеческий язык?
...
Рейтинг: 0 / 0
30.06.2016, 11:58
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39265395
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
its_meqwwqясно, что вы можете бежать в двойном цикле по 2-м сортированным (по разному) экземплярам (по 191 записи), и найти первое попавшееся под итоговый фильтр.

вернее пробежаться чуть дальше -- до размера любой координаты в квадранте "не больше" первого найденного суммарного расстояния [далее => текущего минимального суммарного расстояния], и там немного посортировать (на самом -- на лету вычислять least), но гораздо меньше чем 181*181.

Кто-нибудь может перевести это на человеческий язык?

вы хотели спросить, как это перевести с человеческого на язык пошаговых инструкций "для дебилов" ?

можно и на пошаговый. но лень.
...
Рейтинг: 0 / 0
30.06.2016, 12:34
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39265419
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
маленький экзерсис для мозга:

вот например у вас есть неуникальный индекс
, для простоты по NOT NULL полю
, и вы выбираете вдоль него лимит 10
смотрите план -- постгрес правильно берет лимит вдоль индекса, профетчив всего 10 записей.

далее вы добавляете в свой order by, ПОСЛЕ своего индексированного поля, еще и ключевое поле -- для уникальности [повторяемости] сортировки -- смотрите план, и видите лимит после фетча всего и сортировки всего. и начинаете медленно охеревать с интеллектуальности оптимизатора.

недолго думая вы пишете выборку всего того, что не больше (по вашему полю), чем то, что найдено по предыдущему, неповторимому, условию "лимит 10", и делаете повторимый ордер бай лимит 10 от этой промежуточной выборки -- и берёте с полки пирожок с чувством СВ "глубокого удовлетворения".

вот когда этот экзерсис разберёте руками и поймете -- начнете что--то понимать и про прочую оптимизацию. А заодно -- про оптимизатор ПЖ.
...
Рейтинг: 0 / 0
30.06.2016, 12:50
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39265569
Jonhson
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Jonhson
Участник
qwwq
далее вы добавляете в свой order by, ПОСЛЕ своего индексированного поля, еще и ключевое поле -- для уникальности [повторяемости] сортировки -- смотрите план, и видите лимит после фетча всего и сортировки всего. и начинаете медленно охеревать с интеллектуальности оптимизатора.

добавить в order by в смысле или имеется ввиду подзапрос?
...
Рейтинг: 0 / 0
30.06.2016, 14:54
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39265578
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
qwwqмаленький экзерсис для мозга:

вот например у вас есть неуникальный индекс
, для простоты по NOT NULL полю
, и вы выбираете вдоль него лимит 10
смотрите план -- постгрес правильно берет лимит вдоль индекса, профетчив всего 10 записей.

далее вы добавляете в свой order by, ПОСЛЕ своего индексированного поля, еще и ключевое поле -- для уникальности [повторяемости] сортировки -- смотрите план, и видите лимит после фетча всего и сортировки всего. и начинаете медленно охеревать с интеллектуальности оптимизатора.

недолго думая вы пишете выборку всего того, что не больше (по вашему полю), чем то, что найдено по предыдущему, неповторимому, условию "лимит 10", и делаете повторимый ордер бай лимит 10 от этой промежуточной выборки -- и берёте с полки пирожок с чувством СВ "глубокого удовлетворения".

вот когда этот экзерсис разберёте руками и поймете -- начнете что--то понимать и про прочую оптимизацию. А заодно -- про оптимизатор ПЖ.

Да дело не в объяснении, русских слов мало да ещё и присутствуют биржевые определения. Вообще я до Вашего объяснения сделал так: взял из первой строки(limit 1) первую сумму(быстро отрабатывает) и сделал в выборке не больше или равно этой суммы, а как гарантия того что не попадет самое большое сделал ORDER по dist_start например и время сократилось в 10-15 раз. Может Вы примерно такое имели ввиду.
...
Рейтинг: 0 / 0
30.06.2016, 14:59
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39265660
Jonhson
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Jonhson
Участник
qwwqвот например у вас есть неуникальный индекс
, для простоты по NOT NULL полю
, и вы выбираете вдоль него лимит 10
смотрите план -- постгрес правильно берет лимит вдоль индекса, профетчив всего 10 записей..


вот что получилось
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
create table t12 (id int, var varchar(10));
CREATE TABLE
db1=# create index t12_ind on t12(id);
CREATE INDEX
db1=# insert into t12 select id, 'rrr'||id from (select generate_series(1, 100000) as id) t;
INSERT 0 100000
db1=# commit;
COMMIT
db1=# explain analyze select id from t12 limit 10;
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.15 rows=10 width=4) (actual time=0.010..0.013 rows=10 loops=1)
   ->  Seq Scan on t12  (cost=0.00..1541.00 rows=100000 width=4) (actual time=0.006..0.007 rows=10 loops=1)
 Planning time: 0.392 ms
 Execution time: 0.036 ms




т.е. таки по таблице скан, если я правильно понимаю план
...
Рейтинг: 0 / 0
30.06.2016, 16:04
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39265708
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Jonhson,

 
data
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
DROP SCHEMA IF EXISTS test CASCADE;

CREATE SCHEMA test   AUTHORIZATION postgres;


CREATE TABLE test.t (id int primary key,  f varchar(15) not null);
create index ON test.t (f);
INSERT INTO test.t (id,f) SELECT n , (n%1000)::text||'f' FROM generate_series(1, 200000) n;
ANALYZE test.t;



test:
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
EXPLAIN (ANALYZE on, VERBOSE on, COSTS on, BUFFERS off, TIMING off )
SELECT id, f FROM test.t ORDER BY f LIMIT 10;
------
'Limit  (cost=0.42..0.92 rows=10 width=8) (actual rows=10 loops=1)'
'  Output: id, f'
'  ->  Index Scan using t_f_idx on test.t  (cost=0.42..9988.23 rows=200000 width=8) (actual rows=10 loops=1)'
'        Output: id, f'
'Planning time: 0.726 ms'
'Execution time: 0.080 ms'




 
2
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
EXPLAIN (ANALYZE on, VERBOSE on, COSTS on, BUFFERS off, TIMING off )
SELECT id, f FROM test.t ORDER BY f,id LIMIT 10;
-------------
'Limit  (cost=7382.93..7382.95 rows=10 width=8) (actual rows=10 loops=1)'
'  Output: id, f'
'  ->  Sort  (cost=7382.93..7882.93 rows=200000 width=8) (actual rows=10 loops=1)'
'        Output: id, f'
'        Sort Key: t.f, t.id'
'        Sort Method: top-N heapsort  Memory: 17kB'
'        ->  Seq Scan on test.t  (cost=0.00..3061.00 rows=200000 width=8) (actual rows=200000 loops=1)'
'              Output: id, f'
'Planning time: 0.300 ms'
'Execution time: 89.230 ms'




 
handjob
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
EXPLAIN (ANALYZE on, VERBOSE on, COSTS on, BUFFERS off, TIMING off )

SELECT id, f FROM test.t
WHERE f<=(SELECT max(f) FROM (SELECT f FROM test.t ORDER BY f LIMIT 10) foo )
 ORDER BY f,id LIMIT 10;
----------------
'Limit  (cost=4769.13..4769.16 rows=10 width=8) (actual rows=10 loops=1)'
'  Output: t.id, t.f'
'  InitPlan 1 (returns $0)'
'    ->  Aggregate  (cost=1.04..1.05 rows=1 width=4) (actual rows=1 loops=1)'
'          Output: max((t_1.f)::text)'
'          ->  Limit  (cost=0.42..0.92 rows=10 width=4) (actual rows=10 loops=1)'
'                Output: t_1.f'
'                ->  Index Only Scan using t_f_idx on test.t t_1  (cost=0.42..9988.23 rows=200000 width=4) (actual rows=10 loops=1)'
'                      Output: t_1.f'
'                      Heap Fetches: 10'
'  ->  Sort  (cost=4768.08..4934.74 rows=66667 width=8) (actual rows=10 loops=1)'
'        Output: t.id, t.f'
'        Sort Key: t.f, t.id'
'        Sort Method: top-N heapsort  Memory: 17kB'
'        ->  Bitmap Heap Scan on test.t  (cost=1433.09..3327.43 rows=66667 width=8) (actual rows=200 loops=1)'
'              Output: t.id, t.f'
'              Recheck Cond: ((t.f)::text <= $0)'
'              Heap Blocks: exact=200'
'              ->  Bitmap Index Scan on t_f_idx  (cost=0.00..1416.42 rows=66667 width=0) (actual rows=200 loops=1)'
'                    Index Cond: ((t.f)::text <= $0)'
'Planning time: 0.365 ms'
'Execution time: 0.968 ms'



особенно это приятно, когда какой--нито битмап скан вместо индекскана превращает лимит проходной по мердж джойну партиций в лимит после фулл апенда + сорта.
...
Рейтинг: 0 / 0
30.06.2016, 16:30
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39265806
Jonhson
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Jonhson
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
explain analyze select id from t12 order by id limit 10;
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.29..0.61 rows=10 width=4) (actual time=0.046..0.051 rows=10 loops=1)
   ->  Index Only Scan using t12_ind on t12  (cost=0.29..3148.29 rows=100000 width=4) (actual time=0.045..0.049 rows=10 loops=1)
         Heap Fetches: 10
 Planning time: 0.143 ms
 Execution time: 0.074 ms



когда сделал сорт (в моём тесте) , пошло по индексу. Правда что раньше мешало? ;)

а в вашем тесте не понял

Код: sql
1.
Index Scan using t_f_idx on test.t



если вы берёте select id, f , при этом id нет в индексе по f
...
Рейтинг: 0 / 0
30.06.2016, 17:46
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39265811
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Jonhson <>
а в вашем тесте не понял
<>
а как дысал, как дысал !
...
Рейтинг: 0 / 0
30.06.2016, 17:51
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39265813
Jonhson
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Jonhson
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
explain (analyze on, verbose on, costs on, buffers on, timing on)
select id, var from t12 order by id limit 10;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.29..0.61 rows=10 width=12) (actual time=0.018..0.023 rows=10 loops=1)
   Output: id, var
   Buffers: shared hit=3
   ->  Index Scan using t12_ind on public.t12  (cost=0.29..3148.29 rows=100000 width=12) (actual time=0.018..0.022 rows=10 loops=1)
         Output: id, var
         Buffers: shared hit=3
 Planning time: 0.097 ms
 Execution time: 0.040 ms



чудеса! Берёт то, чего в индексе нет.

Судя по всему шаг table access by rowid план просто не пишет ( а он явно есть)
...
Рейтинг: 0 / 0
30.06.2016, 17:53
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39265815
Jonhson
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Jonhson
Участник
п.с. прекратите уже паясничать. Не два по третьему.
...
Рейтинг: 0 / 0
30.06.2016, 17:54
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39265828
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
А мне это поможет?)
...
Рейтинг: 0 / 0
30.06.2016, 18:02
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39265834
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
its_meА мне это поможет?)зависит от вас.

просто демонстрируется факт, что для нахождения некоторых первых записей не надо строить весь набор, сортировать и выбирать первые.

если вы знаете, как заранее можно обрезать выборку -- это может помочь.

в вашем случае индексов нет, но есть другой способ обхода множества из 181*181 записей, думается. Если конечно фильтр, как назло, не подавляет всё в начале и пропускает только в конце перебора. (т.е. не коррелирует с обходом).
...
Рейтинг: 0 / 0
30.06.2016, 18:09
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39265838
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Jonhsonп.с. прекратите уже паясничать.
свободен,
...
Рейтинг: 0 / 0
30.06.2016, 18:10
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39265855
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
JonhsonСудя по всему шаг table access by rowid план просто не пишет ( а он явно есть)
Вам неоднократно говорили — прочитайте уже, наконец, документацию.
И не ждите ORACLE-их фич, это другая СУБД.
...
Рейтинг: 0 / 0
30.06.2016, 18:34
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266050
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
На чём я остановился пока:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
with bus_points AS (
  select osm_id, way from opengeo.ru_psk_point where public_transport='stop_position' and tags -> 'bus' = 'yes'
),
various_sum AS (
 select * from (
  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 bus_points order by dist_start) 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 bus_points order by dist_end) 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 10
 ) t order by sum_dist limit 1 
)
select 
id_start,start_way,dist_start,id_end,end_way,dist_end
,(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 bus_points order by dist_start) 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 bus_points order by dist_end) 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
and (dist_start+dist_end)<=(select sum_dist from various_sum)
order by sum_dist limit 1;



Анализ:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
"Limit  (cost=572.46..572.47 rows=1 width=96) (actual time=534.149..534.155 rows=1 loops=1)"
"  CTE bus_points"
"    ->  Bitmap Heap Scan on ru_psk_point  (cost=5.67..496.49 rows=1 width=136) (actual time=0.178..2.257 rows=181 loops=1)"
"          Recheck Cond: (public_transport = 'stop_position'::text)"
"          Filter: ((tags -> 'bus'::text) = 'yes'::text)"
"          Rows Removed by Filter: 5"
"          ->  Bitmap Index Scan on pt_only  (cost=0.00..5.67 rows=186 width=0) (actual time=0.102..0.102 rows=186 loops=1)"
"                Index Cond: (public_transport = 'stop_position'::text)"
"  CTE various_sum"
"    ->  Limit  (cost=37.98..37.98 rows=1 width=8) (actual time=48.827..48.832 rows=1 loops=1)"
"          ->  Sort  (cost=37.98..37.98 rows=1 width=8) (actual time=48.814..48.814 rows=1 loops=1)"
"                Sort Key: (((st_distance_spheroid(st_transform(bus_points.way, 4326), '0101000020E6100000B311E39068473C40BB568C0464E84C40'::geometry, 'SPHEROID("WGS 84",6378137,298.257223562997)'::spheroid)) + (st_distance_spheroid(st_transform(bus_points.way, 4326), '0101000020E61000000B24287E8C4B3C403D450E1137E84C40'::geometry, 'SPHEROID("WGS 84",6378137,298.257223562997)'::spheroid))))"
"                Sort Method: top-N heapsort  Memory: 17kB"
"                ->  Limit  (cost=33.39..37.96 rows=1 width=16) (actual time=20.580..48.717 rows=10 loops=1)"
"                      ->  Nested Loop  (cost=33.39..37.96 rows=1 width=16) (actual time=20.566..48.562 rows=10 loops=1)"
"                            ->  Nested Loop  (cost=0.57..0.61 rows=1 width=32) (actual time=12.754..16.386 rows=182 loops=1)"
"                                  ->  Sort  (cost=0.28..0.29 rows=1 width=40) (actual time=6.332..6.338 rows=2 loops=1)"
"                                        Sort Key: (st_distance_spheroid(st_transform(bus_points.way, 4326), '0101000020E6100000B311E39068473C40BB568C0464E84C40'::geometry, 'SPHEROID("WGS 84",6378137,298.257223562997)'::spheroid))"
"                                        Sort Method: quicksort  Memory: 29kB"
"                                        ->  CTE Scan on bus_points  (cost=0.00..0.27 rows=1 width=40) (actual time=1.538..4.760 rows=181 loops=1)"
"                                  ->  Sort  (cost=0.28..0.29 rows=1 width=40) (actual time=3.204..3.800 rows=91 loops=2)"
"                                        Sort Key: (st_distance_spheroid(st_transform(bus_points.way, 4326), '0101000020E61000000B24287E8C4B3C403D450E1137E84C40'::geometry, 'SPHEROID("WGS 84",6378137,298.257223562997)'::spheroid))"
"                                        Sort Method: quicksort  Memory: 29kB"
"                                        ->  CTE Scan on bus_points  (cost=0.00..0.27 rows=1 width=40) (actual time=1.679..4.844 rows=181 loops=1)"
"                            ->  Bitmap Heap Scan on ru_psk_rels rels  (cost=32.82..37.34 rows=1 width=86) (actual time=0.157..0.159 rows=0 loops=182)"
"                                  Recheck Cond: ((ARRAY[bus_points.osm_id, bus_points.osm_id] <@ parts) AND ('{route,ref}'::text[] <@ tags))"
"                                  Filter: (bus_idx_rels_parts(id, bus_points.osm_id) < bus_idx_rels_parts(id, bus_points.osm_id))"
"                                  Rows Removed by Filter: 0"
"                                  ->  BitmapAnd  (cost=32.82..32.82 rows=1 width=0) (actual time=0.108..0.108 rows=0 loops=182)"
"                                        ->  Bitmap Index Scan on idx_ru_psk_rels_gin_parts  (cost=0.00..12.23 rows=31 width=0) (actual time=0.020..0.020 rows=1 loops=182)"
"                                              Index Cond: (ARRAY[bus_points.osm_id, bus_points.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.210..0.210 rows=499 loops=60)"
"                                              Index Cond: ('{route,ref}'::text[] <@ tags)"
"  InitPlan 3 (returns $4)"
"    ->  CTE Scan on various_sum  (cost=0.00..0.02 rows=1 width=8) (actual time=48.841..48.859 rows=1 loops=1)"
"  ->  Sort  (cost=37.97..37.98 rows=1 width=96) (actual time=534.123..534.123 rows=1 loops=1)"
"        Sort Key: (((st_distance_spheroid(st_transform(bus_points.way, 4326), '0101000020E6100000B311E39068473C40BB568C0464E84C40'::geometry, 'SPHEROID("WGS 84",6378137,298.257223562997)'::spheroid)) + (st_distance_spheroid(st_transform(bus_points.way, 4326), '0101000020E61000000B24287E8C4B3C403D450E1137E84C40'::geometry, 'SPHEROID("WGS 84",6378137,298.257223562997)'::spheroid))))"
"        Sort Method: top-N heapsort  Memory: 17kB"
"        ->  Nested Loop  (cost=33.39..37.96 rows=1 width=96) (actual time=71.832..534.041 rows=7 loops=1)"
"              ->  Nested Loop  (cost=0.57..0.61 rows=1 width=96) (actual time=66.092..530.417 rows=5 loops=1)"
"                    Join Filter: (((st_distance_spheroid(st_transform(bus_points.way, 4326), '0101000020E6100000B311E39068473C40BB568C0464E84C40'::geometry, 'SPHEROID("WGS 84",6378137,298.257223562997)'::spheroid)) + (st_distance_spheroid(st_transform(bus_points.way, 4326), '0101000020E61000000B24287E8C4B3C403D450E1137E84C40'::geometry, 'SPHEROID("WGS 84",6378137,298.257223562997)'::spheroid))) <= $4)"
"                    Rows Removed by Join Filter: 32756"
"                    ->  Sort  (cost=0.28..0.29 rows=1 width=40) (actual time=10.708..12.019 rows=181 loops=1)"
"                          Sort Key: (st_distance_spheroid(st_transform(bus_points.way, 4326), '0101000020E6100000B311E39068473C40BB568C0464E84C40'::geometry, 'SPHEROID("WGS 84",6378137,298.257223562997)'::spheroid))"
"                          Sort Method: quicksort  Memory: 29kB"
"                          ->  CTE Scan on bus_points  (cost=0.00..0.27 rows=1 width=40) (actual time=2.028..8.981 rows=181 loops=1)"
"                    ->  Sort  (cost=0.28..0.29 rows=1 width=40) (actual time=0.042..1.267 rows=181 loops=181)"
"                          Sort Key: (st_distance_spheroid(st_transform(bus_points.way, 4326), '0101000020E61000000B24287E8C4B3C403D450E1137E84C40'::geometry, 'SPHEROID("WGS 84",6378137,298.257223562997)'::spheroid))"
"                          Sort Method: quicksort  Memory: 29kB"
"                          ->  CTE Scan on bus_points  (cost=0.00..0.27 rows=1 width=40) (actual time=1.746..4.898 rows=181 loops=1)"
"              ->  Bitmap Heap Scan on ru_psk_rels rels  (cost=32.82..37.34 rows=1 width=86) (actual time=0.614..0.681 rows=1 loops=5)"
"                    Recheck Cond: ((ARRAY[bus_points.osm_id, bus_points.osm_id] <@ parts) AND ('{route,ref}'::text[] <@ tags))"
"                    Filter: (bus_idx_rels_parts(id, bus_points.osm_id) < bus_idx_rels_parts(id, bus_points.osm_id))"
"                    Rows Removed by Filter: 1"
"                    ->  BitmapAnd  (cost=32.82..32.82 rows=1 width=0) (actual time=0.188..0.188 rows=0 loops=5)"
"                          ->  Bitmap Index Scan on idx_ru_psk_rels_gin_parts  (cost=0.00..12.23 rows=31 width=0) (actual time=0.027..0.027 rows=2 loops=5)"
"                                Index Cond: (ARRAY[bus_points.osm_id, bus_points.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.232..0.232 rows=499 loops=3)"
"                                Index Cond: ('{route,ref}'::text[] <@ tags)"
"Total runtime: 534.409 ms"


Результат есть, но меня пугает то, что записи в таблицах, таких как ru_psk_point, ru_psk_rels, будут пополняться и значительно(в разы чем сейчас).
...
Рейтинг: 0 / 0
01.07.2016, 09:22
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266119
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
its_me,
[thy. yt rjvvtynbhe.


PS
однако вот такое вот вам не приходило в голову :

Код: sql
1.
SELECT array[1,1] <@ array [1,2,3]


т.е. если вы не можете усечь перебор до треугольника -- м.б. хотя бы диагональ явно выколоть, а не неизвестно сколько исполняемой ф--ей ?

да ,если выложите в свободное место свои таблички, -- м.б. кто--то не поленится их покрутить. а так -- нет.
...
Рейтинг: 0 / 0
01.07.2016, 10:44
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266147
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
qwwq,

Я херню комментирую).
Про массивы пока не дошло.
Про выкладывания - я ссылку кидал уже выше:
https://www.dropbox.com/s/pbtn1qhgioruqh3/backup_tables.7z?dl=0
...
Рейтинг: 0 / 0
01.07.2016, 11:02
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266162
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
ещё:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
CREATE OR REPLACE FUNCTION bus_idx_rels_parts(IN bigint, IN bigint, OUT idx integer)
RETURNS integer AS
' SELECT idx from opengeo.ru_psk_rels_parts_idx where id=$1 and unnest=$2 '
LANGUAGE sql VOLATILE
COST 100;

CREATE INDEX ru_psk_prels_parts_idx_id
  ON ru_psk_rels_parts_idx
  USING btree
  (id, unnest);

select id,parts,unnest(parts)::varchar as unnest, generate_subscripts(parts,1) as idx INTO ru_psk_rels_parts_idx from ru_psk_rels
...
Рейтинг: 0 / 0
01.07.2016, 11:11
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266205
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
its_meqwwq,

Я херню комментирую).
не льстите себе, мсье. вы её пишете

its_meПро массивы пока не дошло. пичаль. на хабре или их гите щас хорошая статья про микроцефалию новых поколений.
its_meПро выкладывания - я ссылку кидал уже выше:
https://www.dropbox.com/s/pbtn1qhgioruqh3/backup_tables.7z?dl=0 это говно настойчиво хочет от меня логина. приходится им писать в поле логина "кто вы такие, чего вам надо, ..." далее -- по известному мему.
...
Рейтинг: 0 / 0
01.07.2016, 11:41
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266211
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
its_meещё:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
CREATE OR REPLACE FUNCTION bus_idx_rels_parts(IN bigint, IN bigint, OUT idx integer)
RETURNS integer AS
' SELECT idx from opengeo.ru_psk_rels_parts_idx where id=$1 and unnest=$2 '
LANGUAGE sql VOLATILE
COST 100;

CREATE INDEX ru_psk_prels_parts_idx_id
  ON ru_psk_rels_parts_idx
  USING btree
  (id, unnest);

select id,parts,unnest(parts)::varchar as unnest, generate_subscripts(parts,1) as idx INTO ru_psk_rels_parts_idx from ru_psk_rels


это не оракел. не надо подменять sql ф--ями.
хотя оно у вас секъюрити инвокер, должно встраиваться в план как подзапрос.
могли еще стейблом сделать.
...
Рейтинг: 0 / 0
01.07.2016, 11:44
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266233
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
qwwq,

Скачать можно, если в окне регистрации нажать ссылгу "Мне пофиг" (внизу), вверху-справа нажать "Скачать".
...
Рейтинг: 0 / 0
01.07.2016, 12:01
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266234
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
qwwq,

Мне трудно говорить с человеком который говорит сам с собой...
Раньше больше/преимущественно ораклом занимался.
Попробуйте эту ссылку(редко требуется передавать файлы по ссылкам):
http://filecloud.me/s53lmqzwgtaa.html
...
Рейтинг: 0 / 0
01.07.2016, 12:01
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266304
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
its_me,

У вас в запросе функция, которой нет в дампе: bus_idx_rels_parts
...
Рейтинг: 0 / 0
01.07.2016, 12:55
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266326
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
its_meqwwq,

Мне трудно говорить с человеком который говорит сам с собой...
если вы чего--то не поняли, это не проблема собеседника.


хорошая для вас новость -- рекурсивным запросом эффективно резать не удастся.
по крайней мере -- навскидку. (чтобы добегать только до фильтра в каждой итерации надо отдельно сортировать и фильтровать -- идексов то нет)

видимо надо попытаться plpgsql вложенным лупом по 2--м сортированным наборам с выходами по условию выхода из диапазонов. -- должно сработать за 1 проход.

иначе -- это просто многочисленная сортировка с фильтрацией на каждой итерации:
//это должно было быть понятно сразу. проглядел.
...
Рейтинг: 0 / 0
01.07.2016, 13:18
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266368
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
vyegorovits_me,

У вас в запросе функция, которой нет в дампе: bus_idx_rels_parts

если на неё наплевать -- то примерно так :

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
DO
$$
DECLARE 
	_t1 record;
	_t2	record;
	ok boolean;
	sum_dist double precision;
	sum_dist_min double precision;
	a_id_start	bigint[];
	a_id_end	bigint[];
	a_sum_dist double precision[];
	a_dist_start double precision[];
	a_dist_end double precision[];
	_result record;
BEGIN
	<<_t1_>>
	FOR _t1 IN
		(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 (
				select osm_id, way from opengeo.ru_psk_point where public_transport='stop_position' and tags -> 'bus' = 'yes'
			)bus_points  
		ORDER BY dist_start ) 
	LOOP
		<<_t2_>>
		FOR _t2 IN 
			(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 (
					select osm_id, way from opengeo.ru_psk_point where public_transport='stop_position' and tags -> 'bus' = 'yes'
					AND osm_id<> _t1.id_start -- ?
				)bus_points
				
			ORDER BY dist_end ) 
		LOOP 
			ok:= EXISTS (SELECT 1 FROM opengeo.ru_psk_rels rels
						WHERE
							array[_t1.id_start,_t2.id_end]<@rels.parts 
							and array['route','ref']<@rels.tags );
							
			sum_dist:= CASE WHEN ok THEN _t1.dist_start + _t2.dist_end END;
			sum_dist_min:=LEAST(sum_dist_min,sum_dist);
			IF ok THEN
				a_id_start	:= a_id_start	||	_t1.id_start;
				a_id_end	:=a_id_end		||	_t2.id_end;
				a_sum_dist	:=a_sum_dist	||	sum_dist;

				a_dist_start	:=a_dist_start	|| _t1.dist_start;
				a_dist_end 		:=a_dist_end	|| _t2.dist_end;
			END IF;
			

			IF _t2.dist_end >=sum_dist_min THEN
				exit _t2_;
			END IF;

			IF _t1.dist_start >=sum_dist_min THEN
				exit _t1_;
			END IF;
		END LOOP;
	END LOOP;
	
	SELECT * FROM (
	SELECT	unnest(a_id_start) AS id_start
			,unnest(a_id_end) AS id_end
			,unnest(a_sum_dist) AS sum_dist
			,unnest(a_dist_start) AS dist_start
			,unnest(a_dist_end) AS dist_end
	) foo ORDER BY sum_dist LIMIT 1
	INTO _result;

	raise NOTICE '%' , _result;
END;
$$;

...
Рейтинг: 0 / 0
01.07.2016, 13:52
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266475
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
qwwqесли на неё наплевать -- то примерно та:
Чёт не выходит у меня этот цветок :)

Меня напрягает, что нужно сортировать явным образом `ru_psk_point`, без индексов. Если, как было сказано, оно ещё и вырастет, то тут подход неправильный.

Думаю, что в `ru_psk_rels` хранятся маршруты, `ru_psk_rels.parts` -- остановки.
В `ru_psk_point` -- какие-то моменты в рамках маршрута. Может через kNN искать ближайшую остановку из общего списка для каждого момента , а потом уже накладывать эти точки на маршруты? Ну чтобы индексы задействовать...
...
Рейтинг: 0 / 0
01.07.2016, 15:05
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266490
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
vyegorovits_me,

У вас в запросе функция, которой нет в дампе: bus_idx_rels_parts

Я её выше написал отдельно.
...
Рейтинг: 0 / 0
01.07.2016, 15:14
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266496
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
vyegorov
Меня напрягает, что нужно сортировать явным образом `ru_psk_point`, без индексов. Если, как было сказано, оно ещё и вырастет, то тут подход неправильный.

Думаю, что в `ru_psk_rels` хранятся маршруты, `ru_psk_rels.parts` -- остановки.
В `ru_psk_point` -- какие-то моменты в рамках маршрута. Может через kNN искать ближайшую остановку из общего списка для каждого момента , а потом уже накладывать эти точки на маршруты? Ну чтобы индексы задействовать...

ru_psk_rels - маршруты(общее - отношения)
ru_psk_rels.parts - объекты отношения(в тч остановки из ru_psk_point.id)
ru_psk_point - остановки(точки)

Про метод задействовать индексы непонял, что за каждый момент?
...
Рейтинг: 0 / 0
01.07.2016, 15:19
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266502
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
qwwq
видимо надо попытаться plpgsql вложенным лупом по 2--м сортированным наборам с выходами по условию выхода из диапазонов. -- должно сработать за 1 проход.

Вы же можете писать как пример скрипты тут и даже не маленькие, как хорошо бы эту строчку увидеть в "скриптовом" виде)). Вы же вкурсе как до меня доходят Ваши советы)).
...
Рейтинг: 0 / 0
01.07.2016, 15:23
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266512
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
its_meЯ её выше написал отдельно.
Я не понимаю что там:
- создаётся функция
- потом индекс на несуществующей реляции
- потом создаётся таблица с названием `ru_psk_rels_parts_idx` в которой вы раскрываете список точек в маршруте (если мои предположения верны).

Опишите, что вы делаете формально, будет проще экспериментировать.
...
Рейтинг: 0 / 0
01.07.2016, 15:31
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266531
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
vyegorovits_meЯ её выше написал отдельно.
Я не понимаю что там:
- создаётся функция
- потом индекс на несуществующей реляции
- потом создаётся таблица с названием `ru_psk_rels_parts_idx` в которой вы раскрываете список точек в маршруте (если мои предположения верны).

Опишите, что вы делаете формально, будет проще экспериментировать.

Порядок только у меня перепутан)).

Создаю таблицу для извлечения информации по индексам в массиве(parts) объектов отношения.
Далее индекс для этой таблицы.
Далее функцию которая из этой таблицы возвращает индекс(нужна в запросе для фильтра порядка расположения остановок).

Главная проблема/причина у меня была в извлечении индексов объектов массива - версия постгреса 9.2(нету array_position), intarray не включить(ругается osm2pgsql), есть generate(он используется у меня в следующем запросе и он не самый быстрый тоже) - других я не нашел методов возврата индекса.
...
Рейтинг: 0 / 0
01.07.2016, 15:48
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266558
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
its_me,

что храниться в `ru_psk_point.way` -- точка? ломанная? замкнутый контур?

координаты откуда беруться? и что это -- фиксация каких-то мест?

вы считаете дистанцию от заданных координат и потом отбираете минимальныую сумму. Вы хотите найти `way`-ы, ближайшие к заданным координатам? Расстояние между координатами? Расстояния между `way`-ми? -- уточните!

если `ru_psk_point.way` используется в географических выражениях, почему не использовать тип geography?

зачем хранить точки `ru_psk_rels.parts` в виде массива и мучаться с индексами, может ввести подчинённую таблицу?
...
Рейтинг: 0 / 0
01.07.2016, 16:09
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266571
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
vyegorovits_me,

что храниться в `ru_psk_point.way` -- точка? ломанная? замкнутый контур?

координаты откуда беруться? и что это -- фиксация каких-то мест?

вы считаете дистанцию от заданных координат и потом отбираете минимальныую сумму. Вы хотите найти `way`-ы, ближайшие к заданным координатам? Расстояние между координатами? Расстояния между `way`-ми? -- уточните!

если `ru_psk_point.way` используется в географических выражениях, почему не использовать тип geography?

зачем хранить точки `ru_psk_rels.parts` в виде массива и мучаться с индексами, может ввести подчинённую таблицу?


1. Точка(её координаты)
2. Координаты - это входящая переменная(в функции где этот запрос).
3. Минимальную сумму расстояний от стартовой входящей координаты до посадочной остановки и от конечной координаты до остановки для выхода. Далее беру id остановок(для дальнейшей обработки - списка маршрутов) и way-и остановок(для геометрической проверки принадлежности к линии маршрута).
4. Не знаю, просто стоит тип geometry, не думал вообще над этим и зачем это.
5. В таком виде предоставляет данные программа-импорт(osm2pgsql), в принципе я уже создал таблицу для извлечения индексов.
...
Рейтинг: 0 / 0
01.07.2016, 16:24
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266609
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
its_me,

удф по всему у вас мухи копулируют в голове и коде.

он должен выглядеть как--то так:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
select id,parts,unnest(parts) as unnest, generate_subscripts(parts,1) as idx INTO opengeo.ru_psk_rels_parts_idx from opengeo.ru_psk_rels;

CREATE INDEX ru_psk_prels_parts_idx_id
  ON opengeo.ru_psk_rels_parts_idx
  USING btree
  (id, unnest);

CREATE OR REPLACE FUNCTION opengeo.bus_idx_rels_parts(IN bigint, IN bigint, OUT idx integer)
RETURNS integer AS
'SELECT idx from opengeo.ru_psk_rels_parts_idx where id=$1 and unnest=$2 '
LANGUAGE sql VOLATILE
COST 100;




при этом на прогретых данных имеем:
 

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
DO
$$
DECLARE 
	_t1 record;
	_t2	record;
	ok boolean;
	sum_dist double precision;
	sum_dist_min double precision;
	a_id_start	bigint[];
	a_id_end	bigint[];
	a_sum_dist double precision[];
	a_dist_start double precision[];
	a_dist_end double precision[];
	_result record;
BEGIN
	<<_t1_>>
	FOR _t1 IN
		(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 (
				select osm_id, way from opengeo.ru_psk_point where public_transport='stop_position' and tags -> 'bus' = 'yes'
			)bus_points  
		ORDER BY dist_start ) 
	LOOP
		<<_t2_>>
		FOR _t2 IN 
			(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 (
					select osm_id, way from opengeo.ru_psk_point where public_transport='stop_position' and tags -> 'bus' = 'yes'
					AND osm_id<> _t1.id_start -- ?
				)bus_points
				
			ORDER BY dist_end ) 
		LOOP 
			ok:= EXISTS (SELECT 1 FROM opengeo.ru_psk_rels rels
						WHERE
							array[_t1.id_start,_t2.id_end]<@rels.parts 
							and array['route','ref']<@rels.tags 

							and opengeo.bus_idx_rels_parts(rels.id,_t1.id_start)<opengeo.bus_idx_rels_parts(rels.id,_t2.id_end)
				)
				;
							
			sum_dist:= CASE WHEN ok THEN _t1.dist_start + _t2.dist_end END;
			sum_dist_min:=LEAST(sum_dist_min,sum_dist);
			IF ok THEN
				a_id_start	:= a_id_start	||	_t1.id_start;
				a_id_end	:=a_id_end		||	_t2.id_end;
				a_sum_dist	:=a_sum_dist	||	sum_dist;

				a_dist_start	:=a_dist_start	|| _t1.dist_start;
				a_dist_end 		:=a_dist_end	|| _t2.dist_end;
			END IF;
			

			IF _t2.dist_end >=sum_dist_min THEN
				exit _t2_;
			END IF;

			IF _t1.dist_start >=sum_dist_min THEN
				exit _t1_;
			END IF;
		END LOOP;
	END LOOP;
	
	SELECT * FROM (
	SELECT	unnest(a_id_start) AS id_start
			,unnest(a_id_end) AS id_end
			,unnest(a_sum_dist) AS sum_dist
			,unnest(a_dist_start) AS dist_start
			,unnest(a_dist_end) AS dist_end
	) foo ORDER BY sum_dist LIMIT 1
	INTO _result;

	raise NOTICE '%' , _result;
END;
$$;
-----------------------------------
NOTICE:  (2314760557,2314760553,139.332169287831,105.974855791139,33.357313496692)

Запрос успешно выполнен без возвращаемых данных за 40 мс.




ели я правильно понял про ваш unnest .
...
Рейтинг: 0 / 0
01.07.2016, 17:11
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266617
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
qwwq,

и тогда без лишних структур:

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
DO
$$
DECLARE 
	_t1 record;
	_t2	record;
	ok boolean;
	sum_dist double precision;
	sum_dist_min double precision;
	a_id_start	bigint[];
	a_id_end	bigint[];
	a_sum_dist double precision[];
	a_dist_start double precision[];
	a_dist_end double precision[];
	_result record;
BEGIN
	<<_t1_>>
	FOR _t1 IN
		(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 (
				select osm_id, way from opengeo.ru_psk_point where public_transport='stop_position' and tags -> 'bus' = 'yes'
			)bus_points  
		ORDER BY dist_start ) 
	LOOP
		<<_t2_>>
		FOR _t2 IN 
			(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 (
					select osm_id, way from opengeo.ru_psk_point where public_transport='stop_position' and tags -> 'bus' = 'yes'
					AND osm_id<> _t1.id_start -- ?
				)bus_points
				
			ORDER BY dist_end ) 
		LOOP 
			ok:= EXISTS (SELECT 1 FROM opengeo.ru_psk_rels rels
						WHERE
							array[_t1.id_start,_t2.id_end]<@ rels.parts 
							and array['route','ref']<@ rels.tags
							--and opengeo.bus_idx_rels_parts(rels.id,_t1.id_start)<opengeo.bus_idx_rels_parts(rels.id,_t2.id_end)
							
							--AND array_position( rels.parts , _t1.id_start) < array_position( rels.parts , _t2.id_end) --9.5
							--/* --9.3:
							AND (
								(SELECT rn  FROM (SELECT unnest,row_number() over() AS rn FROM unnest(rels.parts)  ) foo WHERE unnest = _t1.id_start ORDER BY rn limit 1)
								<
								(SELECT rn  FROM (SELECT unnest,row_number() over() AS rn FROM unnest(rels.parts)  ) foo WHERE unnest = _t2.id_end ORDER BY rn desc limit 1 )
								)
							--*/
						);
				
							
			sum_dist:= CASE WHEN ok THEN _t1.dist_start + _t2.dist_end END;
			sum_dist_min:=LEAST(sum_dist_min,sum_dist);
			IF ok THEN
				a_id_start	:= a_id_start	||	_t1.id_start;
				a_id_end	:=a_id_end		||	_t2.id_end;
				a_sum_dist	:=a_sum_dist	||	sum_dist;

				a_dist_start	:=a_dist_start	|| _t1.dist_start;
				a_dist_end 		:=a_dist_end	|| _t2.dist_end;
			END IF;
			

			IF _t2.dist_end >=sum_dist_min THEN
				exit _t2_;
			END IF;

			IF _t1.dist_start >=sum_dist_min THEN
				exit _t1_;
			END IF;
		END LOOP;
	END LOOP;
	
	SELECT * FROM (
	SELECT	unnest(a_id_start) AS id_start
			,unnest(a_id_end) AS id_end
			,unnest(a_sum_dist) AS sum_dist
			,unnest(a_dist_start) AS dist_start
			,unnest(a_dist_end) AS dist_end
	) foo ORDER BY sum_dist LIMIT 1
	INTO _result;

	raise NOTICE '%' , _result;
END;
$$;

-------------------------------------
NOTICE:  (2314760557,2314760553,139.332169287831,105.974855791139,33.357313496692)

Запрос успешно выполнен без возвращаемых данных за 36 мс.

...
Рейтинг: 0 / 0
01.07.2016, 17:24
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266618
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
qwwqits_me,

удф по всему у вас мухи копулируют в голове и коде.

он должен выглядеть как--то так:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
select id,parts,unnest(parts) as unnest, generate_subscripts(parts,1) as idx INTO opengeo.ru_psk_rels_parts_idx from opengeo.ru_psk_rels;

CREATE INDEX ru_psk_prels_parts_idx_id
  ON opengeo.ru_psk_rels_parts_idx
  USING btree
  (id, unnest);

CREATE OR REPLACE FUNCTION opengeo.bus_idx_rels_parts(IN bigint, IN bigint, OUT idx integer)
RETURNS integer AS
'SELECT idx from opengeo.ru_psk_rels_parts_idx where id=$1 and unnest=$2 '
LANGUAGE sql VOLATILE
COST 100;




при этом на прогретых данных имеем:
 

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
DO
$$
DECLARE 
	_t1 record;
	_t2	record;
	ok boolean;
	sum_dist double precision;
	sum_dist_min double precision;
	a_id_start	bigint[];
	a_id_end	bigint[];
	a_sum_dist double precision[];
	a_dist_start double precision[];
	a_dist_end double precision[];
	_result record;
BEGIN
	<<_t1_>>
	FOR _t1 IN
		(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 (
				select osm_id, way from opengeo.ru_psk_point where public_transport='stop_position' and tags -> 'bus' = 'yes'
			)bus_points  
		ORDER BY dist_start ) 
	LOOP
		<<_t2_>>
		FOR _t2 IN 
			(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 (
					select osm_id, way from opengeo.ru_psk_point where public_transport='stop_position' and tags -> 'bus' = 'yes'
					AND osm_id<> _t1.id_start -- ?
				)bus_points
				
			ORDER BY dist_end ) 
		LOOP 
			ok:= EXISTS (SELECT 1 FROM opengeo.ru_psk_rels rels
						WHERE
							array[_t1.id_start,_t2.id_end]<@rels.parts 
							and array['route','ref']<@rels.tags 

							and opengeo.bus_idx_rels_parts(rels.id,_t1.id_start)<opengeo.bus_idx_rels_parts(rels.id,_t2.id_end)
				)
				;
							
			sum_dist:= CASE WHEN ok THEN _t1.dist_start + _t2.dist_end END;
			sum_dist_min:=LEAST(sum_dist_min,sum_dist);
			IF ok THEN
				a_id_start	:= a_id_start	||	_t1.id_start;
				a_id_end	:=a_id_end		||	_t2.id_end;
				a_sum_dist	:=a_sum_dist	||	sum_dist;

				a_dist_start	:=a_dist_start	|| _t1.dist_start;
				a_dist_end 		:=a_dist_end	|| _t2.dist_end;
			END IF;
			

			IF _t2.dist_end >=sum_dist_min THEN
				exit _t2_;
			END IF;

			IF _t1.dist_start >=sum_dist_min THEN
				exit _t1_;
			END IF;
		END LOOP;
	END LOOP;
	
	SELECT * FROM (
	SELECT	unnest(a_id_start) AS id_start
			,unnest(a_id_end) AS id_end
			,unnest(a_sum_dist) AS sum_dist
			,unnest(a_dist_start) AS dist_start
			,unnest(a_dist_end) AS dist_end
	) foo ORDER BY sum_dist LIMIT 1
	INTO _result;

	raise NOTICE '%' , _result;
END;
$$;
-----------------------------------
NOTICE:  (2314760557,2314760553,139.332169287831,105.974855791139,33.357313496692)

Запрос успешно выполнен без возвращаемых данных за 40 мс.




ели я правильно понял про ваш unnest .

)))))Да, порядок такой!)))

P.S.: Боюсь понимать уже Вас(удф,мухи,да ещё и копулируют!!!ужас)
...
Рейтинг: 0 / 0
01.07.2016, 17:25
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266622
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
qwwq,

Код: plaintext
1.
ЗАМЕЧАНИЕ:  (2314760557,2314760553,139.332169287356,105.974855790664,33.357313496692)
Запрос успешно выполнен без возвращаемых данных за 90 мс.

Это я Ваш DO выполнил, по времени у меня также мой запрос, в чем изюминка?(если аккуратно можно ответить)))
...
Рейтинг: 0 / 0
01.07.2016, 17:29
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266626
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
its_meqwwq,

Код: plaintext
1.
ЗАМЕЧАНИЕ:  (2314760557,2314760553,139.332169287356,105.974855790664,33.357313496692)
Запрос успешно выполнен без возвращаемых данных за 90 мс.

Это я Ваш DO выполнил, по времени у меня также мой запрос, в чем изюминка?(если аккуратно можно ответить)))
что конкретно вас интересует ?
...
Рейтинг: 0 / 0
01.07.2016, 17:34
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266643
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
_t1_ и _t2_ - эточё такое?

ещё это непонятно, что тут происходит(это "или" или "и"?):

Код: plaintext
a_id_start	:= a_id_start || _t1.id_start;

с unnest-ом еще протестю...

и самый главный вопрос: это будет работать без потери времени при увеличении исходных таблиц?
...
Рейтинг: 0 / 0
01.07.2016, 18:01
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266649
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
its_me,

<<label>> :

https://www.postgresql.org/docs/9.5/static/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS

https://www.postgresql.org/docs/current/static/functions-array.html
...
Рейтинг: 0 / 0
01.07.2016, 18:12
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39266706
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
its_meи самый главный вопрос: это будет работать без потери времени при увеличении исходных таблиц?
с потерей не менее чем квадратичной. при одинаковых длинах путей. если и пути вырастут -- всё будет еще гаже.


вы что--то не то изобретаете. у вас постгис есть с дистансами и прочими удобствами меж точками и мультипойнтами -- а вы все это назад в скл тянете с неоптимальными переборами коллекций.

https://yandex.ru/yandsearch?text=multipoint distance postgis&lr=213

там где--то и оценки затратности от мощности геометрий были.
...
Рейтинг: 0 / 0
01.07.2016, 21:02
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39267816
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
qwwq,

Я правильно понял, что если все строки из первого/верхнего цикла не пройдут по фильтру(ok) с первой строкой внутреннего/второго цикла, то результата вообще не будет(я так понял описание выхода из блока/цикла)?
...
Рейтинг: 0 / 0
05.07.2016, 11:17
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39267841
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
its_meqwwq,

Я правильно понял, что если все строки из первого/верхнего цикла не пройдут по фильтру(ok) с первой строкой внутреннего/второго цикла, то результата вообще не будет(я так понял описание выхода из блока/цикла)?

народноемальчик, ты дебил ?

формально -- да, результата не будет.

но фильтр просто повторяет ваш множественный джойн , если вы не заметили. т.е. фильтрует только те пары, которые принадлежат одному пути и состоят в заданном отношении порядка. т.е. он не может ничего не вернуть. выгода EXISTS вместо JOIN -- на единственности чека.

можно идти в обратную сторону -- идти циклом не по точкам, а по путям. вложенно -- циклом по точкам путей (по индексу массивов). (там есть пара мест для оптимизации -- т.е. понятно , когда НЕ надо пересчитывать суммарные длинноты на шаге, посчитав расстояние от очередной точки).

т.е. затраты -- около ~ числа путей на число или квадрат (там на мультипойнты рассечения надо брать, но слева инкрементально расстояние считается, а справа его не надо пересчитывать часто -- если расстояние от выпавшей точки больше прежнего, т.ч. квадрата там не будет) числа точек в пути.
...
Рейтинг: 0 / 0
05.07.2016, 11:43
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39267860
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
qwwq,

А почему он "не может ничего не вернуть"?
Теоретически легко же может не вернуть?
...
Рейтинг: 0 / 0
05.07.2016, 12:04
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39268041
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
its_meqwwq,

А почему он "не может ничего не вернуть"?
Теоретически легко же может не вернуть?


Отвечаю сам себе. Вся проблема из-за маленького нюанса - я не знал , что 10>=null возвращает FALSE(где-то я встречал наоборот). Пришлось протестить этот вопрос отдельно. Теперь точно что-то вернёт, если такое есть там(пройдет все основные минимальные варианты). Так что прошу прощения за последнее сомнение.
На счет наоборот идти и других вариантов, на вскидку, будет быстрее или минимальны изменения по времени?
...
Рейтинг: 0 / 0
05.07.2016, 15:13
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39268053
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
its_meits_meqwwq,

А почему он "не может ничего не вернуть"?
Теоретически легко же может не вернуть?


Отвечаю сам себе. Вся проблема из-за маленького нюанса - я не знал , что 10>=null возвращает FALSE(где-то я встречал наоборот).мальчик, <<<>>>

Код: sql
1.
SELECT (10>=Null) IS NULL;


и не путайте результаты least | greatest с результатом сравнения. там результат если один из операндов null оговорен отдельным соглашением.
...
Рейтинг: 0 / 0
05.07.2016, 15:25
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39268056
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
its_meНа счет наоборот идти и других вариантов, на вскидку, будет быстрее или минимальны изменения по времени?

вам что--то мешает проверить ? напишите, учтите моменты, оптимизирующие проход -- и сравните.

всё в ваших руках. или вы хотите , чтобы за вас это сделали из чисто факультативного интереса ?
...
Рейтинг: 0 / 0
05.07.2016, 15:28
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39268080
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
qwwq,

Я протестил least и greatest с null и без - он просто игнорит его и всё.
На счет самому протестить, то я собирался попробовать идти геометрическим путём без цикла в голове по подбору вариантов по текущему направлению, поэтому спросил на вскидку. Да и спасибо за проделанную помощь, результат ведь уже есть положительный.
...
Рейтинг: 0 / 0
05.07.2016, 15:43
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39268090
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
qwwq,

А ещё, по поводу замечания что FALSE возвращает: для IF он его и возвращает, NULL это по любому=у не TRUE для IF.
...
Рейтинг: 0 / 0
05.07.2016, 15:47
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39268135
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
its_meqwwq,

А ещё, по поводу замечания что FALSE возвращает: для IF он его и возвращает, NULL это по любому=у не TRUE для IF.

не надо рисать херни, даже когда очень хочется

просто выполните :
Код: sql
1.
SELECT (10>=Null) =FALSE ;



3-х значная логика потому и называется 3-х значной, что

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
DO
$$
begin
	if null = true
	THEN
		RAISE NOTICE 'TRUE';
	ELSIF null = false THEN
		RAISE NOTICE 'FALSE';
	ELSE
		RAISE NOTICE 'ELSE';
	ENd IF;
end;
$$
...
Рейтинг: 0 / 0
05.07.2016, 16:31
| Ответить | Цитировать | Написать  
Что можно сделать для сокращения времени обработки запроса?
    #39268147
its_me
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
its_me
Гость
qwwq,

Тут каждый на своём остаётся и спорить просто нет смысла(зря я неуклюже растянул эту тему).
...
Рейтинг: 0 / 0
05.07.2016, 16:47
| Ответить | Цитировать | Написать  
69 сообщений из 69, показаны все 3 страниц
  все  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Что можно сделать для сокращения времени обработки запроса?
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]