Geek asks and answers

Does Postgresql plpgsql / sql support short circuit in where clause?

If I have the following toy request

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;

Will the first condition in the WHERE clause short-circuit the second condition, which will have a difficult runtime?

I am working on some sql that are actually part of FOR LOOP in plpgsql and I could iterate over all the records that exist in my_other_tables and then test under FOR LOOP with some_slow_func (), but I'm curious if sql, or plpgsql supports short circuit.

Some research: I looked at the Postgres mailing lists and found this SQL statement doesn't support short circuiting at all:

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

, . , . , , , ? - ?

+5
3

, WHERE .

. CTE. , IN(...), :

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;

- , , , , . 100, , :

ALTER FUNCTION funcname(argument types) cost N;

N - , , Constant Cost Constants.

+6

, , , CASE WHERE . :

SELECT *
  FROM my_tables
 WHERE CASE WHEN my_id in (SELECT my_other_id
                             FROM my_other_tables)
           AND some_slow_func(arg) BETWEEN 1 AND 2
           THEN 1 
           ELSE 0 
      END = 1;

SQL , DB. , , my_id, , , .

+2

According to the Postgresql docs and this answer by Tom Lane , the execution order of WHERE constraints is not reliable.

I think it’s best to add here that the other part of your WHERE clause is at the top of your function and is “not working fast”; those. run my_id in ( SELECT my_other_id FROM my_other_tables)in your function, and if it fails, go back there before doing heavy processing. This should help you get the same effect.

0
source

More articles:


All Articles