explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zx4V

Settings
# exclusive inclusive rows x rows loops node
1. 0.142 13.383 ↑ 1.0 1 1

Group (cost=0.15..938.20 rows=1 width=40) (actual time=13.378..13.383 rows=1 loops=1)

  • Group Key: t.user_id, t.question_set_id
2. 0.097 0.097 ↑ 1.0 1 1

Index Scan using pk_tests on tests t (cost=0.15..2.17 rows=1 width=16) (actual time=0.094..0.097 rows=1 loops=1)

  • Index Cond: ((user_id = 1) AND (question_set_id = 1))
3.          

SubPlan (forGroup)

4. 0.394 0.543 ↑ 1.0 1 1

Aggregate (cost=2.55..2.56 rows=1 width=32) (actual time=0.543..0.543 rows=1 loops=1)

5. 0.035 0.149 ↓ 2.0 2 1

Merge Join (cost=0.43..2.54 rows=1 width=8) (actual time=0.133..0.149 rows=2 loops=1)

  • Merge Cond: (tq_score.question_id = q_score.question_id)
6. 0.046 0.046 ↓ 2.0 2 1

Index Scan using pk_test_questions_association on test_questions_association tq_score (cost=0.15..2.17 rows=1 width=8) (actual time=0.039..0.046 rows=2 loops=1)

  • Index Cond: ((user_id = t.user_id) AND (question_set_id = t.question_set_id))
7. 0.068 0.068 ↑ 2,000.0 2 1

Index Scan using pk_questions on questions q_score (cost=0.28..132.28 rows=4,000 width=8) (actual time=0.067..0.068 rows=2 loops=1)

8. 0.087 12.332 ↑ 1.0 1 1

Index Scan using ix_test_questions_test_q_number on test_questions_association tq_get_question (cost=0.15..928.97 rows=1 width=32) (actual time=12.330..12.332 rows=1 loops=1)

  • Index Cond: ((user_id = t.user_id) AND (question_set_id = t.question_set_id) AND (question_number = 2))
9.          

SubPlan (forIndex Scan)

10. 0.208 12.245 ↑ 1.0 1 1

Nested Loop (cost=0.43..926.80 rows=1 width=32) (actual time=12.231..12.245 rows=1 loops=1)

11. 0.061 0.061 ↑ 1.0 1 1

Index Scan using ix_test_questions_test_q_number on test_questions_association tq_to_dict (cost=0.15..2.17 rows=1 width=14) (actual time=0.056..0.061 rows=1 loops=1)

  • Index Cond: ((user_id = tq_get_question.user_id) AND (question_set_id = tq_get_question.question_set_id))
  • Filter: (question_id = tq_get_question.question_id)
  • Rows Removed by Filter: 1
12. 0.046 0.046 ↑ 1.0 1 1

Index Scan using pk_questions on questions q_to_dict (cost=0.28..2.30 rows=1 width=130) (actual time=0.040..0.046 rows=1 loops=1)

  • Index Cond: (question_id = tq_get_question.question_id)
13.          

SubPlan (forNested Loop)

14. 0.003 0.003 ↓ 0.0 0 1

Result (cost=0.28..2.30 rows=1 width=4) (actual time=0.002..0.003 rows=0 loops=1)

  • One-Time Filter: (tq_to_dict.user_answer_index IS NOT NULL)
15. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_questions on questions (cost=0.28..2.30 rows=1 width=4) (never executed)

  • Index Cond: (question_id = tq_to_dict.question_id)
16. 6.621 6.621 ↑ 1.0 1 1

Aggregate (cost=459.69..459.70 rows=1 width=32) (actual time=6.621..6.621 rows=1 loops=1)

  • -> Index Only Scan using ix_answered_questions_user_id_q_set_id_q_id_answer_index on subscriptions_questions_association aq_score (cost=0.29..459.54 rows=20 width=4) (actual time=6.
  • Index Cond: (question_id = q_to_dict.question_id)
  • Heap Fetches: 0
17. 5.306 5.306 ↑ 1.0 1 1

Aggregate (cost=460.30..460.31 rows=1 width=32) (actual time=5.305..5.306 rows=1 loops=1)

  • -> Index Only Scan using ix_answered_questions_user_id_q_set_id_q_id_answer_index on subscriptions_questions_association aq_breakdown (cost=0.29..459.54 rows=20 width=4) (actual tim
  • Index Cond: (question_id = q_to_dict.question_id)
  • Heap Fetches: 0
18. 0.126 0.269 ↑ 1.0 1 1

Aggregate (cost=4.48..4.49 rows=1 width=32) (actual time=0.269..0.269 rows=1 loops=1)

19. 0.079 0.079 ↓ 2.0 2 1

Index Scan using ix_test_questions_test_q_number on test_questions_association tq_history (cost=0.15..2.17 rows=1 width=13) (actual time=0.073..0.079 rows=2 loops=1)

  • Index Cond: ((user_id = t.user_id) AND (question_set_id = t.question_set_id))
  • Filter: is_seen
20.          

SubPlan (forAggregate)

21. 0.012 0.064 ↓ 0.0 0 2

Result (cost=0.28..2.30 rows=1 width=4) (actual time=0.031..0.032 rows=0 loops=2)

  • One-Time Filter: (tq_history.user_answer_index IS NOT NULL)
22. 0.052 0.052 ↑ 1.0 1 1

Index Scan using pk_questions on questions questions_1 (cost=0.28..2.30 rows=1 width=4) (actual time=0.050..0.052 rows=1 loops=1)

  • Index Cond: (question_id = tq_history.question_id)