Простой запрос WHERE EXISTS ORDER BY очень медленный в PostrgeSQL

У меня есть очень простой запрос, сгенерированный моей ORM (Entity Framework Core):

SELECT *
 FROM "table1" AS "t1"
 WHERE EXISTS (
     SELECT 1
     FROM "table2" AS "t2"
     WHERE ("t2"."is_active" = TRUE) AND ("t1"."table2_id" = "t2"."id"))
 ORDER BY "t1"."table2_id"
  1. Есть 2 записи is_active. Другие задействованные столбцы («id») являются первичными ключами. Запрос возвращает ровно 4 строки.
  2. Таблица 1 составляет 96 миллионов записей.
  3. Таблица 2 составляет 30 миллионов записей.
  4. 3 столбца, участвующие в этом запросе, индексируются (is_active, id, table2_id).
  5. Код C#/LINQ, создающий этот простой запрос, выглядит следующим образом: Table2.Where(t => t.IsActive).Include(t => t.Table1).ToList();`
  6. SET STATISTICS 10000 было установлено для всех трех столбцов.
  7. VACUUM FULL ANALYZE был запущен на обеих таблицах.

БЕЗ предложения ORDER BY запрос возвращается в течение нескольких миллисекунд, и я не ожидаю ничего другого для возврата 4 записей. ОБЪЯСНИТЕ вывод:

Nested Loop  (cost=1.13..13.42 rows=103961024 width=121)
  ->  Index Scan using table2_is_active_idx on table2  (cost=0.56..4.58 rows=1 width=8)
        Index Cond: (is_active = true)
        Filter: is_active
  ->  Index Scan using table1_table2_id_fkey on table1 t1 (cost=0.57..8.74 rows=10 width=121)
        Index Cond: (table2_id = table1.id)

С предложением ORDER BY выполнение запроса занимает 5 минут! ОБЪЯСНИТЕ вывод:

Merge Semi Join  (cost=10.95..4822984.67 rows=103961040 width=121)
  Merge Cond: (t1.table2_id = t2.id)
  ->  Index Scan using table1_table2_id_fkey on table1 t1  (cost=0.57..4563070.61 rows=103961040 width=121)
  ->  Sort  (cost=4.59..4.59 rows=2 width=8)
        Sort Key: t2.id
        ->  Index Scan using table2_is_active_idx on table2 a  (cost=0.56..4.58 rows=2 width=8)
              Index Cond: (is_active = true)
              Filter: is_active

Внутреннее, первое сканирование индекса должно возвращать не более 2 строк. Тогда внешнее, второе сканирование индекса не имеет смысла с его стоимостью 4563070 и 103961040 строк. Он должен сопоставить только 2 строки в table2 с 4 строками в table1!

Это очень простой запрос, возвращающий очень мало записей. Почему Postgres не может выполнить его должным образом?


postgresql entity-framework-core sql-execution-plan postgres-9.6 partial-index
person olivierr91    schedule 23.07.2017    source источник
comment
Вы должны включить оба плана объяснения.   -  person Jakub Kania    schedule 23.07.2017

Ответы (3)


arrow_upward
3
arrow_downward

Хорошо, я решил свою проблему самым неожиданным образом. Я обновил Postgresql с 9.6.1 до 9.6.3. И это было все. После перезапуска службы план объяснения теперь выглядел хорошо, и на этот раз запрос выполнялся нормально. Я ничего не менял, ни нового индекса, ничего. Единственное объяснение, которое я могу придумать, это то, что в 9.6.1 была ошибка планировщика запросов, которая была решена в 9.6.3. Спасибо всем за ваши ответы!

person olivierr91    schedule 23.07.2017
comment
Бага скорее всего нет. Планировщик запросов очень сложен и не всегда принимает правильные решения. Нет гарантии, что ваш запрос будет нормально работать через несколько недель или после другого обновления. - person Jakub Kania; 24.07.2017
comment
@JakubKania: В целом верно. Но так уж получилось, что в 9.6.1 была ошибка, которая подходит к делу. А для частот значений, которые очищаются, Postgres никогда снова не примет такого неправильного решения после исправления ошибки и после того, как частичный индекс будет на месте. - person Erwin Brandstetter; 24.07.2017

arrow_upward
3
arrow_downward

Добавьте индекс:

CREATE INDEX _index 
ON table2 
USING btree (id) 
WHERE is_active IS TRUE;

И перепишите запрос следующим образом

SELECT table1.*
FROM table2
INNER JOIN table1 ON (table1.table2_id = table2.id)
WHERE table2.is_active IS TRUE 
ORDER BY table2.id

Необходимо учитывать, что "is_active IS TRUE" и "is_active = TRUE" обрабатываются PostgreSQL по-разному. Таким образом, выражение в предикате индекса и запрос должны совпадать.

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

CREATE INDEX _index 
ON table2 
USING btree (id) 
WHERE is_active = TRUE;
person Dima Pavlov    schedule 23.07.2017
comment
Спасибо за ваш ответ, это хорошая идея, и я не знал, что мы можем делать такие частичные индексы. Моя проблема была решена путем обновления Postgres до последней второстепенной версии, это последнее, что я думал, что это исправит. - person olivierr91; 23.07.2017
comment
Интересно, вы можете обновить пост с новым планом объяснения? - person Dima Pavlov; 23.07.2017

arrow_upward
2
arrow_downward

Ваше предположение верно, в Postgres 9.6.1 есть ошибка, которая точно соответствует вашему варианту использования. И обновление было правильным решением. Обновление до последней версии всегда правильный поступок.

Цитирование примечаний к выпуску Postgres 9.6.2:

  • Исправление оценки избирательности соединения на основе внешнего ключа для полусоединений и антисоединений, а также случаев наследования (Том Лейн)

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

Вы все равно должны создать этот частичный индекс, как советовал Дима. Но будьте проще:

is_active = TRUE и is_active IS TRUE немного отличаются тем, что второй вместо этого возвращает FALSE из NULL для ввода NULL. Но все это не имеет значения в предложении WHERE, где подходит только TRUE. И оба выражения — просто шум. В Postgres вы можете напрямую использовать значения boolean:

CREATE INDEX t2_id_idx ON table2 (id) WHERE is_active;  -- that's all

И не переписывайте свой запрос с помощью LEFT JOIN. Это добавит строки, состоящие из значений NULL, к результату для «активных» строк в table2 без каких-либо братьев и сестер в table1. Чтобы соответствовать вашей текущей логике, это должно быть [INNER] JOIN:

SELECT t1.*
FROM   table2 t2
JOIN   table1 t1 ON t1.table2_id = t2.id  -- and no parentheses needed
WHERE  t2.is_active  -- that's all
ORDER  BY t1.table2_id;

Но нет необходимости переписывать ваш запрос таким образом. Полусоединение EXISTS у вас такое же хорошее. Результаты в том же плане запроса, как только у вас есть частичный индекс.

SELECT *
FROM   table1 t1
WHERE  EXISTS (
   SELECT 1 FROM table2
   WHERE  is_active  -- that's all
   WHERE  id = t1.table2_id
   )
ORDER  BY table2_id;

Кстати, поскольку вы исправили ошибку путем обновления и после того, как вы создали этот частичный индекс (и запустили ANALYZE или VACUUM ANALYZE для таблицы хотя бы один раз — или автоочистка сделала это за вас), вы никогда снова получите плохой план запроса для этого, так как Postgres поддерживает отдельные оценки для частичного индекса, которые однозначны для ваших чисел. Подробности:

person Erwin Brandstetter    schedule 24.07.2017