explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Dnsk

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 1,979.617 ↓ 0.0 0 1

Merge Join (cost=140,737.40..140,740.33 rows=1 width=24) (actual time=1,979.617..1,979.617 rows=0 loops=1)

  • Merge Cond: (survey_employee_hash.hash = total_q.hash)
  • Join Filter: (total_q.rank = (min(qb_join.rank)))
2. 0.010 1,979.557 ↑ 93.0 1 1

GroupAggregate (cost=140,726.09..140,727.72 rows=93 width=10) (actual time=1,979.557..1,979.557 rows=1 loops=1)

  • Group Key: survey_employee_hash.hash
3. 0.459 1,979.547 ↑ 7.8 12 1

Sort (cost=140,726.09..140,726.32 rows=93 width=10) (actual time=1,979.546..1,979.547 rows=12 loops=1)

  • Sort Key: survey_employee_hash.hash
  • Sort Method: quicksort Memory: 41kB
4. 0.060 1,979.088 ↓ 3.8 356 1

Merge Join (cost=140,680.08..140,723.05 rows=93 width=10) (actual time=1,977.931..1,979.088 rows=356 loops=1)

  • Merge Cond: (survey.qb_id = qb.id)
5. 0.142 1,978.772 ↓ 3.7 356 1

Nested Loop (cost=140,679.77..140,746.71 rows=95 width=18) (actual time=1,977.707..1,978.772 rows=356 loops=1)

6. 30.842 1,977.918 ↓ 3.7 356 1

Merge Join (cost=140,679.49..140,718.18 rows=95 width=22) (actual time=1,977.264..1,977.918 rows=356 loops=1)

  • Merge Cond: (qb_join.qb_id = survey.qb_id)
  • Join Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Join Filter: 130
7. 0.328 0.328 ↓ 1.0 1,640 1

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

8. 0.046 0.633 ↓ 19.0 474 1

Sort (cost=89.45..89.52 rows=25 width=10) (actual time=0.615..0.633 rows=474 loops=1)

  • Sort Key: survey.qb_id
  • Sort Method: quicksort Memory: 27kB
9. 0.029 0.587 ↓ 1.9 48 1

Hash Join (cost=10.09..88.87 rows=25 width=10) (actual time=0.174..0.587 rows=48 loops=1)

  • Hash Cond: (survey_employee.survey_id = survey.id)
10. 0.037 0.477 ↓ 1.9 48 1

Nested Loop (cost=0.85..79.56 rows=25 width=10) (actual time=0.073..0.477 rows=48 loops=1)

11. 0.392 0.392 ↓ 1.9 48 1

Index Scan using timestamp_index on survey_employee (cost=0.43..13.56 rows=25 width=8) (actual time=0.062..0.392 rows=48 loops=1)

  • Index Cond: ("timestamp" > (now() + '-02:00:00'::interval))
  • Filter: (state <> 3)
  • Rows Removed by Filter: 278
12. 0.048 0.048 ↑ 1.0 1 48

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

  • Index Cond: (id = survey_employee.hash_id)
13. 0.031 0.081 ↓ 1.0 239 1

Hash (cost=6.33..6.33 rows=233 width=8) (actual time=0.081..0.081 rows=239 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
14. 0.050 0.050 ↓ 1.0 239 1

Seq Scan on survey (cost=0.00..6.33 rows=233 width=8) (actual time=0.019..0.050 rows=239 loops=1)

15.          

SubPlan (forMerge Join)

16. 27.499 1,946.115 ↑ 6.0 55,794 1

Hash Join (cost=91,394.23..139,755.71 rows=333,573 width=10) (actual time=1,275.739..1,946.115 rows=55,794 loops=1)

  • Hash Cond: (survey_employee_2.hash_id = survey_employee_hash_2.id)
17. 166.943 1,869.089 ↑ 6.0 55,794 1

Hash Join (cost=83,126.02..130,611.86 rows=333,573 width=8) (actual time=1,224.132..1,869.089 rows=55,794 loops=1)

  • Hash Cond: (survey_employee_select_one_answer.survey_employee_id = survey_employee_2.id)
18. 1,286.152 1,665.930 ↑ 1.1 1,911,230 1

HashAggregate (cost=76,685.45..97,673.63 rows=2,098,818 width=8) (actual time=1,187.536..1,665.930 rows=1,911,230 loops=1)

  • Group Key: survey_employee_select_one_answer.survey_employee_id, survey_employee_select_one_answer.question_id
19. 61.655 379.778 ↓ 1.0 2,136,224 1

Append (cost=0.00..66,191.36 rows=2,098,818 width=8) (actual time=0.020..379.778 rows=2,136,224 loops=1)

20. 249.371 249.371 ↓ 1.0 1,613,027 1

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

21. 68.734 68.734 ↓ 1.0 523,194 1

Seq Scan on survey_employee_multi_select_answer (cost=0.00..10,097.54 rows=513,954 width=8) (actual time=0.018..68.734 rows=523,194 loops=1)

22. 0.018 0.018 ↑ 1.0 3 1

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

23. 5.713 36.216 ↑ 1.0 45,266 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2281kB
24. 30.503 30.503 ↑ 1.0 45,266 1

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

  • Filter: (state <> 3)
  • Rows Removed by Filter: 241747
25. 32.399 49.527 ↑ 1.0 296,276 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 16827kB
26. 17.128 17.128 ↑ 1.0 296,276 1

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

27. 0.712 0.712 ↑ 1.0 1 356

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

  • Index Cond: (id = qb_join.answer_set_id)
  • Heap Fetches: 29
28. 0.256 0.256 ↑ 1.0 210 1

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

  • Heap Fetches: 210
29. 0.024 0.057 ↓ 0.0 0 1

Sort (cost=11.31..11.33 rows=8 width=24) (actual time=0.057..0.057 rows=0 loops=1)

  • Sort Key: total_q.hash
  • Sort Method: quicksort Memory: 25kB
30. 0.001 0.033 ↓ 0.0 0 1

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

31. 0.010 0.032 ↓ 0.0 0 1

Sort (cost=11.09..11.11 rows=8 width=24) (actual time=0.032..0.032 rows=0 loops=1)

  • Sort Key: qb_join_1.rank
  • Sort Method: quicksort Memory: 25kB
32. 0.000 0.022 ↓ 0.0 0 1

Nested Loop (cost=1.69..10.97 rows=8 width=24) (actual time=0.022..0.022 rows=0 loops=1)

33. 0.001 0.023 ↓ 0.0 0 1

Nested Loop (cost=1.41..8.45 rows=8 width=22) (actual time=0.022..0.023 rows=0 loops=1)

34. 0.000 0.022 ↓ 0.0 0 1

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

35. 0.001 0.022 ↓ 0.0 0 1

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

36. 0.000 0.021 ↓ 0.0 0 1

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

37. 0.021 0.021 ↓ 0.0 0 1

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

  • Index Cond: (id = 291494)
  • Filter: ((state <> 3) AND ("timestamp" > (now() + '-02:00:00'::interval)))
  • Rows Removed by Filter: 1
38. 0.000 0.000 ↓ 0.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) (never executed)

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

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

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

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

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

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

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

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

  • Index Cond: (id = qb_join_1.answer_set_id)