explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aexX : Optimization for: plan #v7hO

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.082 11.981 ↑ 1.0 1 1

Group (cost=5.78..929.88 rows=1 width=40) (actual time=11.976..11.981 rows=1 loops=1)

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

Initplan (forGroup)

3. 0.083 0.127 ↑ 1.0 1 1

Aggregate (cost=5.64..5.65 rows=1 width=32) (actual time=0.126..0.127 rows=1 loops=1)

4. 0.042 0.042 ↑ 2.0 1 1

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

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

SubPlan (forAggregate)

6. 0.002 0.002 ↓ 0.0 0 1

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

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

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

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

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

9.          

SubPlan (forGroup)

10. 0.025 0.200 ↑ 1.0 1 1

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

11. 0.021 0.175 ↑ 1.0 2 1

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

  • Merge Cond: (q.question_id = tq.question_id)
12. 0.062 0.062 ↑ 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.058..0.062 rows=3 loops=1)

13. 0.052 0.092 ↑ 1.0 2 1

Sort (cost=1.04..1.04 rows=2 width=8) (actual time=0.091..0.092 rows=2 loops=1)

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

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

  • Filter: ((user_id = t.user_id) AND (question_set_id = t.question_set_id))
15. 0.028 11.523 ↑ 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.519..11.523 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.177 11.495 ↑ 1.0 1 1

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

18. 0.024 0.024 ↑ 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.013..0.024 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.054 0.054 ↑ 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.046..0.054 rows=1 loops=1)

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

SubPlan (forNested Loop)

21. 0.002 0.002 ↓ 0.0 0 1

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

  • One-Time Filter: (tq_to_dict.user_answer_index IS NOT NULL)
22. 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)
23. 0.060 5.768 ↑ 1.0 1 1

Aggregate (cost=456.66..456.67 rows=1 width=32) (actual time=5.767..5.768 rows=1 loops=1)

24. 5.708 5.708 ↓ 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.163..5.708 rows=22 loops=1)

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

Aggregate (cost=457.27..457.28 rows=1 width=32) (actual time=5.469..5.470 rows=1 loops=1)

26. 5.318 5.318 ↓ 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_breakdown (cost=0.29..456.50 rows=20 width=4) (actual time=0.116..5.318 rows=22 loops=1)

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