explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nyYW

Settings
# exclusive inclusive rows x rows loops node
1. 0.199 25.968 ↑ 1,850.0 1 1

Group (cost=36.92..1,982,467.29 rows=1,850 width=40) (actual time=25.958..25.968 rows=1 loops=1)

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

Initplan (forGroup)

3. 0.220 0.477 ↑ 1.0 1 1

Aggregate (cost=36.76..36.77 rows=1 width=32) (actual time=0.476..0.477 rows=1 loops=1)

4. 0.075 0.075 ↓ 2.0 2 1

Seq Scan on test_questions_association tq_history (cost=0.00..34.45 rows=1 width=13) (actual time=0.069..0.075 rows=2 loops=1)

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

SubPlan (forAggregate)

6. 0.030 0.182 ↑ 1.0 1 2

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

  • One-Time Filter: (tq_history.user_answer_index IS NOT NULL)
7. 0.152 0.152 ↑ 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.074..0.076 rows=1 loops=2)

  • Index Cond: (question_id = tq_history.question_id)
8. 1.693 1.693 ↑ 1,850.0 1 1

Index Scan using pk_tests on tests t (cost=0.15..39.90 rows=1,850 width=16) (actual time=1.687..1.693 rows=1 loops=1)

9.          

SubPlan (forGroup)

10. 0.064 0.332 ↑ 1.0 1 1

Aggregate (cost=148.63..148.64 rows=1 width=32) (actual time=0.331..0.332 rows=1 loops=1)

11. 0.040 0.268 ↑ 815.0 2 1

Merge Join (cost=0.43..136.41 rows=1,630 width=8) (actual time=0.246..0.268 rows=2 loops=1)

  • Merge Cond: (tq.question_id = q.question_id)
12. 0.123 0.123 ↑ 815.0 2 1

Index Scan using pk_test_questions_association on test_questions_association tq (cost=0.15..44.75 rows=1,630 width=8) (actual time=0.114..0.123 rows=2 loops=1)

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

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

14. 0.097 23.267 ↑ 1.0 1 1

Index Scan using ix_test_questions_test_q_number on test_questions_association tq_get_question (cost=0.15..922.91 rows=1 width=32) (actual time=23.258..23.267 rows=1 loops=1)

  • Index Cond: ((user_id = t.user_id) AND (question_set_id = t.question_set_id) AND (question_number = 1))
15.          

SubPlan (forIndex Scan)

16. 0.300 23.170 ↑ 1.0 1 1

Nested Loop (cost=0.43..920.73 rows=1 width=32) (actual time=23.152..23.170 rows=1 loops=1)

17. 0.055 0.055 ↑ 1.0 1 1

Index Scan using pk_test_questions_association on test_questions_association tq_to_dict (cost=0.15..2.17 rows=1 width=13) (actual time=0.051..0.055 rows=1 loops=1)

  • Index Cond: ((user_id = tq_get_question.user_id) AND (question_set_id = tq_get_question.question_set_id) AND (question_id = tq_get_question.question_id))
18. 0.057 0.057 ↑ 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.048..0.057 rows=1 loops=1)

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

SubPlan (forNested Loop)

20. 0.013 0.054 ↑ 1.0 1 1

Result (cost=0.28..2.30 rows=1 width=4) (actual time=0.050..0.054 rows=1 loops=1)

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

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

  • Index Cond: (question_id = tq_to_dict.question_id)
22. 0.112 12.016 ↑ 1.0 1 1

Aggregate (cost=456.66..456.67 rows=1 width=32) (actual time=12.015..12.016 rows=1 loops=1)

23. 11.904 11.904 ↓ 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.139..11.904 rows=22 loops=1)

  • Index Cond: (question_id = q_to_dict.question_id)
  • Heap Fetches: 2
24. 0.277 10.688 ↑ 1.0 1 1

Aggregate (cost=457.27..457.28 rows=1 width=32) (actual time=10.687..10.688 rows=1 loops=1)

25. 10.411 10.411 ↓ 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.208..10.411 rows=22 loops=1)

  • Index Cond: (question_id = q_to_dict.question_id)
  • Heap Fetches: 2
Planning time : 6.674 ms