explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 4Fd

options
# exclusive inclusive rows x rows loops node
1. 0.031 18.940 ↓ 4.0 4 1
→

Group (cost=87.62..87.67 rows=1 width=210) (actual time=18.913..18.940 rows=4 loops=1)

2. 0.051 18.909 ↓ 4.0 4 1
→

Sort (cost=87.62..87.62 rows=1 width=210) (actual time=18.906..18.909 rows=4 loops=1)

  • Sort Key: sr.ward_id, sp.survey_master_profile_id, sq.survey_question_no, sr.hospital_id, ho.hospital_prefix, ho.name, sr.ward_type_id, sq.survey_question_id, sp.survey_profile_title, sq.survey_question, (unnest_index(string_to_array((survey_questions.survey_question_options)::text, '|'::text))), (unnest(string_to_array((survey_questions.survey_question_options)::text, '|'::text))), (count((unnest(string_to_array((survey_result.answer)::text, '|'::text))))), (unnest(string_to_array((survey_result.answer)::text, '|'::text))), (count(DISTINCT COALESCE((NULLIF(s0.person_code, 0))::character varying, s0.survey_session_id))), s2.record_date, s2.answer, s3.record_date, (1)
  • Sort Method: quicksort Memory: 17kB
3. 0.057 18.858 ↓ 4.0 4 1
→

Nested Loop Left Join (cost=73.40..87.61 rows=1 width=210) (actual time=13.723..18.858 rows=4 loops=1)

  • Join Filter: ((s0.ward_id = sr.ward_id) AND (s0.survey_question_id = sr.survey_question_id))
4. 0.025 17.825 ↓ 4.0 4 1
→

Nested Loop Left Join (cost=64.08..78.23 rows=1 width=206) (actual time=13.312..17.825 rows=4 loops=1)

  • Join Filter: ((s2.ward_id = sr.ward_id) AND (s2.survey_question_id = sr.survey_question_id))
5. 0.019 16.784 ↓ 4.0 4 1
→

Nested Loop Left Join (cost=52.46..66.58 rows=1 width=162) (actual time=12.308..16.784 rows=4 loops=1)

6. 0.206 16.741 ↓ 4.0 4 1
→

Nested Loop Left Join (cost=52.46..65.75 rows=1 width=149) (actual time=12.298..16.741 rows=4 loops=1)

  • Join Filter: (sr.survey_question_id = sq.survey_question_id)
7. 0.407 16.339 ↓ 4.0 4 1
→

Nested Loop (cost=52.46..63.26 rows=1 width=121) (actual time=12.213..16.339 rows=4 loops=1)

  • Join Filter: (sr.survey_master_record_id = sm.survey_master_id)
8. 0.967 15.129 ↓ 73.0 73 1
→

Nested Loop Left Join (cost=52.46..61.88 rows=1 width=125) (actual time=2.211..15.129 rows=73 loops=1)

  • Join Filter: ((survey_result.ward_id = s3.ward_id) AND (survey_result.survey_question_id = s3.survey_question_id))
9. 1.342 5.621 ↓ 73.0 73 1
→

Nested Loop Left Join (cost=43.15..52.52 rows=1 width=125) (actual time=1.857..5.621 rows=73 loops=1)

  • Join Filter: ((survey_result.ward_id = sr.ward_id) AND (survey_result.survey_question_id = sr.survey_question_id) AND ((unnest(string_to_array((survey_result.answer)::text, '|'::text))) = (unnest(string_to_array((survey_questions.survey_question_options)::text, '|'::text)))))
10. 0.348 1.432 ↓ 73.0 73 1
→

Nested Loop Left Join (cost=33.57..42.90 rows=1 width=77) (actual time=0.783..1.432 rows=73 loops=1)

  • Join Filter: (sr.survey_question_id = survey_questions.survey_question_id)
11. 0.057 0.139 ↓ 27.0 27 1
→

Nested Loop (cost=0.00..9.31 rows=1 width=41) (actual time=0.037..0.139 rows=27 loops=1)

  • Join Filter: (ho.hospital_id = sr.hospital_id)
12. 0.015 0.015 ↑ 1.0 1 1
→

Seq Scan on hospitals ho (cost=0.00..1.02 rows=1 width=25) (actual time=0.014..0.015 rows=1 loops=1)

  • Filter: ((hospital_prefix)::text = 'XB'::text)
13. 0.067 0.067 ↓ 27.0 27 1
→

Index Scan using idx_record_date on survey_result sr (cost=0.00..8.27 rows=1 width=20) (actual time=0.018..0.067 rows=27 loops=1)

  • Index Cond: ((sr.record_date >= '2012-04-04 09:00:00'::timestamp without time zone) AND (sr.record_date <= '2012-04-24 16:00:00'::timestamp without time zone))
14. 0.234 0.945 ↓ 9.0 9 27
→

Sort (cost=33.57..33.57 rows=1 width=29) (actual time=0.028..0.035 rows=9 loops=27)

  • Sort Key: survey_questions.survey_question_id
  • Sort Method: quicksort Memory: 17kB
15. 0.483 0.711 ↓ 9.0 9 1
→

Hash IN Join (cost=30.74..33.56 rows=1 width=29) (actual time=0.515..0.711 rows=9 loops=1)

  • Hash Cond: (survey_questions.survey_master_profile_id = survey_master_profile.survey_master_profile_id)
16. 0.051 0.051 ↓ 2.5 54 1
→

Seq Scan on survey_questions (cost=0.00..2.22 rows=22 width=33) (actual time=0.004..0.051 rows=54 loops=1)

17. 0.005 0.177 ↓ 3.0 3 1
→

Hash (cost=30.73..30.73 rows=1 width=4) (actual time=0.177..0.177 rows=3 loops=1)

18. 0.070 0.172 ↓ 3.0 3 1
→

Hash IN Join (cost=29.21..30.73 rows=1 width=4) (actual time=0.139..0.172 rows=3 loops=1)

  • Hash Cond: (survey_master_profile.survey_master_id = so.survey_master_record_id)
19. 0.057 0.057 ↓ 1.6 60 1
→

Seq Scan on survey_master_profile (cost=0.00..1.37 rows=37 width=8) (actual time=0.007..0.057 rows=60 loops=1)

20. 0.005 0.045 ↑ 2.3 3 1
→

Hash (cost=29.12..29.12 rows=7 width=4) (actual time=0.045..0.045 rows=3 loops=1)

21. 0.024 0.040 ↑ 2.3 3 1
→

Hash Join (cost=1.04..29.12 rows=7 width=4) (actual time=0.029..0.040 rows=3 loops=1)

  • Hash Cond: (so.hospital_id = h4.hospital_id)
22. 0.005 0.005 ↑ 327.5 4 1
→

Seq Scan on survey_owner so (cost=0.00..23.10 rows=1310 width=8) (actual time=0.002..0.005 rows=4 loops=1)

23. 0.004 0.011 ↑ 1.0 1 1
→

Hash (cost=1.02..1.02 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=1)

24. 0.007 0.007 ↑ 1.0 1 1
→

Seq Scan on hospitals h4 (cost=0.00..1.02 rows=1 width=4) (actual time=0.005..0.007 rows=1 loops=1)

  • Filter: ((hospital_prefix)::text = 'XB'::text)
25. 1.872 2.847 ↓ 18.0 18 73
→

HashAggregate (cost=9.58..9.59 rows=1 width=40) (actual time=0.016..0.039 rows=18 loops=73)

26. 0.148 0.975 ↓ 27.0 27 1
→

Nested Loop (cost=0.00..9.57 rows=1 width=40) (actual time=0.125..0.975 rows=27 loops=1)

  • Join Filter: (survey_result.hospital_id = h1.hospital_id)
27. 0.692 0.692 ↓ 27.0 27 1
→

Index Scan using idx_record_date on survey_result (cost=0.00..8.52 rows=1 width=35) (actual time=0.112..0.692 rows=27 loops=1)

  • Index Cond: ((record_date >= '2012-04-04 09:00:00'::timestamp without time zone) AND (record_date <= '2012-04-24 16:00:00'::timestamp without time zone))
28. 0.135 0.135 ↑ 1.0 1 27
→

Seq Scan on hospitals h1 (cost=0.00..1.02 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=27)

  • Filter: ((h1.hospital_prefix)::text = 'XB'::text)
29. 2.701 8.541 ↓ 12.0 12 73
→

Unique (cost=9.32..9.34 rows=1 width=16) (actual time=0.009..0.117 rows=12 loops=73)

30. 3.869 5.840 ↓ 27.0 27 73
→

Group (cost=9.32..9.33 rows=1 width=16) (actual time=0.007..0.080 rows=27 loops=73)

31. 1.693 1.971 ↓ 27.0 27 73
→

Sort (cost=9.32..9.32 rows=1 width=16) (actual time=0.005..0.027 rows=27 loops=73)

  • Sort Key: s3.ward_id, s3.survey_question_id, s3.record_date
  • Sort Method: quicksort Memory: 18kB
32. 0.115 0.278 ↓ 27.0 27 1
→

Nested Loop (cost=0.00..9.31 rows=1 width=16) (actual time=0.019..0.278 rows=27 loops=1)

  • Join Filter: (s3.hospital_id = h3.hospital_id)
33. 0.055 0.055 ↓ 27.0 27 1
→

Index Scan using idx_record_date on survey_result s3 (cost=0.00..8.27 rows=1 width=20) (actual time=0.011..0.055 rows=27 loops=1)

  • Index Cond: ((record_date >= '2012-04-04 09:00:00'::timestamp without time zone) AND (record_date <= '2012-04-24 16:00:00'::timestamp without time zone))
34. 0.108 0.108 ↑ 1.0 1 27
→

Seq Scan on hospitals h3 (cost=0.00..1.02 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=27)

  • Filter: ((h3.hospital_prefix)::text = 'XB'::text)
35. 0.803 0.803 ↓ 3.0 3 73
→

Seq Scan on survey_master_record sm (cost=0.00..1.36 rows=1 width=4) (actual time=0.007..0.011 rows=3 loops=73)

  • Filter: ((NOT sm.hospedia_owned_survey) AND ((sm.survey_poll_schedule)::text = ''::text))
36. 0.196 0.196 ↓ 2.5 54 4
→

Seq Scan on survey_questions sq (cost=0.00..2.22 rows=22 width=28) (actual time=0.003..0.049 rows=54 loops=4)

37. 0.024 0.024 ↑ 1.0 1 4
→

Index Scan using survey_master_profile_id on survey_master_profile sp (cost=0.00..0.81 rows=1 width=17) (actual time=0.004..0.006 rows=1 loops=4)

  • Index Cond: (sq.survey_master_profile_id = sp.survey_master_profile_id)
38. 0.028 1.016 ↓ 3.0 3 4
→

HashAggregate (cost=11.62..11.63 rows=1 width=27) (actual time=0.251..0.254 rows=3 loops=4)

39. 0.014 0.988 ↓ 3.0 3 1
→

Nested Loop (cost=0.00..11.61 rows=1 width=27) (actual time=0.631..0.988 rows=3 loops=1)

  • Join Filter: (s2.hospital_id = h2.hospital_id)
40. 0.154 0.959 ↓ 3.0 3 1
→

Nested Loop (cost=0.00..10.57 rows=1 width=31) (actual time=0.622..0.959 rows=3 loops=1)

  • Join Filter: (s2.survey_question_id = sq2.survey_question_id)
41. 0.049 0.049 ↓ 27.0 27 1
→

Index Scan using idx_record_date on survey_result s2 (cost=0.00..8.27 rows=1 width=31) (actual time=0.008..0.049 rows=27 loops=1)

  • Index Cond: ((record_date >= '2012-04-04 09:00:00'::timestamp without time zone) AND (record_date <= '2012-04-24 16:00:00'::timestamp without time zone))
42. 0.756 0.756 ↓ 2.0 4 27
→

Seq Scan on survey_questions sq2 (cost=0.00..2.27 rows=2 width=4) (actual time=0.021..0.028 rows=4 loops=27)

  • Filter: ((sq2.survey_question_options)::text = ''::text)
43. 0.015 0.015 ↑ 1.0 1 3
→

Seq Scan on hospitals h2 (cost=0.00..1.02 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=3)

  • Filter: ((h2.hospital_prefix)::text = 'XB'::text)
44. 0.560 0.976 ↓ 12.0 12 4
→

GroupAggregate (cost=9.32..9.35 rows=1 width=32) (actual time=0.097..0.244 rows=12 loops=4)

45. 0.142 0.416 ↓ 27.0 27 4
→

Sort (cost=9.32..9.32 rows=1 width=32) (actual time=0.083..0.104 rows=27 loops=4)

  • Sort Key: s0.ward_id, s0.survey_question_id
  • Sort Method: quicksort Memory: 18kB
46. 0.116 0.274 ↓ 27.0 27 1
→

Nested Loop (cost=0.00..9.31 rows=1 width=32) (actual time=0.019..0.274 rows=27 loops=1)

  • Join Filter: (s0.hospital_id = h0.hospital_id)
47. 0.050 0.050 ↓ 27.0 27 1
→

Index Scan using idx_record_date on survey_result s0 (cost=0.00..8.27 rows=1 width=36) (actual time=0.010..0.050 rows=27 loops=1)

  • Index Cond: ((record_date >= '2012-04-04 09:00:00'::timestamp without time zone) AND (record_date <= '2012-04-24 16:00:00'::timestamp without time zone))
48. 0.108 0.108 ↑ 1.0 1 27
→

Seq Scan on hospitals h0 (cost=0.00..1.02 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=27)

  • Filter: ((h0.hospital_prefix)::text = 'XB'::text)