enigmasck enigmasck - 1 month ago 13
SQL Question

Does Postgresql plpgsql/sql support short circuiting in the where clause?

If I have the following toy query

SELECT *
FROM my_tables
WHERE my_id in (
SELECT my_other_id
FROM my_other_tables
) AND some_slow_func(arg) BETWEEN 1 AND 2;


Would the first condition in the WHERE clause short circuit the second condition which would have a complex run time?

I'm working on some sql that is actually part of a FOR LOOP in plpgsql, and I could do iterations over all records that exist in the my_other_tables, and then test within the scope of the FOR LOOP with the some_slow_func(). But I'm curious if sql supports, or plpgsql supports short circuiting.

Some Research:
I looked in the Postgres mailing lists and found this saying SQL in general doesn't support short circuiting:

http://www.postgresql.org/message-id/171423D4-9229-4D56-B06B-58D29BB50A77@yahoo.com

But one of the responses says that order can be enforced through subselects. I'm not exactly sure what he's speaking about. I know what a subselect is, but I'm not sure how order would be enforced? Could some one clarify this for me?

Answer

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;

where N is the estimated per-call cost, expressed in an arbitrary unit that should be compared to the Planner Cost Constants.