ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Неоптимальный план запроса
8 сообщений из 8, страница 1 из 1
Неоптимальный план запроса
    #39700537
Vladimir Yanchenko
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vladimir Yanchenko
Гость
Здравствуйте!

Столкнулся с тем, что Postgres неоптимально выбирает план.

Запрос:
select servicecal0_.id as col_0_0_, employee1_.id as col_1_0_, servicecal0_.title as col_2_0_, servicecal0_.removed as col_3_0_, servicecal0_.removal_date as col_4_0_, servicecal0_.case_id as col_5_0_ from public.tbl_servicecall servicecal0_ inner join public.tbl_employee employee1_ on servicecal0_.author_id=employee1_.id where employee1_.id in (443891218) order by servicecal0_.id asc limit 21

Есть индекс idx_abstractus0 по таблице tbl_servicecall (id, title, removed, removal_date, case_id, route, clientemployee_id);

План запроса:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
                                                                                    QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.85..6348.35 rows=21 width=47) (actual time=23233.830..23425.123 rows=21 loops=1)
   ->  Nested Loop  (cost=0.85..1783648.60 rows=5901 width=47) (actual time=23233.829..23425.110 rows=21 loops=1)
         ->  Index Scan using tbl_servicecall_pkey on tbl_servicecall servicecal0_  (cost=0.43..1783566.39 rows=5901 width=47) (actual time=23233.751..23424.964 rows=21 loops=1)
               Filter: (author_id = 443891218)
               Rows Removed by Filter: 2295008
         ->  Materialize  (cost=0.42..8.44 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=21)
               ->  Index Only Scan using tbl_employee_pkey on tbl_employee employee1_  (cost=0.42..8.44 rows=1 width=8) (actual time=0.066..0.067 rows=1 loops=1)
                     Index Cond: (id = 443891218)
                     Heap Fetches: 1
 Planning time: 1.424 ms
 Execution time: 23425.233 ms
(11 строк)



Если указать планировщику не использовать сканирование индекса, получаю приемлемое время выполнения:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
     
                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=22608.94..22608.99 rows=21 width=47) (actual time=91.102..91.125 rows=21 loops=1)
   ->  Sort  (cost=22608.94..22623.69 rows=5901 width=47) (actual time=91.100..91.104 rows=21 loops=1)
         Sort Key: servicecal0_.id
         Sort Method: top-N heapsort  Memory: 28kB
         ->  Nested Loop  (cost=118.59..22449.84 rows=5901 width=47) (actual time=2.827..86.862 rows=6904 loops=1)
               ->  Bitmap Heap Scan on tbl_employee employee1_  (cost=4.43..8.44 rows=1 width=8) (actual time=0.037..0.038 rows=1 loops=1)
                     Recheck Cond: (id = 443891218)
                     Heap Blocks: exact=1
                     ->  Bitmap Index Scan on tbl_employee_pkey  (cost=0.00..4.43 rows=1 width=0) (actual time=0.028..0.028 rows=1 loops=1)
                           Index Cond: (id = 443891218)
               ->  Bitmap Heap Scan on tbl_servicecall servicecal0_  (cost=114.16..22382.39 rows=5901 width=47) (actual time=2.784..83.431 rows=6904 loops=1)
                     Recheck Cond: (author_id = 443891218)
                     Heap Blocks: exact=6645
                     ->  Bitmap Index Scan on idx_servicecall_author  (cost=0.00..112.69 rows=5901 width=0) (actual time=1.811..1.811 rows=6904 loops=1)
                           Index Cond: (author_id = 443891218)
 Planning time: 3.523 ms
 Execution time: 91.351 ms
(17 строк)



Статистику перестраивал, увеличивал значение default_statistics_target.
Стоимости операций в конфигурации выставлены по умолчанию.

Подскажите, пожалуйста, в чем может быть дело.
...
Рейтинг: 0 / 0
10.09.2018, 17:12
| Ответить | Цитировать | Написать  
Неоптимальный план запроса
    #39700591
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Vladimir Yanchenko,

А что говорит
select count(*), count(*) filter (where author_id = 443891218), min(id), min(id) filter (where author_id = 443891218) from tbl_servicecall;

?

И всегда делайте explain (ANALYZE, COSTS, BUFFERS, TIMING) а не explain analyze
иначе не понять там с CPU плохо или диск тормозит.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
10.09.2018, 18:22
| Ответить | Цитировать | Написать  
Неоптимальный план запроса
    #39700630
Vladimir Yanchenko
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vladimir Yanchenko
Гость
Maxim Boguk, спасибо за ответ.

Переделал план рекомендуемой командой:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
                                                                                    QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.98..10111.55 rows=21 width=47) (actual time=34632.677..35442.927 rows=21 loops=1)
   Buffers: shared hit=72682 read=2229226 written=500
   ->  Unique  (cost=0.98..2972990.10 rows=6175 width=47) (actual time=34632.674..35442.903 rows=21 loops=1)
         Buffers: shared hit=72682 read=2229226 written=500
         ->  Nested Loop  (cost=0.98..2972912.91 rows=6175 width=47) (actual time=34632.671..35442.789 rows=21 loops=1)
               Buffers: shared hit=72682 read=2229226 written=500
               ->  Index Scan using idx_abstractus0 on tbl_servicecall servicecal0_  (cost=0.56..2972827.29 rows=6175 width=47) (actual time=34632.246..35442.263 rows=21 loops=1)
                     Filter: (author_id = 443891218)
                     Rows Removed by Filter: 2295008
                     Buffers: shared hit=72682 read=2229219 written=500
               ->  Materialize  (cost=0.42..8.44 rows=1 width=8) (actual time=0.021..0.021 rows=1 loops=21)
                     Buffers: shared read=7
                     ->  Index Only Scan using tbl_employee_pkey on tbl_employee employee1_  (cost=0.42..8.44 rows=1 width=8) (actual time=0.362..0.364 rows=1 loops=1)
                           Index Cond: (id = 443891218)
                           Heap Fetches: 1
                           Buffers: shared read=7
 Planning time: 12.024 ms
 Execution time: 35443.367 ms
(18 строк)



Вывод запроса:
select count(*), count(*) filter (where author_id = 443891218), min(id), min(id) filter (where author_id = 443891218) from tbl_servicecall;
count | count | min | min
---------+-------+---------+-----------
4116804 | 6904 | 1728402 | 447887574
...
Рейтинг: 0 / 0
10.09.2018, 19:42
| Ответить | Цитировать | Написать  
Неоптимальный план запроса
    #39700641
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Vladimir Yanchenko,

Теперь смотрим на проблему:
всего строк 4116804 из них 6904 с нужным ID
если бы у вас author_id располагался равномерно по диапазону ID
то для выбора 21 строки по author_id = 443891218 order by id asc limit 21
базе бы понадобилось просмотреть (4116804/6904)*21 = 12516 строки
по факту же база просматривает Rows Removed by Filter: 2295008 строк (т.е. пол таблицы или в 200 раз больше).
Отсюда и тормоза.

Называется скрытая корреляция между данными.

Если у вас 10тая версия то может помочь
CREATE STATISTICS IF NOT EXISTS tbl_servicecall_author_id_id_correlation ON id, author_id FROM tbl_servicecall;
analyze tbl_servicecall;

PS: а может и не помочь но сделана она в 10той версии как раз для этого.

PPS: для версий до 10той я бы переделал запрос на order by servicecal0_.id+0 asc limit 21 чтобы убрать неудачный план.
Но такая версия запроса будет себя плохо очень вести если у какого то из author_id будет очень много строк в таблице (тогда тот план что у вас быстрый - станет ОЧЕНЬ медленным).

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
10.09.2018, 20:00
| Ответить | Цитировать | Написать  
Неоптимальный план запроса
    #39700656
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Vladimir Yanchenko,

могабыть сделать индекс по (author_id, id ) и запинать план на него.

если в author_id in () множество, но небольшое, авторов -- пинать потипу лузскана с предфильтрацией не более 21 на рыло и ручным речеком после слияния

если авторов в ине великое множество -- то вернуться к изначальному запросу.
...
Рейтинг: 0 / 0
10.09.2018, 21:11
| Ответить | Цитировать | Написать  
Неоптимальный план запроса
    #39700811
Vladimir Yanchenko
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vladimir Yanchenko
Гость
CREATE STATISTICS IF NOT EXISTS tbl_servicecall_author_id_id_correlation ON id, author_id FROM tbl_servicecall;
analyze tbl_servicecall;

Создание статистики не поменяло план.
Создал индекс по (author_id, id ). Также без результатов.

author_id в строке tbl_servicecall может быть только один, это ORM использует конструкцию IN.

Попробовал тот же запрос с другими значениями author_id. Действительно, если в индексе нужно перебрать только небольшую часть значений, запрос по нашему "проблемному" плану выполняется быстро. Проблема возникает только на некоторых значениях, когда нужно пересмотреть значительную часть индекса.

Неужели нет возможности уточнить статистику по распределению значений в индексе?

Кстати, при смене порядка сортировки выбирается другой план, и запрос выполняется за 277 мс.

Код: 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.
                                                                               QUERY PLAN                                                                               
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=24972.63..24972.95 rows=21 width=47) (actual time=112.435..112.470 rows=21 loops=1)
   Buffers: shared read=6670
   ->  Unique  (cost=24972.63..25069.79 rows=6477 width=47) (actual time=112.433..112.464 rows=21 loops=1)
         Buffers: shared read=6670
         ->  Sort  (cost=24972.63..24988.83 rows=6477 width=47) (actual time=112.432..112.448 rows=21 loops=1)
               Sort Key: servicecal0_.id DESC, servicecal0_.title, servicecal0_.removed, servicecal0_.removal_date, servicecal0_.case_id
               Sort Method: quicksort  Memory: 732kB
               Buffers: shared read=6670
               ->  Nested Loop  (cost=123.05..24562.60 rows=6477 width=47) (actual time=3.402..106.113 rows=6904 loops=1)
                     Buffers: shared read=6670
                     ->  Index Only Scan using tbl_employee_pkey on tbl_employee employee1_  (cost=0.42..8.44 rows=1 width=8) (actual time=0.099..0.104 rows=1 loops=1)
                           Index Cond: (id = 443891218)
                           Heap Fetches: 1
                           Buffers: shared read=4
                     ->  Bitmap Heap Scan on tbl_servicecall servicecal0_  (cost=122.63..24489.40 rows=6477 width=47) (actual time=3.295..102.578 rows=6904 loops=1)
                           Recheck Cond: (author_id = 443891218)
                           Heap Blocks: exact=6645
                           Buffers: shared read=6666
                           ->  Bitmap Index Scan on idx_servicecall_author  (cost=0.00..121.01 rows=6477 width=0) (actual time=2.349..2.349 rows=6904 loops=1)
                                 Index Cond: (author_id = 443891218)
                                 Buffers: shared read=21
 Planning time: 2.592 ms
 Execution time: 112.745 ms
(23 строки)
...
Рейтинг: 0 / 0
11.09.2018, 11:15
| Ответить | Цитировать | Написать  
Неоптимальный план запроса
    #39700853
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Vladimir Yanchenko,

>>Неужели нет возможности уточнить статистику по распределению значений в индексе?

Это не распределение значение а тайное знание что author_id = 443891218 в начале таблицы public.tbl_servicecall отсутствует вообще... тут даже не ясно в каком виде подобную информацию собирать и хранить увы...
Может например быть первая запись с этим author_id в первых 100 строках по id таблицы а оставшиеся 20 в самом конце таблицы и что дальше делать?


>>Создал индекс по (author_id, id ). Также без результатов.
А если переписать запрос как:
Код: plsql
1.
select servicecal0_.id as col_0_0_,  servicecal0_.author_id as col_1_0_, servicecal0_.title as col_2_0_, servicecal0_.removed as col_3_0_, servicecal0_.removal_date as col_4_0_, servicecal0_.case_id as col_5_0_ from public.tbl_servicecall servicecal0_  where servicecal0_.author_id in (443891218) order by servicecal0_.id asc limit 21


то какой план получается с индексом индексом по (author_id, id )?

оно с этим индексом лучше не становится? зачем вообще в этом запросе join c public.tbl_employee ???
И если уж вам ну очень хочется join оставить то вот так вот сделать попробовать

Код: plsql
1.
select servicecal0_.id as col_0_0_, employee1_.id as col_1_0_, servicecal0_.title as col_2_0_, servicecal0_.removed as col_3_0_, servicecal0_.removal_date as col_4_0_, servicecal0_.case_id as col_5_0_ from public.tbl_servicecall servicecal0_ inner join public.tbl_employee employee1_ on servicecal0_.author_id=employee1_.id where servicecal0_.author_id in (443891218) order by servicecal0_.id asc limit 21



--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
11.09.2018, 12:16
| Ответить | Цитировать | Написать  
Неоптимальный план запроса
    #39701009
Vladimir Yanchenko
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vladimir Yanchenko
Гость
Maxim Boguk,

Спасибо за помощь!

Я думал, что за отображение местоположения значений (рядом - не рядом) отвечает столбец correlation в pg_stats. Видимо это немного не то.

Предложенные запросы действительно быстро отрабатывают с индексом:

Код: 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.
# CREATE INDEX CONCURRENTLY idx_sc_author_id_id ON tbl_servicecall (author_id, id);
CREATE INDEX
# explain analyze select servicecal0_.id as col_0_0_, employee1_.id as col_1_0_, servicecal0_.title as col_2_0_, servicecal0_.removed as col_3_0_, servicecal0_.removal_date as col_4_0_, servicecal0_.case_id as col_5_0_ from public.tbl_servicecall servicecal0_ inner join public.tbl_employee employee1_ on servicecal0_.author_id=employee1_.id where employee1_.id in (443891218) order by servicecal0_.id asc limit 21;
                                                                              QUERY PLAN                                                                               
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.85..84.53 rows=21 width=47) (actual time=0.161..0.305 rows=21 loops=1)
   ->  Nested Loop  (cost=0.85..25814.70 rows=6478 width=47) (actual time=0.158..0.298 rows=21 loops=1)
         ->  Index Scan using idx_sc_author_id_id on tbl_servicecall servicecal0_  (cost=0.43..25725.28 rows=6478 width=47) (actual time=0.087..0.203 rows=21 loops=1)
               Index Cond: (author_id = 443891218)
         ->  Materialize  (cost=0.42..8.44 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=21)
               ->  Index Only Scan using tbl_employee_pkey on tbl_employee employee1_  (cost=0.42..8.44 rows=1 width=8) (actual time=0.061..0.068 rows=1 loops=1)
                     Index Cond: (id = 443891218)
                     Heap Fetches: 2
 Planning time: 3.422 ms
 Execution time: 0.510 ms
(10 строк)


# explain analyze select servicecal0_.id as col_0_0_,  servicecal0_.author_id as col_1_0_, servicecal0_.title as col_2_0_, servicecal0_.removed as col_3_0_, servicecal0_.removal_date as col_4_0_, servicecal0_.case_id as col_5_0_ from public.tbl_servicecall servicecal0_  where servicecal0_.author_id in (443891218) order by servicecal0_.id asc limit 21
                                                                           QUERY PLAN                                                                            
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..83.82 rows=21 width=47) (actual time=0.063..0.206 rows=21 loops=1)
   ->  Index Scan using idx_sc_author_id_id on tbl_servicecall servicecal0_  (cost=0.43..25725.28 rows=6478 width=47) (actual time=0.062..0.201 rows=21 loops=1)
         Index Cond: (author_id = 443891218)
 Planning time: 0.683 ms
 Execution time: 0.229 ms
(5 строк)



Хотя с DISTINCT запрос не ускоряется:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
# explain analyze select distinct servicecal0_.id as col_0_0_, employee1_.id as col_1_0_, servicecal0_.title as col_2_0_, servicecal0_.removed as col_3_0_, servicecal0_.removal_date as col_4_0_, servicecal0_.case_id as col_5_0_ from public.tbl_servicecall servicecal0_ inner join public.tbl_employee employee1_ on servicecal0_.author_id=employee1_.id where employee1_.id in (443891218) order by servicecal0_.id asc limit 21;
                                                                                    QUERY PLAN                                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.98..9656.63 rows=21 width=47) (actual time=19543.093..19711.695 rows=21 loops=1)
   ->  Unique  (cost=0.98..2980380.25 rows=6482 width=47) (actual time=19543.091..19711.681 rows=21 loops=1)
         ->  Nested Loop  (cost=0.98..2980299.22 rows=6482 width=47) (actual time=19543.089..19711.619 rows=21 loops=1)
               ->  Index Scan using idx_abstractus0 on tbl_servicecall servicecal0_  (cost=0.56..2980209.76 rows=6482 width=47) (actual time=19543.004..19711.467 rows=21 loops=1)
                     Filter: (author_id = 443891218)
                     Rows Removed by Filter: 2295008
               ->  Materialize  (cost=0.42..8.44 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=21)
                     ->  Index Only Scan using tbl_employee_pkey on tbl_employee employee1_  (cost=0.42..8.44 rows=1 width=8) (actual time=0.064..0.073 rows=1 loops=1)
                           Index Cond: (id = 443891218)
                           Heap Fetches: 2
 Planning time: 3.102 ms
 Execution time: 19711.922 ms
(12 строк)



К сожалению, у нас не получается гибко менять запросы. Используется ORM, которая сама эти запросы составляет.
...
Рейтинг: 0 / 0
11.09.2018, 17:10
| Ответить | Цитировать | Написать  
8 сообщений из 8, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Неоптимальный план запроса
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&gotonew=1&tid=1995596]:
 0ms
get settings:
 8ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 34ms
get topic data:
 31ms
get first new msg:
 6ms
get forum data:
 2ms
get page messages:
 51ms
get tp. blocked users:
 1ms
others: 297ms
total:  448ms
созд. / загр.: 448ms
x
x
Закрыть


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