explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RVQW : Optimization for: plan #zGcF

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.388 19.543 ↑ 1.0 1 1

GroupAggregate (cost=248.81..292.21 rows=1 width=44) (actual time=19.542..19.543 rows=1 loops=1)

  • Group Key: s.user_id, s.question_set_id, qs.question_set_id
2.          

Initplan (forGroupAggregate)

3. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on question_counts_mv qc (cost=0.00..1.01 rows=1 width=1,444) (actual time=0.008..0.009 rows=1 loops=1)

4. 0.013 17.754 ↑ 1.0 5 1

Nested Loop (cost=247.80..250.97 rows=5 width=106) (actual time=17.735..17.754 rows=5 loops=1)

5. 0.009 0.109 ↑ 1.0 1 1

Nested Loop (cost=0.28..3.33 rows=1 width=74) (actual time=0.098..0.109 rows=1 loops=1)

6. 0.062 0.062 ↑ 1.0 1 1

Index Scan using pk_user_question_set_association on user_question_set_association s (cost=0.28..2.30 rows=1 width=24) (actual time=0.055..0.062 rows=1 loops=1)

  • Index Cond: ((user_id = 1) AND (question_set_id = 2))
7. 0.038 0.038 ↑ 1.0 1 1

Seq Scan on question_sets qs (cost=0.00..1.02 rows=1 width=50) (actual time=0.035..0.038 rows=1 loops=1)

  • Filter: (question_set_id = 2)
  • Rows Removed by Filter: 1
8. 0.017 17.632 ↑ 1.0 5 1

Sort (cost=247.52..247.54 rows=5 width=44) (actual time=17.630..17.632 rows=5 loops=1)

  • Sort Key: ts.name
  • Sort Method: quicksort Memory: 18kB
9.          

Initplan (forSort)

10. 0.015 0.015 ↑ 1.0 1 1

Seq Scan on question_counts_mv qc_1 (cost=0.00..1.01 rows=1 width=1,444) (actual time=0.013..0.015 rows=1 loops=1)

11. 0.298 17.600 ↑ 1.0 5 1

GroupAggregate (cost=246.18..246.46 rows=5 width=44) (actual time=17.421..17.600 rows=5 loops=1)

  • Group Key: ts.topic_id
12. 0.019 17.302 ↑ 1.0 5 1

Sort (cost=246.18..246.19 rows=5 width=44) (actual time=17.300..17.302 rows=5 loops=1)

  • Sort Key: ts.topic_id
  • Sort Method: quicksort Memory: 18kB
13. 0.055 17.283 ↑ 1.0 5 1

Hash Right Join (cost=243.09..246.12 rows=5 width=44) (actual time=17.254..17.283 rows=5 loops=1)

  • Hash Cond: (q.topic_id = ts.topic_id)
14. 0.012 17.184 ↑ 40.0 5 1

Sort (cost=241.90..242.40 rows=200 width=55) (actual time=17.182..17.184 rows=5 loops=1)

  • Sort Key: sts.name
  • Sort Method: quicksort Memory: 18kB
15.          

Initplan (forSort)

16. 0.018 0.018 ↑ 1.0 1 1

Seq Scan on question_counts_mv qc_2 (cost=0.00..1.01 rows=1 width=1,444) (actual time=0.017..0.018 rows=1 loops=1)

17. 0.254 17.154 ↑ 40.0 5 1

HashAggregate (cost=224.75..233.25 rows=200 width=55) (actual time=17.043..17.154 rows=5 loops=1)

  • Group Key: sts.subtopic_id, q.topic_id, q.subtopic_id
18. 0.040 16.900 ↑ 40.0 5 1

Hash Left Join (cost=217.71..222.25 rows=200 width=43) (actual time=16.881..16.900 rows=5 loops=1)

  • Hash Cond: (q.subtopic_id = sts.subtopic_id)
19. 1.446 16.758 ↑ 40.0 5 1

HashAggregate (cost=216.26..218.26 rows=200 width=24) (actual time=16.745..16.758 rows=5 loops=1)

  • Group Key: q.topic_id, q.subtopic_id
20. 4.526 15.312 ↓ 2.0 1,000 1

Hash Join (cost=102.71..211.22 rows=504 width=16) (actual time=11.584..15.312 rows=1,000 loops=1)

  • Hash Cond: (q.question_id = q2.question_id)
21. 1.945 1.945 ↑ 1.0 4,000 1

Seq Scan on questions q (cost=0.00..98.00 rows=4,000 width=12) (actual time=0.016..1.945 rows=4,000 loops=1)

22. 0.969 8.841 ↓ 2.0 1,000 1

Hash (cost=96.41..96.41 rows=504 width=8) (actual time=8.841..8.841 rows=1,000 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
23. 2.337 7.872 ↓ 2.0 1,000 1

GroupAggregate (cost=0.57..91.37 rows=504 width=8) (actual time=0.150..7.872 rows=1,000 loops=1)

  • Group Key: q2.question_id
24. 2.712 5.535 ↓ 2.0 1,000 1

Merge Join (cost=0.57..81.29 rows=504 width=12) (actual time=0.121..5.535 rows=1,000 loops=1)

  • Merge Cond: (q2.question_id = aq.question_id)
25. 1.739 1.739 ↑ 1.0 2,000 1

Index Only Scan using ix_questions_question_set_id_question_id_answer_index on questions q2 (cost=0.28..44.28 rows=2,000 width=8) (actual time=0.061..1.739 rows=2,000 loops=1)

  • Index Cond: (question_set_id = 2)
  • Heap Fetches: 0
26. 1.084 1.084 ↑ 1.0 1,000 1

Index Only Scan using ix_answered_questions_user_id_q_set_id_q_id_answer_index on subscriptions_questions_association aq (cost=0.29..24.47 rows=1,009 width=8) (actual time=0.046..1.084 rows=1,000 loops=1)

  • Index Cond: ((user_id = 1) AND (question_set_id = 2))
  • Heap Fetches: 0
27. 0.050 0.102 ↑ 1.0 20 1

Hash (cost=1.20..1.20 rows=20 width=19) (actual time=0.102..0.102 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
28. 0.052 0.052 ↑ 1.0 20 1

Seq Scan on subtopics sts (cost=0.00..1.20 rows=20 width=19) (actual time=0.032..0.052 rows=20 loops=1)

29. 0.020 0.044 ↑ 1.0 5 1

Hash (cost=1.12..1.12 rows=5 width=12) (actual time=0.044..0.044 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
30. 0.024 0.024 ↑ 1.0 5 1

Seq Scan on topics ts (cost=0.00..1.12 rows=5 width=12) (actual time=0.018..0.024 rows=5 loops=1)

  • Filter: (question_set_id = 2)
  • Rows Removed by Filter: 5
31.          

SubPlan (forGroupAggregate)

32. 0.095 1.377 ↑ 1.0 1 1

Aggregate (cost=39.05..39.06 rows=1 width=8) (actual time=1.376..1.377 rows=1 loops=1)

33. 1.282 1.282 ↑ 1.0 20 1

Seq Scan on user_question_set_association centile_rank_table (cost=0.00..39.00 rows=20 width=4) (actual time=1.244..1.282 rows=20 loops=1)

  • Filter: ((score IS NOT NULL) AND (question_set_id = s.question_set_id))
  • Rows Removed by Filter: 1980
34. 0.015 0.015 ↓ 0.0 0 1

Seq Scan on tests active_test (cost=0.00..1.02 rows=1 width=1) (actual time=0.015..0.015 rows=0 loops=1)

  • Filter: ((user_id = s.user_id) AND (question_set_id = s.question_set_id))