explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0T8

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.349 17.631 ↑ 1.0 1 1

GroupAggregate (cost=228.23..234.12 rows=1 width=44) (actual time=17.630..17.631 rows=1 loops=1)

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

CTE subtopic_breakdown

3. 1.592 16.135 ↑ 40.0 5 1

HashAggregate (cost=193.68..195.68 rows=200 width=24) (actual time=16.119..16.135 rows=5 loops=1)

  • Group Key: q.topic_id, q.subtopic_id
4. 3.026 14.543 ↓ 4.0 1,000 1

Merge Join (cost=0.85..191.16 rows=252 width=16) (actual time=0.180..14.543 rows=1,000 loops=1)

  • Merge Cond: (q.question_id = q2.question_id)
5. 3.279 3.279 ↑ 1.0 2,000 1

Index Scan using ix_questions_question_set_id_question_id_answer_index on questions q (cost=0.28..85.97 rows=2,000 width=12) (actual time=0.044..3.279 rows=2,000 loops=1)

  • Index Cond: (question_set_id = 2)
6. 2.465 8.238 ↓ 2.0 1,000 1

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

  • Group Key: q2.question_id
7. 2.802 5.773 ↓ 2.0 1,000 1

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

  • Merge Cond: (q2.question_id = aq.question_id)
8. 1.802 1.802 ↑ 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.049..1.802 rows=2,000 loops=1)

  • Index Cond: (question_set_id = 2)
  • Heap Fetches: 0
9. 1.169 1.169 ↑ 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.041..1.169 rows=1,000 loops=1)

  • Index Cond: ((user_id = 1) AND (question_set_id = 2))
  • Heap Fetches: 0
10.          

Initplan (forGroupAggregate)

11. 0.012 0.012 ↑ 1.0 1 1

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

12. 0.013 17.113 ↑ 1.0 5 1

Nested Loop (cost=31.54..34.71 rows=5 width=106) (actual time=17.098..17.113 rows=5 loops=1)

13. 0.014 0.120 ↑ 1.0 1 1

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

14. 0.069 0.069 ↑ 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.065..0.069 rows=1 loops=1)

  • Index Cond: ((user_id = 1) AND (question_set_id = 2))
15. 0.037 0.037 ↑ 1.0 1 1

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

  • Filter: (question_set_id = 2)
  • Rows Removed by Filter: 1
16. 0.023 16.980 ↑ 1.0 5 1

Sort (cost=31.26..31.28 rows=5 width=44) (actual time=16.978..16.980 rows=5 loops=1)

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

Initplan (forSort)

18. 0.011 0.011 ↑ 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.010..0.011 rows=1 loops=1)

19. 0.280 16.946 ↑ 1.0 5 1

GroupAggregate (cost=29.92..30.20 rows=5 width=44) (actual time=16.767..16.946 rows=5 loops=1)

  • Group Key: ts.topic_id
20. 0.028 16.666 ↑ 1.0 5 1

Sort (cost=29.92..29.93 rows=5 width=44) (actual time=16.664..16.666 rows=5 loops=1)

  • Sort Key: ts.topic_id
  • Sort Method: quicksort Memory: 18kB
21. 0.048 16.638 ↑ 1.0 5 1

Hash Right Join (cost=26.83..29.86 rows=5 width=44) (actual time=16.609..16.638 rows=5 loops=1)

  • Hash Cond: (subtopic_breakdown.topic_id = ts.topic_id)
22. 0.012 16.542 ↑ 40.0 5 1

Sort (cost=25.64..26.14 rows=200 width=55) (actual time=16.540..16.542 rows=5 loops=1)

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

Initplan (forSort)

24. 0.016 0.016 ↑ 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.014..0.016 rows=1 loops=1)

25. 0.238 16.514 ↑ 40.0 5 1

HashAggregate (cost=8.49..16.99 rows=200 width=55) (actual time=16.407..16.514 rows=5 loops=1)

  • Group Key: sts.subtopic_id, subtopic_breakdown.topic_id, subtopic_breakdown.subtopic_id
26. 0.037 16.276 ↑ 40.0 5 1

Hash Left Join (cost=1.45..5.99 rows=200 width=43) (actual time=16.242..16.276 rows=5 loops=1)

  • Hash Cond: (subtopic_breakdown.subtopic_id = sts.subtopic_id)
27. 16.153 16.153 ↑ 40.0 5 1

CTE Scan on subtopic_breakdown (cost=0.00..4.00 rows=200 width=24) (actual time=16.126..16.153 rows=5 loops=1)

28. 0.036 0.086 ↑ 1.0 20 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
29. 0.050 0.050 ↑ 1.0 20 1

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

30. 0.021 0.048 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
31. 0.027 0.027 ↑ 1.0 5 1

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

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

SubPlan (forGroupAggregate)

33. 0.081 0.143 ↑ 1.0 1 1

Aggregate (cost=1.54..1.55 rows=1 width=8) (actual time=0.142..0.143 rows=1 loops=1)

34. 0.062 0.062 ↑ 1.0 20 1

Index Only Scan using ix_user_question_set_association_centile_rank on user_question_set_association centile_rank_table (cost=0.14..1.49 rows=20 width=4) (actual time=0.048..0.062 rows=20 loops=1)

  • Index Cond: (question_set_id = s.question_set_id)
  • Heap Fetches: 0
35. 0.014 0.014 ↓ 0.0 0 1

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

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