explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NZII

Settings
# exclusive inclusive rows x rows loops node
1. 0.734 2,905.793 ↑ 22.0 1 1

GroupAggregate (cost=631.46..631.75 rows=22 width=112) (actual time=2,905.793..2,905.793 rows=1 loops=1)

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

CTE user_stat

3. 15.130 2,897.673 ↓ 68.5 1,506 1

GroupAggregate (cost=557.79..557.90 rows=22 width=76) (actual time=2,875.695..2,897.673 rows=1,506 loops=1)

  • Group Key: user_1.id
4. 36.462 2,882.543 ↓ 2,969.3 65,325 1

Sort (cost=557.79..557.80 rows=22 width=5) (actual time=2,875.678..2,882.543 rows=65,325 loops=1)

  • Sort Key: user_1.id
  • Sort Method: quicksort Memory: 4,599kB
5. 550.375 2,846.081 ↓ 2,969.3 65,325 1

Hash Join (cost=22.02..557.69 rows=22 width=5) (actual time=0.285..2,846.081 rows=65,325 loops=1)

  • Hash Cond: ((user_quiz_answer.quiz_answer_id = quiz_answer.id) AND (quiz_concept_question.quiz_question_id = quiz_answer.quiz_question_id))
6. 701.513 2,295.492 ↓ 1,191.8 4,663,379 1

Nested Loop (cost=6.38..537.94 rows=3,913 width=13) (actual time=0.061..2,295.492 rows=4,663,379 loops=1)

7. 12.724 27.841 ↓ 717.1 74,578 1

Nested Loop (cost=6.30..290.62 rows=104 width=12) (actual time=0.050..27.841 rows=74,578 loops=1)

8. 0.732 7.507 ↓ 5.1 1,522 1

Nested Loop (cost=0.06..282.17 rows=297 width=12) (actual time=0.025..7.507 rows=1,522 loops=1)

9. 2.191 2.191 ↓ 5.1 1,528 1

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

  • Filter: (completed AND passed AND (lesson_program_id = 1))
  • Rows Removed by Filter: 5,672
10. 4.584 4.584 ↑ 1.0 1 1,528

Index Only Scan using user_pkey on ""user"" user_1 (cost=0.06..0.20 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1,528)

  • Index Cond: (id = license.user_id)
  • Heap Fetches: 1
11. 7.556 7.610 ↓ 49.0 49 1,522

Materialize (cost=6.25..7.42 rows=1 width=8) (actual time=0.000..0.005 rows=49 loops=1,522)

12. 0.028 0.054 ↓ 49.0 49 1

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

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

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

14. 0.004 0.013 ↓ 5.0 5 1

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

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

  • Index Cond: (lesson_program_id = 1)
16. 1,566.138 1,566.138 ↓ 1.1 63 74,578

Index Scan using user_quiz_answer_user_id_quiz_question_id on user_quiz_answer (cost=0.08..2.20 rows=59 width=9) (actual time=0.003..0.021 rows=63 loops=74,578)

  • Index Cond: (user_id = user_1.id)
17. 0.093 0.214 ↑ 1.0 662 1

Hash (cost=12.99..12.99 rows=662 width=8) (actual time=0.214..0.214 rows=662 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 34kB
18. 0.121 0.121 ↑ 1.0 662 1

Seq Scan on quiz_answer (cost=0.00..12.99 rows=662 width=8) (actual time=0.004..0.121 rows=662 loops=1)

19. 0.806 2,905.059 ↓ 68.5 1,506 1

Sort (cost=73.56..73.57 rows=22 width=76) (actual time=2,904.960..2,905.059 rows=1,506 loops=1)

  • Sort Key: user_stat.""order"", user_stat.subgroup
  • Sort Method: quicksort Memory: 166kB
20. 1.223 2,904.253 ↓ 68.5 1,506 1

Nested Loop (cost=0.06..73.47 rows=22 width=76) (actual time=2,875.713..2,904.253 rows=1,506 loops=1)

21. 2,898.512 2,898.512 ↓ 68.5 1,506 1

CTE Scan on user_stat (cost=0.00..0.13 rows=22 width=76) (actual time=2,875.698..2,898.512 rows=1,506 loops=1)

22. 4.518 4.518 ↑ 1.0 1 1,506

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

  • Index Cond: (id = user_stat.id)
Planning time : 2.055 ms
Execution time : 2,906.305 ms