I have the following query:
SELECT id,
email,
first_name as "firstName",
last_name as "lastName",
is_active as "isActive",
password,
access,
CASE
WHEN access < 3 THEN (
SELECT
CASE WHEN count(*) = 1 THEN true ELSE false END
FROM user_rating_entity ure
WHERE ure.user_id = u.id
AND ure.rating_entity_id = :re_id
)
ELSE true
END as "isResponsible"
FROM users u
WHERE u.id = :id
If access > 3
, field "isResponsible" should be directly set to true and the subquery should not be executed. I used explain analyze with both cases where access >=
and <
to 3 but I get the same output.
Why is that so?
Best Answer
There are three important parts to reading query plans here,
Sample Data
You didn't provide any sample data, so let's create some.
And, now let's run a basic query with subquery, outside of the possible range of execution.
The plan will show that the case is accompanied for, but never executed.
You can see that with (never executed) on the
Aggregate
line. However, if we set it to something likeCASE WHEN x>20 THEN (SELECT sum(x) FROM foo
you'll see a lot moreHere we can see that the Aggregate is looped through
loops=1
time. PostgreSQL realizes that it isn't a correlated subquery and it's a just a reduces it to a literal (essentially). Now let's make sure it's correlated.Now you'll see this plan
Here the key is that the aggregate has
loops=80
which itself requiresloops=80
seq scans.This is all general, but it's all I can give without your sample data, or query plans.