3

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?

  • 1
    Never write then true else false end. Just write the condition. select count(*) = 1 ... – Evan Carroll Jan 7 '17 at 18:57
  • Can you provide sample data or your explain plan for both cases. – Evan Carroll Jan 7 '17 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). – Erwin Brandstetter Jan 8 '17 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. – Evan Carroll Jan 8 '17 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.) – Erwin Brandstetter Jan 8 '17 at 4:23
5

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.

CREATE TABLE foo AS
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.

EXPLAIN ANALYZE
SELECT
  x,
  (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.

EXPLAIN ANALYZE
SELECT
  x,
  (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. – Oliver Gibson Jan 7 '17 at 19:38
  • Mark as chosen if you're satisfied. – Evan Carroll Jan 7 '17 at 19:54
5

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
LEFT  JOIN LATERAL (
   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 ...

Related:


Or:

SELECT ...
     , 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
LEFT  JOIN LATERAL (
   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" – Evan Carroll Jan 8 '17 at 19:33
  • 1
    @EvanCarroll: The original query has else truein the outer CASE expression, so COALESCE(ure.resp, true) is the equivalent. – Erwin Brandstetter Jan 9 '17 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.