explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tlEO

Settings
# exclusive inclusive rows x rows loops node
1. 1.976 30.280 ↑ 1.0 1 1

GroupAggregate (cost=117.75..124.80 rows=1 width=44) (actual time=30.279..30.280 rows=1 loops=1)

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

CTE subtopic_breakdown

3. 3.643 21.703 ↑ 10.0 20 1

HashAggregate (cost=102.64..104.64 rows=200 width=24) (actual time=21.609..21.703 rows=20 loops=1)

  • Group Key: q.topic_id, q.subtopic_id
4. 5.144 18.060 ↓ 2.0 1,000 1

GroupAggregate (cost=0.57..92.72 rows=496 width=16) (actual time=0.761..18.060 rows=1,000 loops=1)

  • Group Key: q.question_id
5. 5.828 12.916 ↓ 2.0 1,002 1

Merge Join (cost=0.57..82.80 rows=496 width=20) (actual time=0.690..12.916 rows=1,002 loops=1)

  • Merge Cond: (q.question_id = aq.question_id)
6. 4.225 4.225 ↑ 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.347..4.225 rows=2,000 loops=1)

  • Index Cond: (question_set_id = 1)
  • Heap Fetches: 0
7. 2.863 2.863 ↓ 1.0 1,002 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.11 rows=991 width=8) (actual time=0.312..2.863 rows=1,002 loops=1)

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

Initplan (forGroupAggregate)

9. 0.030 0.030 ↑ 1.0 1 1

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

10. 0.029 27.475 ↑ 1.0 5 1

Nested Loop (cost=12.10..15.26 rows=5 width=110) (actual time=27.434..27.475 rows=5 loops=1)

11. 0.020 0.218 ↑ 1.0 1 1

Nested Loop (cost=0.28..3.33 rows=1 width=78) (actual time=0.197..0.218 rows=1 loops=1)

12. 0.138 0.138 ↑ 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=28) (actual time=0.129..0.138 rows=1 loops=1)

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

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

  • Filter: (question_set_id = 1)
  • Rows Removed by Filter: 1
14. 0.274 27.228 ↑ 1.0 5 1

Sort (cost=11.82..11.83 rows=5 width=44) (actual time=27.223..27.228 rows=5 loops=1)

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

Initplan (forSort)

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

17. 1.890 26.931 ↑ 1.0 5 1

GroupAggregate (cost=10.48..10.75 rows=5 width=44) (actual time=25.534..26.931 rows=5 loops=1)

  • Group Key: ts.topic_id
18. 0.119 25.041 ↓ 4.0 20 1

Sort (cost=10.48..10.49 rows=5 width=44) (actual time=25.020..25.041 rows=20 loops=1)

  • Sort Key: ts.topic_id
  • Sort Method: quicksort Memory: 22kB
19. 0.120 24.922 ↓ 4.0 20 1

Hash Left Join (cost=9.22..10.42 rows=5 width=44) (actual time=24.861..24.922 rows=20 loops=1)

  • Hash Cond: (ts.topic_id = subtopic_subquery.topic_id)
20. 0.051 0.051 ↑ 1.0 5 1

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

  • Filter: (question_set_id = 1)
  • Rows Removed by Filter: 5
21. 0.105 24.751 ↓ 2.0 20 1

Hash (cost=9.10..9.10 rows=10 width=36) (actual time=24.750..24.751 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.041 24.646 ↓ 2.0 20 1

Subquery Scan on subtopic_subquery (cost=8.97..9.10 rows=10 width=36) (actual time=24.597..24.646 rows=20 loops=1)

23. 0.278 24.605 ↓ 2.0 20 1

Sort (cost=8.97..9.00 rows=10 width=55) (actual time=24.589..24.605 rows=20 loops=1)

  • Sort Key: sts.name
  • Sort Method: quicksort Memory: 22kB
24.          

Initplan (forSort)

25. 0.030 0.030 ↑ 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.027..0.030 rows=1 loops=1)

26. 1.846 24.297 ↓ 2.0 20 1

HashAggregate (cost=7.37..7.80 rows=10 width=55) (actual time=22.886..24.297 rows=20 loops=1)

  • Group Key: sts.subtopic_id, subtopic_breakdown.topic_id, subtopic_breakdown.subtopic_id
27. 0.115 22.451 ↓ 2.0 20 1

Hash Join (cost=2.64..7.25 rows=10 width=43) (actual time=22.144..22.451 rows=20 loops=1)

  • Hash Cond: (sts.topic_id = ts_1.topic_id)
28. 0.217 22.240 ↑ 1.0 20 1

Hash Join (cost=1.45..5.99 rows=20 width=43) (actual time=21.992..22.240 rows=20 loops=1)

  • Hash Cond: (subtopic_breakdown.subtopic_id = sts.subtopic_id)
29. 21.795 21.795 ↑ 10.0 20 1

CTE Scan on subtopic_breakdown (cost=0.00..4.00 rows=200 width=24) (actual time=21.621..21.795 rows=20 loops=1)

30. 0.095 0.228 ↑ 1.0 20 1

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

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

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

32. 0.052 0.096 ↑ 1.0 5 1

Hash (cost=1.12..1.12 rows=5 width=4) (actual time=0.095..0.096 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
33. 0.044 0.044 ↑ 1.0 5 1

Seq Scan on topics ts_1 (cost=0.00..1.12 rows=5 width=4) (actual time=0.024..0.044 rows=5 loops=1)

  • Filter: (question_set_id = 1)
  • Rows Removed by Filter: 5
34.          

SubPlan (forGroupAggregate)

35. 0.493 0.755 ↑ 1.0 1 1

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

36. 0.262 0.262 ↑ 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.232..0.262 rows=20 loops=1)

  • Index Cond: (question_set_id = s.question_set_id)
  • Heap Fetches: 0
37. 0.044 0.044 ↓ 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.043..0.044 rows=0 loops=1)

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