I have the following query:

   first_name as "firstName",
   last_name as "lastName",
   is_active as "isActive",
     WHEN access < 3 THEN (
         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?

  • 1
    Never write then true else false end. Just write the condition. select count(*) = 1 ... Jan 7, 2017 at 18:57
  • Can you provide sample data or your explain plan for both cases. Jan 7, 2017 at 18:59
  • 2
    @Evan: That advice happens to apply for the example with count(*) which cannot be null. But "never" is bad advice, it fails for nullable expression, for which the given CASE folds null to false. More elegantly expressed as COALESCE (<boolean-expression>, false). Jan 8, 2017 at 3:29
  • Sure, if you want that. COALESCE works great. ;) My point was more directed at not writing then true else false end seems like an antipattern. Though it's just style. Jan 8, 2017 at 4:09
  • @EvanCarroll: But then true else false end is not just style, it folds null to false. (Only count(*) is never null to begin with.) Jan 8, 2017 at 4:23

2 Answers 2


There are three important parts to reading query plans here,

  • Did it run. If so,
  • How many times?
  • Was it correlated?

Sample Data

You didn't provide any sample data, so let's create some.

SELECT x FROM generate_series(1,100) AS x;

And, now let's run a basic query with subquery, outside of the possible range of execution.

  (CASE WHEN x>200 THEN (SELECT sum(x) FROM foo) END)
FROM foo;

The plan will show that the case is accompanied for, but never executed.

 Seq Scan on foo  (cost=2.26..4.51 rows=100 width=4) (actual time=0.017..0.047 rows=100 loops=1)
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=2.25..2.26 rows=1 width=4) (never executed)
           ->  Seq Scan on foo foo_1  (cost=0.00..2.00 rows=100 width=4) (never executed)
 Planning time: 0.101 ms
 Execution time: 0.118 ms
(6 rows)

You can see that with (never executed) on the Aggregate line. However, if we set it to something like CASE WHEN x>20 THEN (SELECT sum(x) FROM foo you'll see a lot more

 Seq Scan on foo  (cost=2.26..4.51 rows=100 width=4) (actual time=0.020..0.095 rows=100 loops=1)
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=2.25..2.26 rows=1 width=4) (actual time=0.043..0.043 rows=1 loops=1)
           ->  Seq Scan on foo foo_1  (cost=0.00..2.00 rows=100 width=4) (actual time=0.006..0.019 rows=100 loops=1)
 Planning time: 0.092 ms
 Execution time: 0.158 ms
(6 rows)

Here 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.

  (CASE WHEN x>20 THEN (SELECT sum(f2.x)+f1.x FROM foo AS f2) END)
FROM foo AS f1;

Now you'll see this plan

 Seq Scan on foo f1  (cost=0.00..228.50 rows=100 width=4) (actual time=0.020..3.210 rows=100 loops=1)
   SubPlan 1
     ->  Aggregate  (cost=2.25..2.26 rows=1 width=4) (actual time=0.038..0.038 rows=1 loops=80)
           ->  Seq Scan on foo f2  (cost=0.00..2.00 rows=100 width=4) (actual time=0.005..0.017 rows=100 loops=80)
 Planning time: 0.104 ms
 Execution time: 3.272 ms

Here the key is that the aggregate has loops=80 which itself requires loops=80 seq scans.

This is all general, but it's all I can give without your sample data, or query plans.

  • This is the answer, good explanation I didn't see the never executed (used graphical representation), I upvoted but got less than 15 rep. Jan 7, 2017 at 19:38
  • Mark as chosen if you're satisfied. Jan 7, 2017 at 19:54

Evan already pointed out that you may have overlooked the (never executed) in the output of EXPLAIN ANALYZE.

A cleaner and more versatile way to write your query in modern Postgres would be with a LATERAL subquery (not necessarily faster):

SELECT id, email
     , first_name AS "firstName"
     , last_name  AS "lastName"
     , is_active  AS "isActive"
     , password, access
     , COALESCE(ure.resp, true) AS "isResponsible"
FROM  users u
   SELECT (count(*) = 1) AS resp
   FROM   user_rating_entity
   WHERE  user_id = u.id  -- lateral reference
   AND    rating_entity_id = :re_id
   ) ure ON u.access < 3
WHERE  u.id = :id;

Like I commented, COALESCE() is the more elegant replacement for your particular CASE expression.

But you said count(*) is never null? Then why COALESCE()?

Even though count(*) itself is never null (0 for "no rows"), the LEFT JOIN still produces null where the join condition is no met. And that's the whole point here: Postgres does not count for outer rows where the join condition u.access < 3 is not met. We get null and fold that to true, according to your original query.

As we can see in Evan's answer, count(*) triggers a sequential scan in user_rating_entity for every row in users that qualifies. OK for small tables or very few users, problematic for big tables.

It can be substantially faster with a matching index to allow index scans:

CREATE INDEX foo ON user_rating_entity (user_id, rating_entity_id)

And if there are more than a few rows per count, there are faster query techniques. But that's stretching the scope of this question ...



     , CASE WHEN ure.ct <> 1 THEN false ELSE true END AS "isResponsible"
    -- COALESCE(NOT ure.ct <> 1, true)  -- equivalent
    -- ure.ct = 1                       -- NOT equivalent, misses NULL case
FROM  users u
   SELECT count(*) AS ct
   FROM   ...
   ) ure ON u.access < 3
WHERE  u.id = :id;

Same result.

  • In your case, shouldn't it be COALESCE(ure.resp, false) AS "isResponsible" Jan 8, 2017 at 19:33
  • 1
    @EvanCarroll: The original query has else truein the outer CASE expression, so COALESCE(ure.resp, true) is the equivalent. Jan 9, 2017 at 2:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.