explain.depesz.com

A tool for finding a real cause for slow queries.

Result: lpYF

options
# exclusive inclusive rows x rows loops node
1. 0.022 19.092 ↓ 4.0 4 1
→

Group (cost=87.62..87.68 rows=1 width=210) (actual time=19.074..19.092 rows=4 loops=1)

2. 0.056 19.070 ↓ 4.0 4 1
→

Sort (cost=87.62..87.63 rows=1 width=210) (actual time=19.067..19.070 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.063 19.014 ↓ 4.0 4 1
→

Nested Loop Left Join (cost=73.40..87.61 rows=1 width=210) (actual time=13.864..19.014 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.963 ↓ 4.0 4 1
→

Nested Loop Left Join (cost=64.08..78.24 rows=1 width=206) (actual time=13.440..17.963 rows=4 loops=1)

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

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

6. 0.214 16.842 ↓ 4.0 4 1
→

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

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

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

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

Nested Loop Left Join (cost=52.46..61.88 rows=1 width=125) (actual time=2.248..15.221 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.385 5.652 ↓ 73.0 73 1
→

Nested Loop Left Join (cost=43.15..52.52 rows=1 width=125) (actual time=1.897..5.652 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.493 ↓ 73.0 73 1
→

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

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

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

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

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

  • Filter: ((hospital_prefix)::text = 'XB'::text)
13. 0.073 0.073 ↓ 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.024..0.073 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.249 0.999 ↓ 9.0 9 27
→

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

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

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

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

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

17. 0.005 0.181 ↓ 3.0 3 1
→

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

18. 0.072 0.176 ↓ 3.0 3 1
→

Hash IN Join (cost=29.21..30.73 rows=1 width=4) (actual time=0.142..0.176 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.047 ↑ 2.3 3 1
→

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

21. 0.026 0.042 ↑ 2.3 3 1
→

Hash Join (cost=1.04..29.12 rows=7 width=4) (actual time=0.031..0.042 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.003..0.005 rows=4 loops=1)

23. 0.006 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.005 0.005 ↑ 1.0 1 1
→

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

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

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

26. 0.145 0.956 ↓ 27.0 27 1
→

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

  • Join Filter: (survey_result.hospital_id = h1.hospital_id)
27. 0.676 0.676 ↓ 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.116..0.676 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.628 8.468 ↓ 12.0 12 73
→

Unique (cost=9.32..9.34 rows=1 width=16) (actual time=0.009..0.116 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.692 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.117 0.279 ↓ 27.0 27 1
→

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

  • Join Filter: (s3.hospital_id = h3.hospital_id)
33. 0.054 0.054 ↓ 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.054 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.204 0.204 ↓ 2.5 54 4
→

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

37. 0.028 0.028 ↑ 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.005..0.007 rows=1 loops=4)

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

Group (cost=11.62..11.64 rows=1 width=27) (actual time=0.255..0.263 rows=3 loops=4)

39. 0.040 1.024 ↓ 3.0 3 4
→

Sort (cost=11.62..11.62 rows=1 width=27) (actual time=0.253..0.256 rows=3 loops=4)

  • Sort Key: s2.ward_id, s2.survey_question_id, s2.answer, s2.record_date, (1)
  • Sort Method: quicksort Memory: 17kB
40. 0.018 0.984 ↓ 3.0 3 1
→

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

  • Join Filter: (s2.hospital_id = h2.hospital_id)
41. 0.172 0.954 ↓ 3.0 3 1
→

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

  • Join Filter: (s2.survey_question_id = sq2.survey_question_id)
42. 0.053 0.053 ↓ 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.010..0.053 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))
43. 0.729 0.729 ↓ 2.0 4 27
→

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

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

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

  • Filter: ((h2.hospital_prefix)::text = 'XB'::text)
45. 0.568 0.988 ↓ 12.0 12 4
→

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

46. 0.143 0.420 ↓ 27.0 27 4
→

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

  • Sort Key: s0.ward_id, s0.survey_question_id
  • Sort Method: quicksort Memory: 18kB
47. 0.122 0.277 ↓ 27.0 27 1
→

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

  • Join Filter: (s0.hospital_id = h0.hospital_id)
48. 0.047 0.047 ↓ 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.009..0.047 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))
49. 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)