The purpose of this post is to demonstrate that Postgres follows short-circuit evaluation. That means, if you are checking two boolean values, if you are seeing if one or the other is true, and the first one is true, then you don't need to check the second one, because you know the whole statement is true. For example:
true or true: true; when we see "or", we know that because the first statement was "true", we don't need to check the second statement.
true or false: true; same answer as above, for the same reason. this statement is still true even though the second part of the statement is "false", because the first part is true and it's joined using "or".
false or true: true; need to check both because the first one was false.
false or false: false; checked both, and the final result is false.
Postgres has a command "pg_sleep(seconds)" that lets you delay execution (documentation at https://www.postgresql.org/docs/9.6/static/functions-datetime.html#FUNCTIONS-DATETIME-DELAY). Its return type is void. It can accept a decimal value as input.
select 1, pg_sleep(0);
?column? | pg_sleep
----------+----------
1 |
(1 row)
While the parts of the where clause in postgresql may execute in an unpredictable order, this example demonstrates to me that postgresql uses short-circuit evaluation:
select
((pg_sleep(1)::text)||'true')::boolean
AND ((pg_sleep(1)::text)||'true')::boolean;
?column?
----------
t
(1 row)
Time: 2149.086 ms -- took twice as long!
select
((pg_sleep(1)::text)||'true')::boolean
OR ((pg_sleep(1)::text)||'true')::boolean;
?column?
----------
t
(1 row)
Time: 1072.391 ms -- took half as long!
In the query that uses "OR" instead of "AND", the query returns after the first sleep, which is clear because it only took 1 second to execute the query. When using "AND" instead of "OR", the query executed both sleep commands and took 2 seconds to execute.
However, as described on Stack Overflow (https://stackoverflow.com/a/14944611/2821804), the ordering of statements is not predictable, so don't count on a specific statement short-circuiting the next one, because the query planner might rip your attempted optimizations right out.