Плохая производительность на EXISTS-предложениях в функциях

У меня есть следующая функция, которую я хотел бы использовать в sql-запросе (Postgres 9.3):

SELECT * FROM test_table tt WHERE has_access(tt.id, tt.login)

CREATE OR REPLACE FUNCTION has_access(integer, integer)
RETURNS boolean AS
$BODY$
SELECT
  EXISTS (SELECT true
            FROM test_read_access
          WHERE id = $1 and login = $2 
  )
  AND
  NOT EXISTS (SELECT true
            FROM test_no_read_access
          WHERE id = $1 and login = $2 
  )
$BODY$

Это прекрасно работает, пока мне приходится заботиться только о функциональной корректности. Поскольку анализатор запросов говорит мне, что функция должна оцениваться для каждой строки, и поэтому предложения EXISTS не могут быть оптимизированы должным образом. Действительно, запрос очень медленный по сравнению со следующим запросом (встраивание предложений EXISTS без предложения SELECT):

SELECT * FROM test_table tt WHERE 
   EXISTS (SELECT true
            FROM test_read_access
          WHERE id = tt.id and login = tt.login 
  )
  AND
  NOT EXISTS (SELECT true
            FROM test_no_read_access
          WHERE id = tt.id and login = tt.login 
  )

Назначение функции has_access(id, login) состоит в том, чтобы сгруппировать некоторое правило доступа в функции и затем использовать его в разных запросах. Я имею в виду, что можно сделать что-то подобное, чтобы получить хорошую производительность:

SELECT * FROM test_table tt WHERE EXISTS (select has_access(tt.id, tt.login))

CREATE OR REPLACE FUNCTION has_access(integer, integer)
RETURNS SETOF boolean AS
$BODY$
SELECT true
   FROM test_read_access
WHERE id = $1 and login = $2 
$BODY$

Но теперь у меня есть только один подзапрос к одной таблице в функции, и в моем случае это бесполезно. Любое предложение о том, как сделать это правильно, чтобы не столкнуться с проблемами производительности?

Спасибо!


postgresql function plpgsql inline-code
person reinoslav    schedule 03.11.2014    source источник
comment
EXISTS обычно приводит к коррелированным подзапросам, которые СУБД труднее оптимизировать; использование IN/NOT IN, как правило, более эффективно.   -  person okaram    schedule 04.11.2014
comment
@okaram: совершенно неправильно. Обратите внимание, что в данном случае нет коррелированного подзапроса, так как нет основного запроса.   -  person wildplasser    schedule 04.11.2014
comment
@wildplasser, запрос в функции не коррелирован, а тот, что выше, есть (но я не понял, что проблема была в функции, когда писал комментарий :)   -  person okaram    schedule 04.11.2014

Ответы (1)


arrow_upward
0
arrow_downward

ОК, кажется, я понимаю, в чем твоя проблема; вызовы функций не оптимизируются, поэтому вам нужно выполнять запрос вне функции; что-то вроде

SELECT *
  FROM test_table
WHERE (id,login) IN (SELECT id,login FROM test_read_access)
  AND (id,login) NOT IN (SELECT id,login FROM test_no_read_access)

Проверьте http://sqlfiddle.com/#!12/94a02/2.

person okaram    schedule 04.11.2014
comment
Нет причин предпочесть NOT IN(...) здесь; NOT EXISTS(...) работает так же хорошо, а может быть, даже лучше. Проблема производительности в OQ заключается в вызове функции, который не может быть разбит оптимизатором. - person wildplasser; 04.11.2014
comment
@wildplasser Теперь я понимаю, что проблема в вызове функции; однако обычно запросы в/не в запросах легче оптимизировать, верно? EXISTS, как правило, выполняют коррелированные подзапросы (необходимо ссылаться на внешнюю строку внутри запроса), что сложнее для оптимизатора. - person okaram; 04.11.2014
comment
Практически во всех моих многочисленных тестах в Postgres за последние годы NOT EXISTS оказывался быстрее, чем NOT IN. Кроме того, NOT IN демонстрирует сложное поведение со значениями NULL. Подробнее здесь. Возможны изменения в предстоящей странице 9.4. Не говорю, что есть, но и не ожидал. Просто еще не тестил. - person Erwin Brandstetter; 05.11.2014