Ignore answer_sections without answers

The view will now only count sections that have answers enabled.

Performance

The new query is a bit faster compared to the old one, probably because it uses only a single subquery. The following Query plans are using the data from staging:

Old Query

 EXPLAIN ANALYZE SELECT row_number() OVER () as id,
    ae.id AS exam_id,
    (SELECT COUNT(*) FROM answers_answersection aas WHERE (aas.exam_id = ae.id)),
    (SELECT COUNT(*) FROM answers_answersection aas WHERE (aas.exam_id = ae.id AND EXISTS (
        SELECT aa.id FROM answers_answer aa WHERE aa.answer_section_id = aas.id
    )))
FROM answers_exam ae;
                                                                                         QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=0.00..125033.01 rows=939 width=28) (actual time=0.034..23.130 rows=940 loops=1)
   ->  Seq Scan on answers_exam ae  (cost=0.00..30.39 rows=939 width=4) (actual time=0.007..0.184 rows=940 loops=1)
   SubPlan 1
     ->  Aggregate  (cost=4.57..4.58 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=940)
           ->  Index Only Scan using answers_answersection_exam_id_750cb327 on answers_answersection aas  (cost=0.29..4.53 rows=14 width=0) (actual time=0.001..0.003 rows=13 loops=940)
                 Index Cond: (exam_id = ae.id)
                 Heap Fetches: 200
   SubPlan 2
     ->  Aggregate  (cost=128.53..128.54 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=940)
           ->  Nested Loop Semi Join  (cost=0.57..128.51 rows=5 width=0) (actual time=0.010..0.019 rows=5 loops=940)
                 ->  Index Scan using answers_answersection_exam_id_750cb327 on answers_answersection aas_1  (cost=0.29..30.80 rows=14 width=4) (actual time=0.001..0.004 rows=13 loops=940)
                       Index Cond: (exam_id = ae.id)
                 ->  Index Only Scan using answers_answer_answer_section_id_77cdd7af on answers_answer aa  (cost=0.28..7.16 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=12614)
                       Index Cond: (answer_section_id = aas_1.id)
                       Heap Fetches: 4289
 Planning Time: 0.288 ms
 Execution Time: 23.224 ms
(17 rows)

New Query

EXPLAIN ANALYZE SELECT ae.id, ae.id, COUNT(aas.id), COUNT(sub.answer_section_id)
        FROM answers_exam ae
        INNER JOIN answers_answersection aas ON aas.exam_id = ae.id
        LEFT JOIN (
          SELECT answer_section_id
          FROM answers_answer aa
          GROUP BY aa.answer_section_id
        ) sub ON sub.answer_section_id = aas.id
        WHERE aas.has_answers
        GROUP BY ae.id;
                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=1084.94..1094.33 rows=939 width=24) (actual time=13.048..13.262 rows=898 loops=1)
   Group Key: ae.id
   ->  Hash Left Join  (cost=612.55..990.72 rows=12562 width=12) (actual time=3.563..10.756 rows=12569 loops=1)
         Hash Cond: (aas.id = aa.answer_section_id)
         ->  Hash Join  (cost=42.13..387.31 rows=12562 width=8) (actual time=0.234..5.212 rows=12569 loops=1)
               Hash Cond: (aas.exam_id = ae.id)
               ->  Seq Scan on answers_answersection aas  (cost=0.00..312.06 rows=12562 width=8) (actual time=0.008..2.546 rows=12569 loops=1)
                     Filter: has_answers
                     Rows Removed by Filter: 45
               ->  Hash  (cost=30.39..30.39 rows=939 width=4) (actual time=0.220..0.221 rows=940 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 42kB
                     ->  Seq Scan on answers_exam ae  (cost=0.00..30.39 rows=939 width=4) (actual time=0.004..0.127 rows=940 loops=1)
         ->  Hash  (cost=516.84..516.84 rows=4287 width=4) (actual time=3.320..3.320 rows=4289 loops=1)
               Buckets: 8192  Batches: 1  Memory Usage: 215kB
               ->  HashAggregate  (cost=431.10..473.97 rows=4287 width=4) (actual time=2.198..2.754 rows=4289 loops=1)
                     Group Key: aa.answer_section_id
                     ->  Seq Scan on answers_answer aa  (cost=0.00..418.68 rows=4968 width=4) (actual time=0.004..0.789 rows=4969 loops=1)
 Planning Time: 0.388 ms
 Execution Time: 13.368 ms
(19 rows)