explain.depesz.com

PostgreSQL's explain analyze made readable

Result: e1Dc

Settings
# exclusive inclusive rows x rows loops node
1. 1.903 3,365.609 ↑ 25.0 8 1

GroupAggregate (cost=3,044.06..3,053.85 rows=200 width=84) (actual time=3,365.527..3,365.609 rows=8 loops=1)

  • Group Key: user_stat.""order"", user_stat.subgroup
2.          

CTE user_stat

3. 75.953 3,349.127 ↓ 8.5 10,394 1

GroupAggregate (cost=2,308.45..2,317.66 rows=1,228 width=48) (actual time=3,199.517..3,349.127 rows=10,394 loops=1)

  • Group Key: user_1.id
4. 318.967 3,273.174 ↓ 522.8 642,035 1

Sort (cost=2,308.45..2,309.06 rows=1,228 width=6) (actual time=3,199.373..3,273.174 rows=642,035 loops=1)

  • Sort Key: user_1.id
  • Sort Method: external merge Disk: 9,624kB
5. 0.000 2,954.207 ↓ 522.8 642,035 1

Nested Loop (cost=786.39..2,295.85 rows=1,228 width=6) (actual time=6.969..2,954.207 rows=642,035 loops=1)

6. 202.908 1,777.523 ↓ 294.4 642,035 1

Hash Join (cost=786.33..2,157.55 rows=2,181 width=9) (actual time=6.957..1,777.523 rows=642,035 loops=1)

  • Hash Cond: (user_quiz_answer.user_id = license.user_id)
7. 108.824 1,567.769 ↓ 136.8 665,112 1

Nested Loop (cost=6.33..1,357.64 rows=4,861 width=9) (actual time=0.059..1,567.769 rows=665,112 loops=1)

8. 0.134 0.213 ↓ 66.0 66 1

Hash Join (cost=6.25..7.48 rows=1 width=8) (actual time=0.041..0.213 rows=66 loops=1)

  • Hash Cond: (quiz_concept_question.quiz_concept_id = quiz_concept.id)
9. 0.061 0.061 ↑ 1.0 66 1

Seq Scan on quiz_concept_question (cost=0.00..1.20 rows=66 width=8) (actual time=0.006..0.061 rows=66 loops=1)

10. 0.006 0.018 ↓ 5.0 5 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
11. 0.012 0.012 ↓ 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.010..0.012 rows=5 loops=1)

  • Index Cond: (lesson_program_id = 1)
12. 1,458.732 1,458.732 ↓ 2.3 10,077 66

Index Scan using user_quiz_answer_user_id_quiz_question_id on user_quiz_answer (cost=0.08..1,337.05 rows=4,373 width=9) (actual time=0.008..22.102 rows=10,077 loops=66)

  • Index Cond: (quiz_question_id = quiz_concept_question.quiz_question_id)
13. 1.863 6.846 ↓ 1.5 10,610 1

Hash (cost=755.26..755.26 rows=7,069 width=8) (actual time=6.846..6.846 rows=10,610 loops=1)

  • Buckets: 16,384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 543kB
14. 4.983 4.983 ↓ 1.5 10,613 1

Seq Scan on license (cost=0.00..755.26 rows=7,069 width=8) (actual time=0.009..4.983 rows=10,613 loops=1)

  • Filter: (completed AND passed AND (lesson_program_id = 1))
  • Rows Removed by Filter: 7,768
15. 1,284.070 1,284.070 ↑ 1.0 1 642,035

Index Scan using user_pkey on ""user"" user_1 (cost=0.06..0.06 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=642,035)

  • Index Cond: (id = user_quiz_answer.user_id)
16. 2.941 3,363.706 ↓ 8.5 10,394 1

Sort (cost=726.40..727.01 rows=1,228 width=48) (actual time=3,363.213..3,363.706 rows=10,394 loops=1)

  • Sort Key: user_stat.""order"", user_stat.subgroup
  • Sort Method: quicksort Memory: 1,197kB
17. 3.620 3,360.765 ↓ 8.5 10,394 1

Hash Join (cost=705.78..713.79 rows=1,228 width=48) (actual time=3,204.611..3,360.765 rows=10,394 loops=1)

  • Hash Cond: (user_stat.id = ""user"".id)
18. 3,352.081 3,352.081 ↓ 8.5 10,394 1

CTE Scan on user_stat (cost=0.00..7.37 rows=1,228 width=48) (actual time=3,199.521..3,352.081 rows=10,394 loops=1)

19. 1.865 5.064 ↓ 1.0 13,458 1

Hash (cost=659.59..659.59 rows=13,197 width=8) (actual time=5.064..5.064 rows=13,458 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 647kB
20. 3.199 3.199 ↓ 1.0 13,458 1

Seq Scan on ""user"" (cost=0.00..659.59 rows=13,197 width=8) (actual time=0.014..3.199 rows=13,458 loops=1)

Planning time : 2.449 ms
Execution time : 3,367.639 ms