explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tUCT

Settings
# exclusive inclusive rows x rows loops node
1. 0.705 491.492 ↑ 25.0 1 1

GroupAggregate (cost=579.15..579.47 rows=25 width=80) (actual time=491.492..491.492 rows=1 loops=1)

  • Group Key: user_stat.subgroup
2.          

CTE user_stat

3. 14.314 483.627 ↓ 60.2 1,506 1

GroupAggregate (cost=497.25..497.37 rows=25 width=44) (actual time=459.658..483.627 rows=1,506 loops=1)

  • Group Key: user_1.id
4. 129.623 469.313 ↓ 2,613.0 65,325 1

Sort (cost=497.25..497.26 rows=25 width=5) (actual time=459.640..469.313 rows=65,325 loops=1)

  • Sort Key: user_1.id
  • Sort Method: quicksort Memory: 4,599kB
5. 45.005 339.690 ↓ 2,613.0 65,325 1

Nested Loop (cost=270.34..497.13 rows=25 width=5) (actual time=206.670..339.690 rows=65,325 loops=1)

6. 20.652 229.360 ↓ 1,719.1 65,325 1

Hash Join (cost=270.28..494.82 rows=38 width=9) (actual time=206.651..229.360 rows=65,325 loops=1)

  • Hash Cond: (license.user_id = user_quiz_answer.user_id)
7. 2.078 2.078 ↓ 5.1 1,528 1

Seq Scan on license (cost=0.00..224.20 rows=297 width=8) (actual time=0.011..2.078 rows=1,528 loops=1)

  • Filter: (completed AND passed AND (lesson_program_id = 1))
  • Rows Removed by Filter: 5,685
8. 16.506 206.630 ↓ 106.6 71,178 1

Hash (cost=267.95..267.95 rows=668 width=9) (actual time=206.630..206.630 rows=71,178 loops=1)

  • Buckets: 131,072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 4,083kB
9. 13.161 190.124 ↓ 106.6 71,178 1

Nested Loop (cost=192.10..267.95 rows=668 width=9) (actual time=3.520..190.124 rows=71,178 loops=1)

10. 0.076 0.122 ↓ 49.0 49 1

Hash Join (cost=6.25..7.42 rows=1 width=8) (actual time=0.023..0.122 rows=49 loops=1)

  • Hash Cond: (quiz_concept_question.quiz_concept_id = quiz_concept.id)
11. 0.032 0.032 ↑ 1.0 49 1

Seq Scan on quiz_concept_question (cost=0.00..1.15 rows=49 width=8) (actual time=0.005..0.032 rows=49 loops=1)

12. 0.004 0.014 ↓ 5.0 5 1

Hash (cost=6.24..6.24 rows=1 width=8) (actual time=0.014..0.014 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.010 0.010 ↓ 5.0 5 1

Index Scan using quiz_concept_name_lesson_program_id on quiz_concept (cost=0.03..6.24 rows=1 width=8) (actual time=0.008..0.010 rows=5 loops=1)

  • Index Cond: (lesson_program_id = 1)
14. 50.813 176.841 ↓ 2.3 1,453 49

Bitmap Heap Scan on user_quiz_answer (cost=185.85..258.63 rows=633 width=9) (actual time=2.660..3.609 rows=1,453 loops=49)

  • Recheck Cond: (quiz_question_id = quiz_concept_question.quiz_question_id)
  • Heap Blocks: exact=36,719
15. 126.028 126.028 ↓ 2.3 1,455 49

Bitmap Index Scan on user_quiz_answer_user_id_quiz_question_id (cost=0.00..185.82 rows=633 width=0) (actual time=2.572..2.572 rows=1,455 loops=49)

  • Index Cond: (quiz_question_id = quiz_concept_question.quiz_question_id)
16. 65.325 65.325 ↑ 1.0 1 65,325

Index Only Scan using user_pkey on ""user"" user_1 (cost=0.06..0.06 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=65,325)

  • Index Cond: (id = user_quiz_answer.user_id)
  • Heap Fetches: 1,141
17. 0.770 490.787 ↓ 60.2 1,506 1

Sort (cost=81.78..81.79 rows=25 width=44) (actual time=490.690..490.787 rows=1,506 loops=1)

  • Sort Key: user_stat.subgroup
  • Sort Method: quicksort Memory: 166kB
18. 1.088 490.017 ↓ 60.2 1,506 1

Nested Loop (cost=0.06..81.66 rows=25 width=44) (actual time=459.679..490.017 rows=1,506 loops=1)

19. 484.411 484.411 ↓ 60.2 1,506 1

CTE Scan on user_stat (cost=0.00..0.15 rows=25 width=44) (actual time=459.661..484.411 rows=1,506 loops=1)

20. 4.518 4.518 ↑ 1.0 1 1,506

Index Scan using user_pkey on ""user"" (cost=0.06..3.26 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1,506)

  • Index Cond: (id = user_stat.id)
Planning time : 2.836 ms
Execution time : 492.415 ms