ReSQL.ru
     
powered by simpleCommunicator - 2.0.41     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / быстрый Nested Loop и медленный Merge join
11 сообщений из 11, страница 1 из 1
быстрый Nested Loop и медленный Merge join
    #40040205
polin11
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
polin11
Гость
Есть иерархическая таблица Document, есть простой рекурсивный запрос, получаем записи вниз по иерархии, поле иерархии Hier

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
EXPLAIN (ANALYZE,BUFFERS)
 WITH RECURSIVE DOWN AS( 
           SELECT s.* 
           FROM "Document" s 
           WHERE "Hier" =  1123
           UNION 
           SELECT s1.* 
           FROM DOWN 
           INNER JOIN "Document" s1 ON s1."Hier"= DOWN."@Document" 
         ) 
         SELECT * 
         FROM DOWN



На одной БД планировщик использует Nested Loop запрос выполняется быстро

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
"CTE Scan on down  (cost=229320.82..231171.20 rows=92519 width=253) (actual time=0.207..2.038 rows=17 loops=1)"
"  Buffers: shared hit=62 read=3"
"  CTE down"
"    ->  Recursive Union  (cost=0.43..229320.82 rows=92519 width=272) (actual time=0.202..2.005 rows=17 loops=1)"
"          Buffers: shared hit=62 read=3"
"          ->  Index Scan using "iDocument-Hier" on "Document" s  (cost=0.43..27.52 rows=9 width=272) (actual time=0.194..0.203 rows=4 loops=1)"
"                Index Cond: ("Hier" = 4634473)"
"                Buffers: shared hit=7 read=1"
"          ->  Nested Loop  (cost=0.43..22744.29 rows=9251 width=272) (actual time=0.831..0.870 rows=6 loops=2)"
"                Buffers: shared hit=55 read=2"
"                ->  WorkTable Scan on down down_1  (cost=0.00..1.80 rows=90 width=8) (actual time=0.001..0.008 rows=8 loops=2)"
"                ->  Index Scan using "iDocument-Hier" on "Document" s1  (cost=0.43..251.66 rows=103 width=272) (actual time=0.096..0.098 rows=1 loops=17)"
"                      Index Cond: ("Hier" = down_1."@Document")"
"                      Buffers: shared hit=55 read=2"
"Planning time: 1.200 ms"
"Execution time: 3.596 ms"



На другой БД с примерно теми же данными используется Merge Join и запрос выполняется очень долго и много ресурсов использует

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
"CTE Scan on down  (cost=14385858.75..16871148.97 rows=124264511 width=253) (actual time=7.388..54128.588 rows=17 loops=1)"
"  Buffers: shared hit=562520 read=190529"
"  CTE down"
"    ->  Recursive Union  (cost=0.56..14385858.75 rows=124264511 width=277) (actual time=7.383..54128.547 rows=17 loops=1)"
"          Buffers: shared hit=562520 read=190529"
"          ->  Index Scan using "iDocument-Hier" on "Document" s  (cost=0.56..1610.97 rows=581 width=277) (actual time=7.373..9.128 rows=4 loops=1)"
"                Index Cond: ("Hier" = 939969)"
"                Buffers: shared read=7"
"          ->  Merge Join  (cost=480.01..1189895.76 rows=12426393 width=277) (actual time=27057.258..27059.673 rows=6 loops=2)"
"                Merge Cond: (s1."Hier" = down_1."@Document")"
"                Buffers: shared hit=562520 read=190522"
"                ->  Index Scan using "iDocument-Hier" on "Document" s1  (cost=0.56..986143.92 rows=6750595 width=277) (actual time=0.706..26652.712 rows=2957380 loops=2)"
"                      Buffers: shared hit=562520 read=190522"
"                ->  Sort  (cost=479.45..493.98 rows=5810 width=8) (actual time=0.034..0.040 rows=11 loops=2)"
"                      Sort Key: down_1."@Document""
"                      Sort Method: quicksort  Memory: 25kB"
"                      ->  WorkTable Scan on down down_1  (cost=0.00..116.20 rows=5810 width=8) (actual time=0.003..0.004 rows=8 loops=2)"
"Planning time: 1.677 ms"
"Execution time: 54145.287 ms"




В обеих базах есть индекс по полю Hier, пробовал выполнить vacuum analyze, reindex, обновить статистику для планировщика - ничего не помогает.

Как решить проблему на второй базе, заставить планировщик использовался Nested Loop?
...
Рейтинг: 0 / 0
31.01.2021, 02:10
| Ответить | Цитировать | Написать  
быстрый Nested Loop и медленный Merge join
    #40040211
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
polin11,

Разница в оценке
" -> Index Scan using "iDocument-Hier" on "Document" s (cost=0.43..27.52 rows=9 width=272) (actual time=0.194..0.203 rows=4 loops=1)"
" Index Cond: ("Hier" = 4634473)"

vs

" -> Index Scan using "iDocument-Hier" on "Document" s (cost=0.56..1610.97 rows=581 width=277) (actual time=7.373..9.128 rows=4 loops=1)"
" Index Cond: ("Hier" = 939969)"


при фактически одинаковых результатах...

а покажите вот какие вещи

explain analyze select from Document"
WHERE "Hier" = 4634473;

с первой базы

и

explain analyze select from "Document"
WHERE "Hier" = 939969;

со второй базы...

далее с ОБОИХ баз результаты
explain analyze select from "Document";
и
explain analyze select distinct "Hier" from "Document";

по этим итогам можно будет понять насколько "с примерно теми же данными" и главное как это поправить.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
31.01.2021, 05:27
| Ответить | Цитировать | Написать  
быстрый Nested Loop и медленный Merge join
    #40040240
polin11
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
polin11
Гость
Maxim Boguk,

для первой БД базы с nested loop

Код: sql
1.
2.
explain analyze select from Document"
WHERE "Hier" = 4634473;



Код: sql
1.
2.
3.
4.
5.
"Index Only Scan using "iDocument-Hier" on "Document"  (cost=0.43..10.96 rows=9 width=0) (actual time=0.032..0.037 rows=4 loops=1)"
"  Index Cond: ("Hier" = 4634473)"
"  Heap Fetches: 0"
"Planning time: 0.368 ms"
"Execution time: 0.103 ms"



Код: sql
1.
explain analyze select from "Document";



Код: sql
1.
2.
3.
4.
"Index Only Scan using "pDocument" on "Document"  (cost=0.43..253541.32 rows=6528203 width=0) (actual time=0.031..5072.543 rows=6528443 loops=1)"
"  Heap Fetches: 393112"
"Planning time: 0.206 ms"
"Execution time: 9136.559 ms"



Код: sql
1.
explain analyze select distinct "Hier" from "Document";



Код: sql
1.
2.
3.
4.
5.
"HashAggregate  (cost=310868.54..311503.40 rows=63486 width=8) (actual time=12820.134..12909.957 rows=82974 loops=1)"
"  Group Key: "Hier""
"  ->  Seq Scan on "Document"  (cost=0.00..294548.03 rows=6528203 width=8) (actual time=0.122..7359.429 rows=6528443 loops=1)"
"Planning time: 0.192 ms"
"Execution time: 12961.870 ms"




для проблемной БД с Merge Join

Код: sql
1.
2.
explain analyze select from "Document"
WHERE "Hier" = 939969;



Код: sql
1.
2.
3.
4.
5.
"Index Only Scan using "iDocument-Hier" on "Document"  (cost=0.56..374.08 rows=587 width=0) (actual time=0.145..0.148 rows=4 loops=1)"
"  Index Cond: ("Hier" = 939969)"
"  Heap Fetches: 0"
"Planning time: 0.591 ms"
"Execution time: 0.214 ms"




Код: sql
1.
explain analyze select from "Document";



Код: sql
1.
2.
3.
"Seq Scan on "Document"  (cost=0.00..302320.94 rows=6822494 width=0) (actual time=0.018..3532.781 rows=6695285 loops=1)"
"Planning time: 0.513 ms"
"Execution time: 3915.612 ms"


Код: sql
1.
explain analyze select distinct "Hier" from "Document";



Код: sql
1.
2.
3.
4.
5.
"HashAggregate  (cost=319377.17..319408.72 rows=3155 width=8) (actual time=7617.607..7644.169 rows=76453 loops=1)"
"  Group Key: "Hier""
"  ->  Seq Scan on "Document"  (cost=0.00..302320.94 rows=6822494 width=8) (actual time=0.029..5587.010 rows=6695285 loops=1)"
"Planning time: 0.560 ms"
"Execution time: 7650.837 ms"
...
Рейтинг: 0 / 0
31.01.2021, 13:09
| Ответить | Цитировать | Написать  
быстрый Nested Loop и медленный Merge join
    #40040253
Фотография ptr128
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ptr128
Участник
polin11

Код: sql
1.
"Index Only Scan using "iDocument-Hier" on "Document"  (cost=0.43..10.96 rows=9 width=0) (actual time=0.032..0.037 rows=4 loops=1)"




Код: sql
1.
"Index Only Scan using "iDocument-Hier" on "Document"  (cost=0.56..374.08 rows=587 width=0) (actual time=0.145..0.148 rows=4 loops=1)"




Уже здесь два порядка разницы видно.

Можно попробовать два варианта:
1. Расширить расчет статистик со 100 (по умолчанию) до 1000 уникальных значений поля "Hier". Тогда планировщик сможет точнее предсказывать количество строк при выборке по "iDocument-Hier".
Код: sql
1.
2.
ALTER TABLE "Document" ALTER COLUMN "Hier" SET STATISTICS 1000;
ANALYZE "Document" ("Hier");



2. Создать расширенную статистику по взаимосвязи предок-потомок в таблице "Document". Тогда планировщик скорее уйдет во вложенный цикл, так как будет знать, сколько же в среднем потомков у одного предка.
Код: sql
1.
2.
CREATE STATISTICS IF NOT EXISTS "Document_Hier2@Document_stat" ON "Hier", "@Document" FROM "Document";
ANALYZE "Document" ("Hier", "@Document");
...
Рейтинг: 0 / 0
31.01.2021, 15:21
| Ответить | Цитировать | Написать  
быстрый Nested Loop и медленный Merge join
    #40040258
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
polin11,

hm а вы уверены что
1)у баз одинаковый default_statistics_target (show default_statistics_target ;) ?

2)если ответ да одинаковый то не нет ли измененной статистики по "Document"."Hier" на первой базе где проблемы нет (через alter table set statistics)

3)если тоже нет то не стоит ли ручной n_distinct в первой базе для колонки "Document"."Hier"


Проблема у вас в разнице вот тут вот принципиальной

первая база
Код: plaintext
"HashAggregate  (cost=310868.54..311503.40 rows= 63486  width=8) (actual time=12820.134..12909.957 rows= 82974  loops=1)"
тут более менее верная оценка уникальных Hier

вторая база
Код: plaintext
"HashAggregate  (cost=319377.17..319408.72 rows= 3155  width=8) (actual time=7617.607..7644.169 rows= 76453  loops=1)"
ошибка в 20 раз

отсюда и план плывет...

вам интересно разобраться что и почему? или интересно быстро починить...

если быстро починить то просто на проблемной базе сделать
или
Код: plaintext
1.
alter table "Document" alter "Hier" SET STATISTICS 1000;  (или даже 10000)
analyze "Documents";

или что более правильно
Код: plaintext
1.
alter table "Document" alter "Hier" set (n_distinct=-0.01);
analyze "Documents";

после чего проверить что
explain analyze select distinct "Hier" from "Document";
стал адекватные цифры выдавать
и аналогично что план запроса исправился.



--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
31.01.2021, 16:27
| Ответить | Цитировать | Написать  
быстрый Nested Loop и медленный Merge join
    #40040262
Фотография ptr128
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ptr128
Участник
Maxim Boguk,

расширенные статистики мне нередко помогали в иерархических запросах. Особенно, когда иерархий много, но узких, как у ТС (17 записей на выходе).
У него же на обоих базах расширенная статистика просится, так как в плане на три порядка больше записей в результате запроса, чем по факту.
...
Рейтинг: 0 / 0
31.01.2021, 16:40
| Ответить | Цитировать | Написать  
быстрый Nested Loop и медленный Merge join
    #40040277
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
ptr128
Maxim Boguk,

расширенные статистики мне нередко помогали в иерархических запросах. Особенно, когда иерархий много, но узких, как у ТС (17 записей на выходе).
У него же на обоих базах расширенная статистика просится, так как в плане на три порядка больше записей в результате запроса, чем по факту.


единственное зачем в данном случае нужен увеличенный statistics target - чтобы база могла более точно количество уникальных значений подсчитать (потому что явно значения с 4 вхождениями в гистограмму most common values не попадут никогда)... а это намного проще и эффективнее через n_distinct базе сказать явным образом.

почему плохо бездумно расширенная статистика:
1)при увеличении stat target в 10 раз - пропорционально в 10 раз становится тяжелее analyze (что не всегда приемлемо и может быть больно при pg_upgrade)
2)замедление времени планирования (во много раз тоже) при использовании более широкой статистики
(я уже много раз получал снижение нагрузки на базу в пару раз поставив default_statistics_target=10 вместо 100 и подняв статистику в тех локальных местах где после этого ломались планы... потому что на быстрых запросах время планирования часто >> времени исполнения а prepared запросов естественно нет из за повсеместного использования pgbouncer и такого же повсеместного нежелания использовать хранимки так что приходится не скорость выполнения оптимизировать при 50.000+ rps а скорость планирования).

а вот простановка n_distinct в 90% случаев является вполне рабочей заменой расширенной статистики для планировщика.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
31.01.2021, 18:02
| Ответить | Цитировать | Написать  
быстрый Nested Loop и медленный Merge join
    #40040288
Фотография ptr128
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ptr128
Участник
Maxim Boguk
почему плохо бездумно расширенная статистика:
1)при увеличении stat target в 10 раз

Какая связь между расширенными статистиками и увеличением stat target?
...
Рейтинг: 0 / 0
31.01.2021, 19:09
| Ответить | Цитировать | Написать  
быстрый Nested Loop и медленный Merge join
    #40040289
Фотография ptr128
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ptr128
Участник
ptr128
Maxim Boguk
почему плохо бездумно расширенная статистика:
1)при увеличении stat target в 10 раз

Какая связь между расширенными статистиками и увеличением stat target?


И чем это плохо?
ptr128

Создать расширенную статистику по взаимосвязи предок-потомок в таблице "Document". Тогда планировщик скорее уйдет во вложенный цикл, так как будет знать, сколько же в среднем потомков у одного предка.
Код: sql
1.
2.
CREATE STATISTICS IF NOT EXISTS "Document_Hier2@Document_stat" ON "Hier", "@Document" FROM "Document";
ANALYZE "Document" ("Hier", "@Document");

...
Рейтинг: 0 / 0
31.01.2021, 19:13
| Ответить | Цитировать | Написать  
быстрый Nested Loop и медленный Merge join
    #40040302
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
ptr128
Maxim Boguk
почему плохо бездумно расширенная статистика:
1)при увеличении stat target в 10 раз

Какая связь между расширенными статистиками и увеличением stat target?


я понял что под расширенными статистиками вы имеете в виду stats target.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
31.01.2021, 19:58
| Ответить | Цитировать | Написать  
быстрый Nested Loop и медленный Merge join
    #40040306
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
ptr128
ptr128
пропущено...

Какая связь между расширенными статистиками и увеличением stat target?


И чем это плохо?
ptr128

Создать расширенную статистику по взаимосвязи предок-потомок в таблице "Document". Тогда планировщик скорее уйдет во вложенный цикл, так как будет знать, сколько же в среднем потомков у одного предка.
Код: sql
1.
2.
CREATE STATISTICS IF NOT EXISTS "Document_Hier2@Document_stat" ON "Hier", "@Document" FROM "Document";
ANALYZE "Document" ("Hier", "@Document");



я вот пытаюсь понять как именно планировщик будет эту стату использовать потому что вещей типа "сколько же в среднем потомков у одного предка" в расширенной статистике нет...
так есть только
"Currently supported kinds are ndistinct, which enables n-distinct statistics, dependencies, which enables functional dependency statistics"
и из них это никак не выводится даже теоретически.

для эксперимента можно сделать конечно но я подозревают что ошибка в оценке
Merge Join (cost=480.01..1189895.76 rows=12426393 width=277) (actual time=27057.258..27059.673 rows=6 loops=2)"
тут никак не исправится.

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

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
31.01.2021, 20:07
| Ответить | Цитировать | Написать  
11 сообщений из 11, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / быстрый Nested Loop и медленный Merge join
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru             Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&fpage=16&tid=1994223]:
 0ms
get settings:
 10ms
get forum list:
 11ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 39ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 70ms
get tp. blocked users:
 1ms
others: 45ms
total:  193ms
созд. / загр.: 193ms
x
x
Закрыть


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