ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация большого запроса возможна?
15 сообщений из 65, страница 3 из 3
  3  все
Оптимизация большого запроса возможна?
    #35457923
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
UKYПосле долгих перезапусков выборки удалось получить вот такую вот картину (:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
->  Merge Join  (cost= 1236 . 98 .. 1298 . 87  rows= 117  width= 371 ) (actual time= 4 . 660 .. 6 . 652  rows= 59  loops= 1 )
    Merge Cond: (services.server = servers.id)
    ->  Index Scan using servers_pkey on servers  (cost= 0 . 00 .. 59 . 13  rows= 402  width= 11 ) (actual time= 0 . 016 .. 0 . 790  rows= 410  loops= 1 )

->  Merge Join  (cost= 1179 . 28 .. 1232 . 96  rows= 117  width= 368 ) (actual time= 2 . 522 .. 4 . 431  rows= 59  loops= 1 )
    Merge Cond: (services.id = folders.service)
    ->  Index Scan using services_pkey on services  (cost= 0 . 00 .. 51 . 28  rows= 414  width= 23 ) (actual time= 0 . 016 .. 0 . 824  rows= 413  loops= 1 )
здесь может немного быстрее оказаться NestedLoop.

попробуйте после трех команд:
set enable_seqscan to off;
set enable_hashjoin to off;
set enable_mergejoin to off;
...
Рейтинг: 0 / 0
29.07.2008, 14:19
| Ответить | Цитировать | Написать  
Оптимизация большого запроса возможна?
    #35457964
UKY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
UKY
Гость
Я просто в шоке:
Код: 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.
Unique  (cost= 1680 . 72 .. 1681 . 89  rows= 117  width= 376 ) (actual time= 4 . 654 .. 4 . 930  rows= 29  loops= 1 )
  ->  Sort  (cost= 1680 . 72 .. 1681 . 01  rows= 117  width= 376 ) (actual time= 4 . 649 .. 4 . 747  rows= 59  loops= 1 )
        Sort Key: tths.count, tths.id, files.name
        Sort Method:  quicksort  Memory: 55kB
        ->  Nested Loop  (cost= 0 . 00 .. 1676 . 70  rows= 117  width= 376 ) (actual time= 0 . 426 .. 4 . 104  rows= 59  loops= 1 )
              ->  Nested Loop  (cost= 0 . 00 .. 1521 . 84  rows= 117  width= 373 ) (actual time= 0 . 414 .. 3 . 346  rows= 59  loops= 1 )
                    Join Filter: (services.type = service.id)
                    ->  Index Scan using service_pkey on service  (cost= 0 . 00 .. 8 . 27  rows= 1  width= 13 ) (actual time= 0 . 010 .. 0 . 012  rows= 1  loops= 1 )
                    ->  Nested Loop  (cost= 0 . 00 .. 1512 . 11  rows= 117  width= 368 ) (actual time= 0 . 395 .. 3 . 111  rows= 59  loops= 1 )
                          ->  Nested Loop  (cost= 0 . 00 .. 1175 . 13  rows= 117  width= 353 ) (actual time= 0 . 379 .. 2 . 281  rows= 59  loops= 1 )
                                ->  Nested Loop  (cost= 0 . 00 .. 714 . 17  rows= 117  width= 290 ) (actual time= 0 . 360 .. 1 . 278  rows= 59  loops= 1 )
                                      ->  Nested Loop  (cost= 0 . 00 .. 176 . 28  rows= 20  width= 12 ) (actual time= 0 . 344 .. 0 . 705  rows= 19  loops= 1 )
                                            ->  Function Scan on tth_search  (cost= 0 . 00 .. 5 . 20  rows= 20  width= 4 ) (actual time= 0 . 320 .. 0 . 354  rows= 20  loops= 1 )
                                            ->  Index Scan using tths_pkey on tths  (cost= 0 . 00 .. 8 . 54  rows= 1  width= 8 ) (actual time= 0 . 008 .. 0 . 010  rows= 1  loops= 20 )
                                                  Index Cond: (tths.id = tth_search.tth_search)
                                      ->  Index Scan using files_tth_index on files  (cost= 0 . 00 .. 26 . 82  rows= 6  width= 286 ) (actual time= 0 . 008 .. 0 . 015  rows= 3  loops= 19 )
                                            Index Cond: (files.tth = tths.id)
                                ->  Index Scan using folders_pkey on folders  (cost= 0 . 00 .. 3 . 93  rows= 1  width= 75 ) (actual time= 0 . 008 .. 0 . 010  rows= 1  loops= 59 )
                                      Index Cond: (folders.id = files.path)
                          ->  Index Scan using services_pkey on services  (cost= 0 . 00 .. 2 . 87  rows= 1  width= 23 ) (actual time= 0 . 004 .. 0 . 006  rows= 1  loops= 59 )
                                Index Cond: (services.id = folders.service)
              ->  Index Scan using servers_pkey on servers  (cost= 0 . 00 .. 1 . 31  rows= 1  width= 11 ) (actual time= 0 . 003 .. 0 . 006  rows= 1  loops= 59 )
                    Index Cond: (servers.id = services.server)
Total runtime:  5 . 160  ms
...
Рейтинг: 0 / 0
29.07.2008, 14:32
| Ответить | Цитировать | Написать  
Оптимизация большого запроса возможна?
    #35457995
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
UKYЯ просто в шоке:
Код: plaintext
Total runtime:  5 . 160  ms
да, с NestedLoop получилось быстрее. но если не хочется перед каждым запросом выставлять три set enable_*, а по завершении запроса возвращать их обратно во избежание поломки планов других запросов. кстати, на передачу и выполнение этих шести команд также требуется время. то может быть проще оставить без set enable_* - 9.985 ms приемлемое время. можно еще поиграться с глобальными настройками random_page_cost и другими, на форуме обсуждалось: поиск
...
Рейтинг: 0 / 0
29.07.2008, 14:42
| Ответить | Цитировать | Написать  
Оптимизация большого запроса возможна?
    #35458031
UKY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
UKY
Гость
Я приводил результаты отдельно от комманд set enable_*. Меня это время выборки более чем устраивает :)

Огромное спасибо за помощь.
...
Рейтинг: 0 / 0
29.07.2008, 14:52
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
Оптимизация большого запроса возможна?
    #39826286
PWW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PWW
Участник
Прошло 10 лет, а чудо осталось чудом

set enable_seqscan to off;
set enable_hashjoin to off;
set enable_mergejoin to off;

Ускорение с 7 мин до 3 сек.
...
Рейтинг: 0 / 0
13.06.2019, 17:44
| Ответить | Цитировать | Написать  
Оптимизация большого запроса возможна?
    #39826317
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
PWWПрошло 10 лет, а чудо осталось чудом

set enable_seqscan to off;
set enable_hashjoin to off;
set enable_mergejoin to off;

Ускорение с 7 мин до 3 сек.

Это значит
1)сильно более вероятно - неверно (для вашего сервера и данных) выставлены random_page_cost/seq_page_cost и effective_cache_size
или
2)планировщик сильно в селективности ошибается
...
Рейтинг: 0 / 0
13.06.2019, 19:00
| Ответить | Цитировать | Написать  
Оптимизация большого запроса возможна?
    #39826323
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Maxim BogukPWWПрошло 10 лет, а чудо осталось чудом

set enable_seqscan to off;
set enable_hashjoin to off;
set enable_mergejoin to off;

Ускорение с 7 мин до 3 сек.

Это значит
1)сильно более вероятно - неверно (для вашего сервера и данных) выставлены random_page_cost/seq_page_cost и effective_cache_size
или
2)планировщик сильно в селективности ошибается

не-а
планировщик пж и на одной таблице бывает лажает. а уж на джойнах как правило.

т.ч. я бы предположил в корне неверную мат модель рассчёта костов. оно гораздо более похоже на правду.
...
Рейтинг: 0 / 0
13.06.2019, 19:27
| Ответить | Цитировать | Написать  
Оптимизация большого запроса возможна?
    #39826365
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
qwwqт.ч. я бы предположил в корне неверную мат модель рассчёта костов. оно гораздо более похоже на правду.

А можете предложить корректную модель?
Ну или может есть ссылки на публикации по этой теме?
...
Рейтинг: 0 / 0
13.06.2019, 23:49
| Ответить | Цитировать | Написать  
Оптимизация большого запроса возможна?
    #39826404
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
vyegorovqwwqт.ч. я бы предположил в корне неверную мат модель рассчёта костов. оно гораздо более похоже на правду.

А можете предложить корректную модель?
Ну или может есть ссылки на публикации по этой теме?
как бы я высказал предположение, следующее из наблюдений


а насчёт публикаций -- это общее место для практикующих пж--кодеров
21902781 -- последняя
искать лениво , но запинывание кривожоптимизатора на 2--3 порядка на нужный план малоосмысленными довесками в where -- и всякое прочее принуждение к нужному индексу -- стандартное занятие. у прошлом годе на хабре было и тут упоминалось. искать просто лень. как правило ваша команда отмалчивается в тряпочку или бухтит невнятно. а я ищи потом.

не говоря о том, что оно из коробки многого не умеет, что руками всё время приходится реализовывать. чтонть на тему проходов сиком по индексу -- из разряда луз-сканоподобных переборов. что скучно крайне и озлобляет. ну и т.п.
...
Рейтинг: 0 / 0
14.06.2019, 08:48
| Ответить | Цитировать | Написать  
Оптимизация большого запроса возможна?
    #39826539
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
vyegorov,

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


нашёл по старым следам 21025942



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


не удивлюсь, если кто--то годами приторговывает патчами, тщательно оберегаемыми от коммита в ".." кактам у вас принято -- эээ...-- "ванильную" версию.
...
Рейтинг: 0 / 0
14.06.2019, 12:33
| Ответить | Цитировать | Написать  
Оптимизация большого запроса возможна?
    #39827153
PWW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PWW
Участник
Вот, что я понял из знакомства с Postgres:

Postgres рабочий сервер для не ответственных проектов.

Планер запросов в Postgres не работает , все надо указывать через хинты. Примеры хинтов https://habr.com/ru/post/169751/.
После прямого указания хинтами какими индексами запрос должен воспользоваться, запрос выполняется за 200мс, без него он ... выполняется... он ..., снял его через 20минут ожидания.

Установки которые я опробовал ранее топорны
set enable_seqscan to off;
set enable_hashjoin to off;
set enable_mergejoin to off;

На одном запросе они работают, на другом похожем запросе который работает быстро и без этих set enable_..., если их поставить, то скорость катастрофически падает (зачесалось еще ускорить).

Уж лучше хинты
/*+ IndexScan(PK_t_key_id_key IX_Info_page_key_www_id_key IX_Info_page_key_www_id_page) */
SELECT * FROM t_key INNER JOIN ...

В данном проекте у меня 3 запроса, 5 статических таблиц на 99,8Гб с индексами, один пользователь и никакой ответственности. Три запроса я могу вылизать. Но я не могу себе представить как можно ухаживать за проектом с >1000 таблиц и >10'000 запросов, планы которых будут протухать по мере добавления данных. Возможно в Postgres еще много разных фичей. К счастью, пока они мне не известны, поэтому меня все устраивает.

Бесплатный сыр не только в мышеловке, но и в GNU.
...
Рейтинг: 0 / 0
17.06.2019, 11:34
| Ответить | Цитировать | Написать  
Оптимизация большого запроса возможна?
    #39827157
PWW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PWW
Участник
Дополню - если открыть новую вкладке для запроса в pgAdmin и выполнить запрос с хинтами , то он походит по времени, сели хотя бы один раз запустить запрос с set ..., то после этого запрос выполняется с хинтами без set ... .

Мой вопрос конечно решен, но планера запросов просто нет, зато есть шикарный бубен для плясок.
...
Рейтинг: 0 / 0
17.06.2019, 11:53
| Ответить | Цитировать | Написать  
Оптимизация большого запроса возможна?
    #39827175
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
PWWДополню - если открыть новую вкладке для запроса в pgAdmin и выполнить запрос с хинтами , то он походит по времени, сели хотя бы один раз запустить запрос с set ..., то после этого запрос выполняется с хинтами без set ... .

Мой вопрос конечно решен, но планера запросов просто нет, зато есть шикарный бубен для плясок.
blahblahblah


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

а пресловутый бубен не используют "не только лишь все".

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

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

к тому же алгоритмически планер беден до невозможности. -- например при наличии индексов по (А) и (Б) запрос вида "выбрать 20 записей вдоль индекса А досортированных по уникализирующему суффиксу Б " (select * from t order by A,B limit 20) он будет не через "взять не менее 20 вдоль А -- до следующего (по отношению к 20-му значению выборки) узла индекса А, результирующую выборку пересортировать с учетом Б и обрезать 20 первых" . а тупо сканом по Б в лучшем случае. или фуллсканом. и сиди, придумывай всякий раз, как это угрёбище заставить делать по--людски: 20794074
...
Рейтинг: 0 / 0
17.06.2019, 12:31
| Ответить | Цитировать | Написать  
Оптимизация большого запроса возможна?
    #39827188
PWW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PWW
Участник
qwwq,

Описано поведение именно этого запроса.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
set enable_seqscan to off;
set enable_hashjoin to off;
set enable_mergejoin to off;
--set enable_seqscan to on;
--set enable_hashjoin to on;
--set enable_mergejoin to on;
explain
/*+ IndexScan(PK_t_key_id_key IX_Info_page_key_www_id_key IX_Info_page_key_www_id_page) */
SELECT distinct t_key.keys, t_key.id_key,
                         t_key_1.keys AS Expr3, t_key_1.ch_t
FROM t_key INNER JOIN
                         t_info_page_key ON t_key.id_key = t_info_page_key.id_key INNER JOIN
                         t_info_page_key AS t_info_page_key_1 ON t_info_page_key.id_page = t_info_page_key_1.id_page INNER JOIN
                         t_key AS t_key_1 ON t_info_page_key_1.id_key = t_key_1.id_key
WHERE (t_key.keys ilike '%e110%') AND (t_info_page_key.pos_y > 20) AND (t_info_page_key_1.pos_y > 20)


В итоге его надо всеравно запускать с set... хотябы первый раз (открытая вкладка по видимому держит сессию, поэтому второй раз можно и без set...)

 
План
"Unique (cost=120459744.80..120735990.51 rows=22099657 width=104)"
" -> Sort (cost=120459744.80..120514993.94 rows=22099657 width=104)"
" Sort Key: t_key.keys, t_key.id_key, t_key_1.keys, t_key_1.ch_t"
" -> Gather (cost=1090.03..114138126.61 rows=22099657 width=104)"
" Workers Planned: 2"
" -> Nested Loop (cost=90.03..111927160.91 rows=9208190 width=104)"
" -> Nested Loop (cost=89.46..39331434.90 rows=9208190 width=56)"
" -> Nested Loop (cost=88.89..107354.46 rows=7283 width=56)"
" -> Parallel Bitmap Heap Scan on t_key (cost=88.32..21369.13 rows=2383 width=52)"
" Recheck Cond: ((keys)::text ~~* '%e110%'::text)"
" -> Bitmap Index Scan on trgm_t_key_idx (cost=0.00..86.89 rows=5719 width=0)"
" Index Cond: ((keys)::text ~~* '%e110%'::text)"
" -> Index Scan using "IX_Info_page_key_www_id_key" on t_info_page_key (cost=0.57..36.03 rows=5 width=8)"
" Index Cond: (id_key = t_key.id_key)"
" Filter: (pos_y > 20)"
" -> Index Scan using "IX_Info_page_key_www_id_page" on t_info_page_key t_info_page_key_1 (cost=0.57..5376.55 rows=915 width=8)"
" Index Cond: (id_page = t_info_page_key.id_page)"
" Filter: (pos_y > 20)"
" -> Index Scan using "PK_t_key_id_key" on t_key t_key_1 (cost=0.56..7.88 rows=1 width=56)"
" Index Cond: (id_key = t_info_page_key_1.id_key)"


Следующий запрос выполняется без плясок. Только что проверил, он выполняется с любыми set... on/off.
Код: sql
1.
2.
3.
4.
5.
6.
SELECT         t_page.page_, U1.u, t_key.keys, t_info_page_key.pos_y, t_info_page_key.pos_g
FROM            U1 INNER JOIN
                         t_page ON U1.k = t_page.id_www INNER JOIN
                         t_info_page_key ON t_page.id_page = t_info_page_key.id_page INNER JOIN
                         t_key ON t_info_page_key.id_key = t_key.id_key
WHERE        (t_page.page_ iLIKE '%E110%') AND   (t_info_page_key.pos_y > 20) AND (t_info_page_key.pos_y > 20)



 
План
"Nested Loop (cost=531.24..1266476.13 rows=100 width=214)"
" -> Nested Loop (cost=530.67..1265721.81 rows=100 width=170)"
" -> Nested Loop (cost=530.10..1088286.28 rows=34 width=162)"
" -> Seq Scan on u1 (cost=0.00..1.12 rows=12 width=122)"
" -> Bitmap Heap Scan on t_page (cost=530.10..90690.40 rows=3 width=48)"
" Recheck Cond: (id_www = u1.k)"
" Filter: ((page_)::text ~~* '%E110%'::text)"
" -> Bitmap Index Scan on t_page_id_www_idx (cost=0.00..530.10 rows=28560 width=0)"
" Index Cond: (id_www = u1.k)"
" -> Index Scan using "IX_Info_page_key_www_id_page" on t_info_page_key (cost=0.57..5209.54 rows=915 width=16)"
" Index Cond: (id_page = t_page.id_page)"
" Filter: ((pos_y > 20) AND (pos_y > 20))"
" -> Index Scan using "PK_t_key_id_key" on t_key (cost=0.56..7.54 rows=1 width=52)"
" Index Cond: (id_key = t_info_page_key.id_key)"
...
Рейтинг: 0 / 0
17.06.2019, 12:57
| Ответить | Цитировать | Написать  
Оптимизация большого запроса возможна?
    #39827237
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
PWW,

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

примерно в таком направлении
 
Код: 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.
SELECT distinct
	t_key.keys
	,t_key.id_key
	,t_key_1.keys AS Expr3
	,t_key_1.ch_t
FROM t_key
cross JOIN lateral
	(select * from t_info_page_key
	where  t_key.id_key = t_info_page_key.id_key
		AND (t_info_page_key.pos_y > 20)
	--order by id_key,pos_y... --index to scan (id_key,pos_y)
	limit 1000000000 -- prinuzhdenie k nested--loop-s
	) t_info_page_key
cross JOIN lateral
	(select * from t_info_page_key AS t_info_page_key_1
	where t_info_page_key.id_page = t_info_page_key_1.id_page
		AND (t_info_page_key_1.pos_y > 20)
	--order by id_page,pos_y --index to scan (id_page,pos_y)
	limit 1000000000
	) as t_info_page_key_1
cross JOIN lateral
	(select * from t_key AS t_key_1
	where t_info_page_key_1.id_key = t_key_1.id_key
	--order by id_key[,keys,ch_t]... --index to scan
	limit 1000000000
	) t_key_1
WHERE t_key.keys ilike '%e110%'



особо хорошо работает, если вы заведомо знаете что мощность или строго 1 (limit 1) или не выше небольшого числа (limit 7--8).

я правильно понимаю, что это у вас какие-то полнотекстовые самопалы ?
...
Рейтинг: 0 / 0
17.06.2019, 14:27
| Ответить | Цитировать | Написать  
15 сообщений из 65, страница 3 из 3
  3  все
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация большого запроса возможна?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=39827237&tid=1995161]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 50ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 52ms
get tp. blocked users:
 1ms
others: 301ms
total:  446ms
созд. / загр.: 446ms
x
x
Закрыть


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