Materialize в плане запроса / PostgreSQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Materialize в плане запроса

8 сообщений из 8, страница 1 из 1

Materialize в плане запроса

#40115814

eml78

Участник

Сообщения: 655

Рейтинг: 0 / 0

Есть большой сложный запрос, в плане выполнения у него есть такой фрагмент:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

->  Nested Loop  (cost=3901.61..370256.99 rows=38 width=501) (actual time=180792.445..921700.492 rows=37735 loops=1)
      Join Filter: (regpayment0_.contractor_id = contractor14_.id)
      Rows Removed by Join Filter: 1959767164
      ->  Index Scan using ix_b_contractor_linkedaccount on b_contractor contractor14_  (cost=0.42..8869.03 rows=131132 width=16) (actual time=0.013..415.633 rows=28905 loops=1)
            ->  Materialize  (cost=3901.19..272873.97 rows=45 width=501) (actual time=3.874..17.476 rows=67802 loops=28905)
                  ->  Merge Join  (cost=3901.19..272873.75 rows=45 width=501) (actual time=111955.810..135773.390 rows=67802 loops=1)
                        Merge Cond: (clsfundsty9_.activitykind_id = clsviddeja13_.id)
                        ->  Nested Loop  (cost=3900.05..283963.29 rows=202 width=509) (actual time=2456.641..135755.074 rows=150004 loops=1)
                              Join Filter: (regpayment0_.fundstype_id = clsfundsty9_.id)
                              Rows Removed by Join Filter: 360309608
                              ->  Index Scan using ix_bud_clsfundstype_activitykind_id on bud_clsfundstype clsfundsty9_  (cost=0.28..310.82 rows=2403 width=23) (actual time=0.011..19.941 rows=2403 loops=1)
                              ->  Materialize  (cost=3899.77..276371.89 rows=202 width=494) (actual time=0.022..40.332 rows=150004 loops=2403)
                                    ->  Gather  (cost=3899.77..276370.88 rows=202 width=494) (actual time=41.127..1108.839 rows=150004 loops=1)

1) Почему actual time у узлов Materialize меньше чем у вложенных? Как такое может быть, что Materialize завершил выполнение за 40.332 сек, а вложенный Gather только первую запись отдал через 41.127 сек?

2) Правильно ли я понимаю, что Materialize размещается в work_mem? И если Materialize в work_mem не влезает, то будет использоваться диск? Дело в том, что исходный work_mem был 20Mb, я предположил что медленная работа с Materialize отчасти объясняется дисковыми операциями - work_mem увеличили до 80Mb, но время выполнения запроса не изменилось.

server_version = 9.6.20

...

Рейтинг:

0 / 0

29.11.2021, 11:07

| Ответить | Цитировать | Написать

Materialize в плане запроса

#40115860

Guzya

Гость

Вроде, слово Materialize и есть сброс на диск в данном случаи.

...

Рейтинг:

0 / 0

29.11.2021, 13:29

| Ответить | Цитировать | Написать

Materialize в плане запроса

#40115888

Павел Лузанов

Участник

Сообщения: 866

Рейтинг: 0 / 0

eml78,

В actual_time показывается усредненное время по всем итерациям выполнения этой строки плана. Умножайте на значение из loops и получите общее время.

В плане есть два места, намекающие на то, что индексы на regpayment0_. по столбцам contractor_id и fundstype_id могли бы помочь. Но без знания деталей, это почти гадание.

Код: plaintext

1.
2.
3.
4.
5.
6.

...
      Join Filter: (regpayment0_.contractor_id = contractor14_.id)
      Rows Removed by Join Filter: 1959767164
...
                              Join Filter: (regpayment0_.fundstype_id = clsfundsty9_.id)
                              Rows Removed by Join Filter: 360309608
...

...

Рейтинг:

0 / 0

29.11.2021, 14:19

| Ответить | Цитировать | Написать

Materialize в плане запроса

#40115901

eml78

Участник

Сообщения: 655

Рейтинг: 0 / 0

Павел Лузанов

В actual_time показывается усредненное время по всем итерациям выполнения этой строки плана. Умножайте на значение из loops и получите общее время.

Спасибо за пояснение.

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

...

Рейтинг:

0 / 0

29.11.2021, 15:13

| Ответить | Цитировать | Написать

Materialize в плане запроса

#40115909

eml78

Участник

Сообщения: 655

Рейтинг: 0 / 0

Guzya

Вроде, слово Materialize и есть сброс на диск в данном случаи.

Мне казалось, что как раз наоборот.
www.postgresql.org/docs/9.6/using-explain.htmlNotice that here the planner has chosen to "materialize" the inner relation of the join, by putting a Materialize plan node atop it. This means that the t2 index scan will be done just once, even though the nested-loop join node needs to read that data ten times, once for each row from the outer relation. The Materialize node saves the data in memory as it's read, and then returns the data from memory on each subsequent pass.

Поэтому вопрос про Materialize и work_mem актуален

...

Рейтинг:

0 / 0

29.11.2021, 15:26

| Ответить | Цитировать | Написать

Materialize в плане запроса

#40115923

Melkij

Участник

Откуда: Санкт-Петербург

Сообщения: 1 447

Рейтинг: 0 / 0

eml78

Guzya

Вроде, слово Materialize и есть сброс на диск в данном случаи.

Поэтому вопрос про Materialize и work_mem актуален
https://github.com/postgres/postgres/blob/REL9_6_STABLE/src/backend/executor/nodeMaterial.c#L61 + https://github.com/postgres/postgres/blob/REL9_6_STABLE/src/backend/utils/sort/tuplestore.c#L19
Использует абстракцию tuplestore, сначала сохраняет в памяти что поместится по объёму work_mem, затем скидывает это на диск в виде временного файла.

...

Рейтинг:

0 / 0

29.11.2021, 15:59

| Ответить | Цитировать | Написать

Materialize в плане запроса

#40115926

Павел Лузанов

Участник

Сообщения: 866

Рейтинг: 0 / 0

eml78,

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

Не факт, что запрос упирается в work_mem. Добавьте в explain к analyze еще buffers. Станет яснее.

Но в примере проблема не в медленном materialize, а в неправильной оценке кардинальности, начиная с самой нижней строки плана. Эта ошибка приводит к тому, что в первой строке (и еще раз в середине плана) для соединения используется nested loops, хотя этот метод в обоих случаях неудачен. Вот и получается, что materialize гоняется в цикле тысячи раз.

...

Рейтинг:

0 / 0

29.11.2021, 16:16

| Ответить | Цитировать | Написать

Materialize в плане запроса

#40115930

eml78

Участник

Сообщения: 655

Рейтинг: 0 / 0

Павел Лузанов

Но в примере проблема не в медленном materialize, а в неправильной оценке кардинальности

Да, я в курсе, и в принципе даже известно как это поправить. Просто хотел для себя разобраться с materialize.

Спасибо, за ответы по work_mem - там судя по всему один materialize = 40Mb, а второй 80Mb - сейчас в темпе оказывается тот что на 80Мб. Видимо оба одновременно должно в память влезть чтобы быстро было...

...

Рейтинг:

0 / 0

29.11.2021, 16:35

| Ответить | Цитировать | Написать

8 сообщений из 8, страница 1 из 1

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Materialize в плане запроса

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&gotonew=1&tid=1993768]:	0ms
get settings:	7ms
get forum list:	11ms
check forum access:	3ms
check topic access:	3ms
track hit:	45ms
get topic data:	9ms
get first new msg:	7ms
get forum data:	2ms
get page messages:	43ms
get tp. blocked users:	1ms
others:	249ms

total:	380ms