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

Есть план выполнения запроса, в котором видно, что доступ к данным осуществляется с использованием составного индекса.

Увеличил значение work_mem.

План изменился. Теперь выполняется сканирование таблицы с последующей фильтрацией по одному полю из составного индекса. Индекс не используется. Время выполнения запроса снизилось на порядок.

Как это можно объяснить?
...
Рейтинг: 0 / 0
06.06.2018, 08:56
| Ответить | Цитировать | Написать  
Увеличение work_mem приводит к неоптимальному плану
    #39656101
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
big-trotЗаметил такую особенность.

Есть план выполнения запроса, в котором видно, что доступ к данным осуществляется с использованием составного индекса.

Увеличил значение work_mem.

План изменился. Теперь выполняется сканирование таблицы с последующей фильтрацией по одному полю из составного индекса. Индекс не используется. Время выполнения запроса снизилось на порядок.

Как это можно объяснить?

Вряд ли это результат изменения именно work_mem. Покажите explain с разными значениями work_mem для начала.

--
Maxim Boguk
dataegret.ru
...
Рейтинг: 0 / 0
06.06.2018, 09:08
| Ответить | Цитировать | Написать  
Увеличение work_mem приводит к неоптимальному плану
    #39656156
big-trot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
big-trot
Гость
Maxim Boguk,

План выполнения с work_mem=32MB

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
QUERY PLAN
CTE Scan on search_objects co  (cost=9733263.38..9831834.96 rows=4928579 width=328) (actual time=0.064..22.246 rows=1592 loops=1)
  Buffers: shared hit=7583
  CTE search_objects
    ->  Recursive Union  (cost=1.05..9733263.38 rows=4928579 width=205) (actual time=0.060..21.733 rows=1592 loops=1)
          Buffers: shared hit=7583
          ->  Index Scan using obj_link_param_id_hash1_idx3 on obj_link_param l  (cost=1.05..14607.12 rows=4409 width=82) (actual time=0.055..5.876 rows=398 loops=1)
                Index Cond: ((status_link = 1) AND (id_hash1 = ANY ('{32d48e1a87c1ca3117532e0af9fbbc82ebc6a890,***,ccad72e1e94abdf8e83470e5e0ee96be787fae43}'::bpchar[])))
                Buffers: shared hit=1999
          ->  Nested Loop Left Join  (cost=0.56..962008.47 rows=492417 width=205) (actual time=0.012..3.669 rows=398 loops=4)
                Buffers: shared hit=5584
                ->  WorkTable Scan on search_objects so  (cost=0.00..881.80 rows=44090 width=328) (actual time=0.001..0.028 rows=398 loops=4)
                ->  Index Scan using obj_link_param_id_hash1_idx3 on obj_link_param l_1  (cost=0.56..21.69 rows=11 width=82) (actual time=0.009..0.009 rows=0 loops=1592)
                      Index Cond: ((status_link = 1) AND (id_hash1 = so.id_hash))
                      Buffers: shared hit=5584
Planning time: 2.392 ms
Execution time: 80.011 ms



План выполнения с увеличенным work_mem='2GB'
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
QUERY PLAN
CTE Scan on search_objects co  (cost=7497038.40..7595609.98 rows=4928579 width=328) (actual time=0.052..9945.829 rows=1592 loops=1)
  Buffers: shared hit=261577
  CTE search_objects
    ->  Recursive Union  (cost=1.05..7497038.40 rows=4928579 width=205) (actual time=0.050..9945.377 rows=1592 loops=1)
          Buffers: shared hit=261577
          ->  Index Scan using obj_link_param_id_hash1_idx3 on obj_link_param l  (cost=1.05..14607.12 rows=4409 width=82) (actual time=0.045..5.489 rows=398 loops=1)
                Index Cond: ((status_link = 1) AND (id_hash1 = ANY ('{32d48e1a87c1ca3117532e0af9fbbc82ebc6a890,***,ccad72e1e94abdf8e83470e5e0ee96be787fae43}'::bpchar[])))
                Buffers: shared hit=1999
          ->  Hash Left Join  (cost=594192.51..738385.97 rows=492417 width=205) (actual time=2483.466..2484.559 rows=398 loops=4)
                Hash Cond: (so.id_hash = l_1.id_hash1)
                Buffers: shared hit=259578
                ->  WorkTable Scan on search_objects so  (cost=0.00..881.80 rows=44090 width=328) (actual time=0.001..0.026 rows=398 loops=4)
                ->  Hash  (cost=447771.61..447771.61 rows=11713672 width=82) (actual time=9881.227..9881.227 rows=11749175 loops=1)
                      Buckets: 2097152  Batches: 1  Memory Usage: 1399805kB
                      Buffers: shared hit=259578
                      ->  Seq Scan on obj_link_param l_1  (cost=0.00..447771.61 rows=11713672 width=82) (actual time=0.009..2427.044 rows=11749175 loops=1)
                            Filter: (status_link = 1)
                            Rows Removed by Filter: 3306311
                            Buffers: shared hit=259578
Planning time: 2.523 ms
Execution time: 10111.438 ms

...
Рейтинг: 0 / 0
06.06.2018, 10:13
| Ответить | Цитировать | Написать  
Увеличение work_mem приводит к неоптимальному плану
    #39656170
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
big-trot,

Код: sql
1.
2.
3.
                ->  Index Scan using obj_link_param_id_hash1_idx3 on obj_link_param l_1  (cost=0.56..21.69 rows=11 width=82) (actual time=0.009..0.009 rows=0 loops=1592)
                      Index Cond: ((status_link = 1) AND (id_hash1 = so.id_hash))
                      Buffers: shared hit=5584



вот эту часть надо попробовать исправить (где 11 строк), возможно оценка n_distinct для id_hash1 кривая. см. вывод
Код: sql
1.
2.
select n_distinct, null_frac from pg_stats where tablename = 'obj_link_param' and attname = 'id_hash';
select count(distinct id_hash) from obj_link_param;



влияние work_mem на план запроса объясняется легко: для hash join нужна память, которой было недостаточно в первом случае.
...
Рейтинг: 0 / 0
06.06.2018, 10:31
| Ответить | Цитировать | Написать  
Увеличение work_mem приводит к неоптимальному плану
    #39656175
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
big-trot,

У вас очень плохая оценка селективности условия (на порядок) и похоже высокий random_page_cost
(id_hash1 = ANY ('{32d48e1a87c1ca3117532e0af9fbbc82ebc6a890,***,ccad72e1e94abdf8e83470e5e0ee96be787fae43}'::bpchar[])))
(cost=1.05..14607.12 rows= 4409 width=82) (actual time=0.055..5.876 rows= 398 loops=1)
так что удачный план при низком work_mem это просто везение.

Рекомендации две
1)посмотреть что у вас стоит в random_page_cost, учитывая что по вашим планам там получается все с памяти читается - там должно быть тоже самое что в seq_page_cost или на 10% выше максимум а не 4:1 по умолчанию

2)посмотреть что у вас стоит в default_statistics_target и скорее всего поднять его для obj_link_param(id_hash)
через
alter table obj_link_param alter id_hash set statistics 1000;
analyze obj_link_param;

(возможно даже 10000).

После 2 - приведите что показывает
explain analyze select distinct id_hash from obj_link_param; чтобы понять насколько лучше планировщик стал селективность оценивать (и не стоит ли руками n_distinct прописать для obj_link_param(id_hash)).

--
Maxim Boguk
dataegret.ru
...
Рейтинг: 0 / 0
06.06.2018, 10:37
| Ответить | Цитировать | Написать  
Увеличение work_mem приводит к неоптимальному плану
    #39656211
big-trot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
big-trot
Гость
Maxim BogukРекомендации две
1)посмотреть что у вас стоит в random_page_cost, учитывая что по вашим планам там получается все с памяти читается - там должно быть тоже самое что в seq_page_cost или на 10% выше максимум а не 4:1 по умолчанию

Помогла эта рекомендация. Спасибо

Maxim BogukПосле 2 - приведите что показывает
Показываю
 
Код: sql
1.
2.
3.
4.
5.
6.
QUERY PLAN
Unique  (cost=0.56..386219.57 rows=8667322 width=41) (actual time=0.032..4407.896 rows=11984917 loops=1)
  ->  Index Only Scan using obj_link_param_id_hash1_idx on obj_link_param  (cost=0.56..348580.85 rows=15055486 width=41) (actual time=0.031..2277.672 rows=15055486 loops=1)
        Heap Fetches: 0
Planning time: 0.103 ms
Execution time: 4703.541 ms

...
Рейтинг: 0 / 0
06.06.2018, 11:09
| Ответить | Цитировать | Написать  
Увеличение work_mem приводит к неоптимальному плану
    #39656220
big-trot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
big-trot
Гость
Alexius,

Результат select n_distinct, null_frac from pg_stats where tablename = 'obj_link_param' and attname = 'id_hash';
-0.575692, 0
...
Рейтинг: 0 / 0
06.06.2018, 11:20
| Ответить | Цитировать | Написать  
7 сообщений из 7, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Увеличение work_mem приводит к неоптимальному плану
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&gotonew=1&tid=1995740]:
 0ms
get settings:
 9ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 44ms
get topic data:
 13ms
get first new msg:
 8ms
get forum data:
 3ms
get page messages:
 57ms
get tp. blocked users:
 2ms
others: 315ms
total:  473ms
созд. / загр.: 473ms
x
x
Закрыть


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