Почему PostgreSQL иногда выбирает неэффективный план выполнения для простого JOIN? 

Как и в вопросе по ссылке <a href="https://qna.habr.com/q/281973" rel="nofollow">Почему PostgreSQL использует неоптимальный план выполнения для простого запроса?</a>, я сталкиваюсь с аналогичной проблемой, но предложенное решение мне не помогло. У меня есть простая таблица, структура которой следующая: <br/> <pre><code>Table "public.friends_info"
     Column      |  Type  | Collation | Nullable | Default
-----------------+--------+-----------+----------+---------
 user_id         | bigint |           |          |
 friend_id       | bigint |           |          |
 unix_time_check | bigint |           |          |
Indexes:
    "friends_info_user_id_friend_id_key" UNIQUE CONSTRAINT, btree (user_id, friend_id)
    "ix_friends_friend_id" btree (friend_id)
    "ix_friends_user_id" btree (user_id)</code></pre> <br/> Я выполняю два одинаковых запроса, но PostgreSQL выбирает для них разные планы выполнения: <br/> <pre><code>EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM friends_info LEFT JOIN i ON friends_info.friend_id = i.user_id WHERE friends_info.user_id = 1;
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1063.49..41700.28 rows=5465 width=644) (actual time=3.039..18.789 rows=88 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=447 read=1
   -&gt;  Nested Loop Left Join  (cost=63.49..40153.78 rows=2277 width=644) (actual time=0.657..2.055 rows=29 loops=3)
         Buffers: shared hit=447 read=1
         -&gt;  Parallel Bitmap Heap Scan on friends_info  (cost=62.92..20715.73 rows=2277 width=24) (actual time=0.621..0.629 rows=29 loops=3)
               Recheck
  • 29 августа 2024 г. 2:44
Ответы на вопрос 2 
Проблема неэффективных планов выполнения в PostgreSQL может быть обусловлена несколькими факторами, даже для подобных запросов. Вот некоторые из наиболее вероятных причин:

1. **Статистика**: PostgreSQL использует статистику для оценки стоимости выполнения различных операций. Если статистика устарела или недостаточно точна (например, если в таблицах регулярно добавляются/удаляются записи), оптимизатор может выбрать неэффективный план. Регулярно обновляйте статистику с помощью команды `ANALYZE`.

2. **Выборка данных**: Различия в объемах данных, подходящих под фильтры в `WHERE`, могут влиять на выбор плана. Если в одной из таблиц данных больше, чем в другой, это может привести к разному поведению планов.

3. **Размеры таблиц и индексов**: Объем данных в таблицах и индексов может меняться, и это влияет на то, какой план будет более эффективным. Если одна из таблиц значительно увеличилась в размере, это может влиять на оптимизатор.

4. **Настройки конфигурации**: Параметры конфигурации PostgreSQL, такие как `work_mem`, `effective_cache_size`, `random_page_cost`, и другие, могут влиять на то, как оптимизатор выбирает планы. Возможно, стоит поэкспериментировать с их значениями.

5. **Типы соединений и фильтров**: Разные типы соединений (например, Nested Loop, Hash Join) могут быть выбраны в зависимости от конкретных условий запроса. Иногда последовательный выбор соединения может приводить к различным планам для схожих запросов.

6. **Параллелизм**: Если в запросе задействован параллелизм (как показано в вашем выводе плана с `Gather` и `Workers Planned`), различия в доступных ресурсах или текущей загрузке системы могут вызвать выбор различных планов.

Чтобы лучше разобраться, вы можете сделать следующее:

- Запустите `ANALYZE` для обеих таблиц после проверки или изменения данных.
- Сравните статистику, используя `pg_stats` для ваших столбцов, чтобы увидеть, как различные параметры могут влиять на выбор плана.
- Используйте `EXPLAIN (ANALYZE, BUFFERS)` для более глубокого анализа планов выполнения.
- Поэкспериментируйте с настройками конфигурации, чтобы видеть, влияют ли они на выбор плана.

Если после всех этих шагов проблема все еще остается, возможно, стоит обратиться к сообществу PostgreSQL или разработать временное решение, например, использовать подсказки `SET enable_*` для принудительного выбора определенных планов.
Я выполняю 2 одинаковых запроса
 
 "WHERE friends_info.user_id = 1" vs "WHERE friends_info.friend_id= 1" 
 
 о да, одинаковые, да как бы не так. Полностью разные запросы, естественно, могут иметь различные оптимальные планы выполнения. 
 
 
Bitmap Index Scan on ix_friends_user_id  (cost=0.00..61.56 rows=5465 width=0) (actual time=0.742..0.743 rows=88 loops=1)
 
 Суть ошибки выбора плана. Какое распределение данных в таблице? Каков размер самой таблицы? Вероятно несколько пользователей занимают значительную часть таблицы и это сбивает оценку селективности. 
 Простое чуть приподнять SET STATISTICS по полю, собрать новый analyze и посмотреть на оценку числа строк. 
 
 PS: индекс ix_friends_user_id должен быть удалён как бесполезный при наличии friends_info_user_id_friend_id_key
Похожие вопросы
    Показать ещё