Как я могу оптимизировать этот запрос в Postgres

Приведенный ниже запрос требует больше времени для выполнения. Как я могу оптимизировать приведенный ниже запрос для запуска большего количества записей? Я выполнил Explain Analyze для этого запроса. Прикрепил вывод для того же.

Это был существующий запрос, созданный как представление, и для возврата результата потребовалось много времени (более часов).

Я сделал вакуум, проанализировал и переиндексировал эти 2 таблицы, но не повезло.

select st_tr.step_trail_id,
       st_tr.test_id,
       st_tr.trail_id,
       st_tr.step_name, 
       filter.regular_expression as filter_expression,
       filter.order_of_occurrence as filter_order,
       filter.match_type as filter_match_type,
       null as begins_with,
       null as ends_with,
       null as input_source,
       null as pattern_expression,
       null as pattern_matched,
       null as pattern_status,
       null as pattern_order,
       'filter' as record_type
from tab_report_step st_tr, 
     tab_report_filter filter
where st_tr.st_tr_id = filter.st_tr_id)

План запроса:

 Hash Join  (cost=446852.58..1176380.76 rows=6353676 width=489) (actual time=16641.953..47270.831 rows=6345360 loops=1)
   Buffers: shared hit=1 read=451605 dirtied=5456 written=5424, temp read=154080 written=154074
   -> Seq Scan on tab_report_filter filter  (cost=0..24482.76 rows=6353676 width=161) (actual time=0.041..8097.233 rows=6345360 loops=1)
      Buffers: shared read=179946 dirtied=4531 written=4499
   -> Hash  (cost=318817.7..318817.7 rows=4716070 width=89) (actual time=16627.291..16627.291 rows=4709040 loops=1)
      Buffers: shared hit=1 read=271656 dirtied=925 written=925, temp written=47629
        -> Seq Scan on tab_report_step st_tr  (cost=0..318817.7 rows=4716070 width=89) (actual time=0.059..10215.484 rows=4709040 loops=1)
           Buffers: shared hit=1 read=271656 dirtied=925 written=925

postgresql query-optimization postgresql-performance
person user1631072    schedule 12.02.2021    source источник
comment
Это соединение между 4,7 миллионами записей в одной таблице и 6,4 миллионами записей в другой таблице, и вам нужны все. Насколько быстро это должно быть? Он получает около 130 записей в миллисекунду, не так уж хорошо, но и не так уж плохо. Каковы ваши настройки для work_mem?   -  person Frank Heikens    schedule 12.02.2021
comment
Спасибо за ответ. work_mem установлен как 200 МБ.   -  person user1631072    schedule 12.02.2021
comment
47 секунд это точно не часы   -  person jjanes    schedule 12.02.2021
comment
Если я использую объединение на st_tr.st_tr_id = filter.st_tr_id, улучшит ли это производительность? Или есть ли лучший способ оптимизировать этот запрос?   -  person user1631072    schedule 12.02.2021

Ответы (1)


arrow_upward
0
arrow_downward

Вы не запускали VACUUM для этих таблиц. Возможно VACUUM (FULL), но точно не VACUUM.

Есть две вещи, которые можно улучшить:

  • Убедитесь, что никакие страницы не испачканы или не исписаны, пока вы их читаете. Скорее всего, это потому, что вы читаете строки впервые, а PostgreSQL устанавливает биты подсказок.

    Запуск VACUUM (без FULL) исправит это. Кроме того, если вы повторите эксперимент, вы больше не должны получать эти загаженные и записанные буферы.

  • Дайте запросу больше памяти, увеличив work_mem. Хэш не помещается в work_mem и попадает на диск, что приводит к дополнительным операциям чтения и записи на диск, что отрицательно сказывается на производительности.

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

person Laurenz Albe    schedule 12.02.2021
comment
Благодарю за ваш ответ. Да, я запустил VACUUM FULL на обеих таблицах. Попробую с ВАКУУМОМ. рабочая_память составляет 200 МБ. - person user1631072; 12.02.2021