If I have the following toy query
WHERE my_id in (
) AND some_slow_func(arg) BETWEEN 1 AND 2;
As documented, the evaluation order in a WHERE clause is supposed to be unpredictable.
It's different with subqueries. With current versions, the simplest and common technique to drive the evaluation order is to write a subquery in a CTE. To make sure that the
IN(...) is evaluated first, your code could be written as:
WITH subquery AS (select * from my_tables WHERE my_id in (SELECT my_other_id FROM my_other_tables) ) SELECT * FROM subquery WHERE some_slow_func(arg) BETWEEN 1 AND 2;
Something else that you may tweak is the cost of your function to signal to the optimizer that it's slow. The default cost for a function is
100, and it can be altered with a statement like:
ALTER FUNCTION funcname(argument types) cost N;
N is the estimated per-call cost, expressed in an arbitrary unit that should be compared to the Planner Cost Constants.