explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6gFI

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Merge Join (cost=140,737.47..140,740.40 rows=1 width=24) (actual rows= loops=)

  • Merge Cond: (survey_employee_hash.hash = total_q.hash)
  • Join Filter: (total_q.rank = (min(qb_join.rank)))
2. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=140,726.16..140,727.78 rows=93 width=10) (actual rows= loops=)

  • Group Key: survey_employee_hash.hash
3. 0.000 0.000 ↓ 0.0

Sort (cost=140,726.16..140,726.39 rows=93 width=10) (actual rows= loops=)

  • Sort Key: survey_employee_hash.hash
4. 0.000 0.000 ↓ 0.0

Merge Join (cost=140,680.15..140,723.12 rows=93 width=10) (actual rows= loops=)

  • Merge Cond: (survey.qb_id = qb.id)
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=140,679.83..140,746.78 rows=95 width=18) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Merge Join (cost=140,679.56..140,718.24 rows=95 width=22) (actual rows= loops=)

  • Merge Cond: (qb_join.qb_id = survey.qb_id)
  • Join Filter: (NOT (hashed SubPlan 1))
7. 0.000 0.000 ↓ 0.0

Index Scan using qb_id on qb_join (cost=0.28..43.75 rows=1,633 width=16) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Sort (cost=89.52..89.58 rows=25 width=10) (actual rows= loops=)

  • Sort Key: survey.qb_id
9. 0.000 0.000 ↓ 0.0

Hash Join (cost=10.09..88.94 rows=25 width=10) (actual rows= loops=)

  • Hash Cond: (survey_employee.survey_id = survey.id)
10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..79.63 rows=25 width=10) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Index Scan using timestamp_index on survey_employee (cost=0.43..13.63 rows=25 width=8) (actual rows= loops=)

  • Index Cond: ("timestamp" > (now() + '-02:00:00'::interval))
  • Filter: (state <> 3)
12. 0.000 0.000 ↓ 0.0

Index Scan using survey_employee_hash_pkey on survey_employee_hash (cost=0.42..2.64 rows=1 width=10) (actual rows= loops=)

  • Index Cond: (id = survey_employee.hash_id)
13. 0.000 0.000 ↓ 0.0

Hash (cost=6.33..6.33 rows=233 width=8) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Seq Scan on survey (cost=0.00..6.33 rows=233 width=8) (actual rows= loops=)

15.          

SubPlan (forMerge Join)

16. 0.000 0.000 ↓ 0.0

Hash Join (cost=91,394.23..139,755.71 rows=333,573 width=10) (actual rows= loops=)

  • Hash Cond: (survey_employee_2.hash_id = survey_employee_hash_2.id)
17. 0.000 0.000 ↓ 0.0

Hash Join (cost=83,126.02..130,611.86 rows=333,573 width=8) (actual rows= loops=)

  • Hash Cond: (survey_employee_select_one_answer.survey_employee_id = survey_employee_2.id)
18. 0.000 0.000 ↓ 0.0

HashAggregate (cost=76,685.45..97,673.63 rows=2,098,818 width=8) (actual rows= loops=)

  • Group Key: survey_employee_select_one_answer.survey_employee_id, survey_employee_select_one_answer.question_id
19. 0.000 0.000 ↓ 0.0

Append (cost=0.00..66,191.36 rows=2,098,818 width=8) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Seq Scan on survey_employee_select_one_answer (cost=0.00..35,104.61 rows=1,584,861 width=8) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on survey_employee_multi_select_answer (cost=0.00..10,097.54 rows=513,954 width=8) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Seq Scan on survey_employee_range_answer (cost=0.00..1.03 rows=3 width=8) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Hash (cost=5,870.69..5,870.69 rows=45,591 width=8) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Seq Scan on survey_employee survey_employee_2 (cost=0.00..5,870.69 rows=45,591 width=8) (actual rows= loops=)

  • Filter: (state <> 3)
25. 0.000 0.000 ↓ 0.0

Hash (cost=4,564.76..4,564.76 rows=296,276 width=10) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Seq Scan on survey_employee_hash survey_employee_hash_2 (cost=0.00..4,564.76 rows=296,276 width=10) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Index Only Scan using answer_set_pkey on answer_set (cost=0.28..0.30 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = qb_join.answer_set_id)
28. 0.000 0.000 ↓ 0.0

Index Only Scan using question_bank_pkey on qb (cost=0.14..8.60 rows=210 width=4) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Sort (cost=11.31..11.33 rows=8 width=24) (actual rows= loops=)

  • Sort Key: total_q.hash
30. 0.000 0.000 ↓ 0.0

Subquery Scan on total_q (cost=11.09..11.19 rows=8 width=24) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Sort (cost=11.09..11.11 rows=8 width=24) (actual rows= loops=)

  • Sort Key: qb_join_1.rank
32. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.69..10.97 rows=8 width=24) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.41..8.45 rows=8 width=22) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.14..7.86 rows=1 width=18) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.99..7.67 rows=1 width=14) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..5.29 rows=1 width=14) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Index Scan using id_index on survey_employee survey_employee_1 (cost=0.42..2.65 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (id = 291494)
  • Filter: ((state <> 3) AND ("timestamp" > (now() + '-02:00:00'::interval)))
38. 0.000 0.000 ↓ 0.0

Index Scan using survey_employee_hash_pkey on survey_employee_hash survey_employee_hash_1 (cost=0.42..2.64 rows=1 width=10) (actual rows= loops=)

  • Index Cond: (id = survey_employee_1.hash_id)
39. 0.000 0.000 ↓ 0.0

Index Scan using id_btree_index on survey survey_1 (cost=0.14..2.36 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = survey_employee_1.survey_id)
40. 0.000 0.000 ↓ 0.0

Index Only Scan using question_bank_pkey on qb qb_1 (cost=0.14..0.19 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = survey_1.qb_id)
41. 0.000 0.000 ↓ 0.0

Index Scan using qb_id on qb_join qb_join_1 (cost=0.28..0.51 rows=8 width=16) (actual rows= loops=)

  • Index Cond: (qb_id = qb_1.id)
42. 0.000 0.000 ↓ 0.0

Index Scan using answer_set_pkey on answer_set answer_set_1 (cost=0.28..0.32 rows=1 width=6) (actual rows= loops=)

  • Index Cond: (id = qb_join_1.answer_set_id)