explain.depesz.com

PostgreSQL's explain analyze made readable

Result: v7hO

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.094 11.824 ↑ 1.0 1 1

Group (cost=5.78..929.88 rows=1 width=40) (actual time=11.818..11.824 rows=1 loops=1)

  • Group Key: t.user_id, t.question_set_id
2.          

Initplan (forGroup)

3. 0.119 0.242 ↑ 1.0 1 1

Aggregate (cost=5.64..5.65 rows=1 width=32) (actual time=0.241..0.242 rows=1 loops=1)

4. 0.037 0.037 ↑ 1.0 2 1

Seq Scan on test_questions_association tq_history (cost=0.00..1.03 rows=2 width=13) (actual time=0.034..0.037 rows=2 loops=1)

  • Filter: (is_seen AND (user_id = 1) AND (question_set_id = 1))
5.          

SubPlan (forAggregate)

6. 0.018 0.086 ↑ 1.0 1 2

Result (cost=0.28..2.30 rows=1 width=4) (actual time=0.041..0.043 rows=1 loops=2)

  • One-Time Filter: (tq_history.user_answer_index IS NOT NULL)
7. 0.068 0.068 ↑ 1.0 1 2

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

  • Index Cond: (question_id = tq_history.question_id)
8. 0.044 0.044 ↑ 1.0 1 1

Index Scan using pk_tests on tests t (cost=0.12..2.14 rows=1 width=16) (actual time=0.039..0.044 rows=1 loops=1)

9.          

SubPlan (forGroup)

10. 0.037 0.167 ↑ 1.0 1 1

Aggregate (cost=1.44..1.45 rows=1 width=32) (actual time=0.167..0.167 rows=1 loops=1)

11. 0.020 0.130 ↑ 1.0 2 1

Merge Join (cost=1.32..1.42 rows=2 width=8) (actual time=0.119..0.130 rows=2 loops=1)

  • Merge Cond: (q.question_id = tq.question_id)
12. 0.050 0.050 ↑ 1,333.3 3 1

Index Scan using pk_questions on questions q (cost=0.28..132.28 rows=4,000 width=8) (actual time=0.046..0.050 rows=3 loops=1)

13. 0.028 0.060 ↑ 1.0 2 1

Sort (cost=1.04..1.04 rows=2 width=8) (actual time=0.059..0.060 rows=2 loops=1)

  • Sort Key: tq.question_id
  • Sort Method: quicksort Memory: 17kB
14. 0.032 0.032 ↑ 1.0 2 1

Seq Scan on test_questions_association tq (cost=0.00..1.03 rows=2 width=8) (actual time=0.029..0.032 rows=2 loops=1)

  • Filter: ((user_id = t.user_id) AND (question_set_id = t.question_set_id))
15. 0.026 11.277 ↑ 1.0 1 1

Seq Scan on test_questions_association tq_get_question (cost=0.00..920.63 rows=1 width=32) (actual time=11.273..11.277 rows=1 loops=1)

  • Filter: ((user_id = t.user_id) AND (question_set_id = t.question_set_id) AND (question_number = 1))
  • Rows Removed by Filter: 1
16.          

SubPlan (forSeq Scan)

17. 0.158 11.251 ↑ 1.0 1 1

Nested Loop (cost=0.28..919.59 rows=1 width=32) (actual time=11.234..11.251 rows=1 loops=1)

18. 0.020 0.020 ↑ 1.0 1 1

Seq Scan on test_questions_association tq_to_dict (cost=0.00..1.03 rows=1 width=13) (actual time=0.010..0.020 rows=1 loops=1)

  • Filter: ((question_id = tq_get_question.question_id) AND (user_id = tq_get_question.user_id) AND (question_set_id = tq_get_question.question_set_id))
  • Rows Removed by Filter: 1
19. 0.039 0.039 ↑ 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.035..0.039 rows=1 loops=1)

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

SubPlan (forNested Loop)

21. 0.008 0.028 ↑ 1.0 1 1

Result (cost=0.28..2.30 rows=1 width=4) (actual time=0.025..0.028 rows=1 loops=1)

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

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

  • Index Cond: (question_id = tq_to_dict.question_id)
23. 0.059 5.372 ↑ 1.0 1 1

Aggregate (cost=456.66..456.67 rows=1 width=32) (actual time=5.371..5.372 rows=1 loops=1)

24. 5.313 5.313 ↓ 1.1 22 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..456.50 rows=20 width=4) (actual time=0.065..5.313 rows=22 loops=1)

  • Index Cond: (question_id = q_to_dict.question_id)
  • Heap Fetches: 0
25. 0.151 5.634 ↑ 1.0 1 1

Aggregate (cost=457.27..457.28 rows=1 width=32) (actual time=5.633..5.634 rows=1 loops=1)

26. 5.483 5.483 ↓ 1.1 22 1

Index Only Scan using ix_answered_questions_user_id_q_set_id_q_id_answer_index on subscriptions_questions_association (cost=0.29..456.50 rows=20 width=4) (actual time=0.087..5.483 rows=22 loops=1)

  • Index Cond: (question_id = q_to_dict.question_id)
  • Heap Fetches: 0