explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9pgD : Optimization for: plan #0T8

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.367 12.113 ↑ 1.0 1 1

GroupAggregate (cost=137.22..144.26 rows=1 width=44) (actual time=12.112..12.113 rows=1 loops=1)

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

CTE subtopic_breakdown

3. 1.537 10.707 ↑ 40.0 5 1

HashAggregate (cost=102.66..104.66 rows=200 width=24) (actual time=10.697..10.707 rows=5 loops=1)

  • Group Key: q.topic_id, q.subtopic_id
4. 2.704 9.170 ↓ 2.0 1,000 1

GroupAggregate (cost=0.57..92.74 rows=496 width=16) (actual time=0.114..9.170 rows=1,000 loops=1)

  • Group Key: q.question_id
5. 2.822 6.466 ↓ 2.0 1,000 1

Merge Join (cost=0.57..82.82 rows=496 width=20) (actual time=0.085..6.466 rows=1,000 loops=1)

  • Merge Cond: (q.question_id = aq.question_id)
6. 2.494 2.494 ↑ 1.0 2,000 1

Index Only Scan using ix_questions_question_set_question_topic_subtopic_answer_index on questions q (cost=0.28..46.28 rows=2,000 width=16) (actual time=0.035..2.494 rows=2,000 loops=1)

  • Index Cond: (question_set_id = 2)
  • Heap Fetches: 0
7. 1.150 1.150 ↓ 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.13 rows=992 width=8) (actual time=0.038..1.150 rows=1,000 loops=1)

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

Initplan (forGroupAggregate)

9. 0.010 0.010 ↑ 1.0 1 1

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

10. 0.013 11.600 ↑ 1.0 5 1

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

11. 0.010 0.112 ↑ 1.0 1 1

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

12. 0.070 0.070 ↑ 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.066..0.070 rows=1 loops=1)

  • Index Cond: ((user_id = 1) AND (question_set_id = 2))
13. 0.032 0.032 ↑ 1.0 1 1

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

  • Filter: (question_set_id = 2)
  • Rows Removed by Filter: 1
14. 0.021 11.475 ↑ 1.0 5 1

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

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

Initplan (forSort)

16. 0.012 0.012 ↑ 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.012 rows=1 loops=1)

17. 0.275 11.442 ↑ 1.0 5 1

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

  • Group Key: ts.topic_id
18. 0.016 11.167 ↑ 1.0 5 1

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

  • Sort Key: ts.topic_id
  • Sort Method: quicksort Memory: 18kB
19. 0.041 11.151 ↑ 1.0 5 1

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

  • Hash Cond: (subtopic_breakdown.topic_id = ts.topic_id)
20. 0.006 11.072 ↑ 40.0 5 1

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

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

Initplan (forSort)

22. 0.013 0.013 ↑ 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.012..0.013 rows=1 loops=1)

23. 0.229 11.053 ↑ 40.0 5 1

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

  • Group Key: sts.subtopic_id, subtopic_breakdown.topic_id, subtopic_breakdown.subtopic_id
24. 0.024 10.824 ↑ 40.0 5 1

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

  • Hash Cond: (subtopic_breakdown.subtopic_id = sts.subtopic_id)
25. 10.725 10.725 ↑ 40.0 5 1

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

26. 0.034 0.075 ↑ 1.0 20 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
27. 0.041 0.041 ↑ 1.0 20 1

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

28. 0.014 0.038 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
29. 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
30.          

SubPlan (forGroupAggregate)

31. 0.073 0.114 ↑ 1.0 1 1

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

32. 0.041 0.041 ↑ 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.027..0.041 rows=20 loops=1)

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

Index Only Scan using pk_tests on tests active_test (cost=0.15..2.17 rows=1 width=1) (actual time=0.022..0.022 rows=0 loops=1)

  • Index Cond: ((user_id = s.user_id) AND (question_set_id = s.question_set_id))
  • Heap Fetches: 0