Postgres НЕ в исполнении

Привет какие-нибудь идеи, как ускорить этот запрос?

вход

EXPLAIN SELECT entityid FROM entity e

LEFT JOIN level1entity l1 ON l.level1id = e.level1_level1id
LEFT JOIN level2entity l2 ON l2.level2id = l1.level2_level2id
WHERE 

l2.userid = 'a987c246-65e5-48f6-9d2d-a7bcb6284c8f' 
AND 
(entityid NOT IN 
(1377776,1377792,1377793,1377794,1377795,1377796... 50000 ids)
)

Выход

Nested Loop  (cost=0.00..1452373.79 rows=3865 width=8)
  ->  Nested Loop  (cost=0.00..8.58 rows=1 width=8)
        Join Filter: (l1.level2_level2id = l2.level2id)
        ->  Seq Scan on level2entity l2  (cost=0.00..3.17 rows=1 width=8)
              Filter: ((userid)::text = 'a987c246-65e5-48f6-9d2d-a7bcb6284c8f'::text)
        ->  Seq Scan on level1entity l1  (cost=0.00..4.07 rows=107 width=16)
  ->  Index Scan using fk_fk18edb1cfb2a41235_idx on entity e  (cost=0.00..1452086.09 rows=22329 width=16)
        Index Cond: (level1_level1id = l1.level1id)

Хорошо, здесь упрощенная версия, соединения не являются узким местом

SELECT enitityid FROM 
(SELECT enitityid FROM enitity e LIMIT 5000) a

WHERE
(enitityid NOT IN 
(1377776,1377792,1377793,1377794,1377795, ... 50000 ids)
)

проблема в том, чтобы найти энты, у которых нет ни одного из этих идентификаторов

EXPLAIN

Subquery Scan on a  (cost=0.00..312667.76 rows=1 width=8)
  Filter: (e.entityid <> ALL ('{1377776,1377792,1377793,1377794, ... 50000 ids}'::bigint[]))
  ->  Limit  (cost=0.00..111.51 rows=5000 width=8)
        ->  Seq Scan on entity e  (cost=0.00..29015.26 rows=1301026 width=8)
Источник

4 ответа

Решение

Огромный IN Список очень неэффективен. PostgreSQL в идеале должен идентифицировать его и превратить в отношение, к которому он выполняет анти-объединение, но на этом этапе планировщик запросов не знает, как это сделать, и время планирования, необходимое для идентификации этого случая, будет стоить каждый запрос, который использования NOT IN разумно, так что это должна быть очень дешевая проверка. Смотрите этот ранее гораздо более подробный ответ по теме.

Как писал Дэвид Олдридж, это лучше всего решить, превратив его в анти-объединение. Я написал бы это как соединение по VALUES список просто потому, что PostgreSQL очень быстро разбирает VALUES списки в отношения, но эффект тот же:

SELECT entityid 
FROM entity e
LEFT JOIN level1entity l1 ON l.level1id = e.level1_level1id
LEFT JOIN level2entity l2 ON l2.level2id = l1.level2_level2id
LEFT OUTER JOIN (
    VALUES
    (1377776),(1377792),(1377793),(1377794),(1377795),(1377796)
) ex(ex_entityid) ON (entityid = ex_entityid)
WHERE l2.userid = 'a987c246-65e5-48f6-9d2d-a7bcb6284c8f' 
AND ex_entityid IS NULL;

Для достаточно большого набора значений вам лучше создать временную таблицу, COPYвводя в него ценности, создавая PRIMARY KEY на этом, и присоединяясь к этому.

Дополнительные возможности изучены здесь:

/questions/14743786/sql-kogda-delo-dohodit-do-ne-in-i-ne-ravno-chto-yavlyaetsya-bolee-effektivnyim-i-pochemu/14743794#14743794

Источник

Вы можете получить лучший результат, если сможете переписать запрос, чтобы использовать хеш-анти-объединение.

Что-то вроде:

with exclude_list as (
  select unnest(string_to_array('1377776,1377792,1377793,1377794,1377795, ...',','))::integer entity_id)
select entity_id
from   entity left join exclude_list on entity.entity_id = exclude_list.entity_id
where  exclude_list.entity_id is null;
Источник

Хорошо, мое решение было

  • выбрать все объекты
  • оставьте присоединение ко всем объектам, у которых есть один из идентификаторов (без учета не быстрее) в entityid
  • выбрать все строки, где объединенный выбор равен NULL

как объяснено в

http://blog.hagander.net/archives/66-Speeding-up-NOT-IN.html

Источник

Так как вам требуется запись level2entity из-за того, что вы проверяете условие where для определенного идентификатора пользователя "l2.userid = " Вы должны превратить ваш "LEFT JOIN level2entity" в "INNER JOIN level2entity"

INNER JOIN level2entity l2 ON l2.level2id = l1.level2_level2id AND l2.userid = 'a987c246-65e5-48f6-9d2d-a7bcb6284c8f'

Надеемся, что это отфильтрует вашу сущность, так что у вашего NOT IN будет меньше работы.

Источник
Другие вопросы по тегам