postgresql - Bad performance on EXISTS-clauses in functions -

postgresql - Bad performance on EXISTS-clauses in functions -

i have next function i'd utilize in sql query (postgres 9.3):

select * test_table tt has_access(tt.id, tt.login) create or replace function has_access(integer, integer) returns boolean $body$ select exists (select true test_read_access id = $1 , login = $2 ) , not exists (select true test_no_read_access id = $1 , login = $2 ) $body$

this works fine long have concern functional correctness. because query analyzer tells me, function has evaluated every row , hence exists-clauses cannot optimized expected. indeed query slow compared next query (inlining exists-clauses without select-clause):

select * test_table tt exists (select true test_read_access id = tt.id , login = tt.login ) , not exists (select true test_no_read_access id = tt.id , login = tt.login )

the intention of function has_access(id, login) grouping access rule in function , utilize in different queries. mean, possible performance:

select * test_table tt exists (select has_access(tt.id, tt.login)) create or replace function has_access(integer, integer) returns setof boolean $body$ select true test_read_access id = $1 , login = $2 $body$

but have 1 sub query on 1 table in function , not useful in case. suggestion on how in order not run performance issues?

thanks!

ok, think see problem is; function calls not optimizable, need query outside function; like

select * test_table (id,login) in (select id,login test_read_access) , (id,login) not in (select id,login test_no_read_access)

check http://sqlfiddle.com/#!12/94a02/2

function postgresql plpgsql inline-code