With PostgreSQL, I needed to define a query that would conditionally SELECT some data based on a parameter. In this statement, I have a condition that only needs to be evaluated only if another condition evaluates to TRUE.
To solve this, my first idea was to use a CASE inside a WHERE, which worked fine. For example, using a table test_table with columns id, name, and value:
SELECT
name
FROM test_table
WHERE
CASE
WHEN $1 > 10 THEN test_table.value > $1
ELSE TRUE
END
;
However, other peers suggested to use regular boolean logic instead, as it would perform faster. For example:
SELECT
name
FROM test_table
WHERE
(
$1 <= 10
OR test_table.value > $1
)
;
After testing with around 100k rows and using EXPLAIN ANALYSE, the speed difference seemed to average at about 1ms difference:
CASE: ~19.5ms Execution TimeQuestion is: Is the second approach really faster? Or was my testing flawed, and thus the results incorrect?
It is not so much that not using the case is faster all the time. The issue is that case prevents the optimizer from choosing different execution paths. It basically says that the conditions have to be executed in order, because case expressions impose ordering.
With fewer options, the resulting query plan might be slower.
There are some cases where the execution order is an advantage. This is particularly true when you know that some condition is very expensive to evaluate, so you want to be sure that other conditions are evaluated first:
where (case when x = 1 then true
when <some expensive function> then true
end)
could be more performant than:
where (x = 1) or <some expensive function>
(although in this simple example I would expect the Postgres compiler to be smart enough to do the right thing in the second case).
The second reason for avoiding case in a where clause is aesthetic. There are already sufficient boolean operators to generate just about any condition you need -- so case is syntactic sugar that usually provides no additional nutrients