函数中EXISTS子句的性能不好

Question

我有以下函数，我想在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子句。事实上，与以下查询（在没有SELECT子句的情况下内联EXISTS子句）相比，该查询非常慢：

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$

但现在我在函数中的一个表上只有一个子查询，这在我的情况下没有用处。关于如何正确执行此操作以避免遇到性能问题，有什么建议吗？

谢谢！

Answer 1

好吧，我想我明白你的问题是什么了；函数调用是不可优化的，因此您需要在函数之外进行查询；类似的东西

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