explain.depesz.com

A tool for finding a real cause for slow queries.

Result: bPN

options
# exclusive inclusive rows x rows loops node
1. 0.309 60.484 ↓ 25.0 50 1
→

Group (cost=547.54..547.64 rows=2 width=743) (actual time=60.125..60.484 rows=50 loops=1)

2. 4.311 60.175 ↓ 40.0 80 1
→

Sort (cost=547.54..547.54 rows=2 width=743) (actual time=60.113..60.175 rows=80 loops=1)

  • Sort Key: sr.ward_id, sp.survey_profile_title, survey_questions.survey_question_no, sr.hospital_id, ho.hospital_prefix, ho.name, sr.ward_type_id, survey_questions.survey_question_id, sp.survey_master_profile_id, survey_questions.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))), res3.response_count, res3.answer_txt, (count(DISTINCT COALESCE((NULLIF(s0.person_code, 0))::character varying, s0.survey_session_id))), res2.free_datestamp, res2.set_answer, res4.set_datestamp, res2.response_count
  • Sort Method: quicksort Memory: 31kB
3. 0.194 55.864 ↓ 40.0 80 1
→

Hash Left Join (cost=62.64..547.53 rows=2 width=743) (actual time=53.897..55.864 rows=80 loops=1)

  • Hash Cond: ((sr.ward_id = res2.ward_id) AND (sr.survey_question_id = res2.survey_question_id))
4. 0.329 55.235 ↓ 40.0 80 1
→

Nested Loop Left Join (cost=44.99..529.85 rows=2 width=703) (actual time=53.438..55.235 rows=80 loops=1)

5. 0.213 54.586 ↓ 40.0 80 1
→

Nested Loop (cost=44.99..529.23 rows=2 width=688) (actual time=53.421..54.586 rows=80 loops=1)

  • Join Filter: (sr.hospital_id = ho.hospital_id)
6. 0.016 0.016 ↑ 1.0 1 1
→

Seq Scan on hospitals ho (cost=0.00..1.04 rows=1 width=540) (actual time=0.014..0.016 rows=1 loops=1)

  • Filter: ((hospital_prefix)::text = 'XW'::text)
7. 0.430 54.357 ↓ 80.0 160 1
→

Hash Left Join (cost=44.99..528.17 rows=2 width=152) (actual time=53.399..54.357 rows=160 loops=1)

  • Hash Cond: ((sr.ward_id = res3.ward_id) AND (sr.survey_question_id = res3.survey_question_id) AND ((unnest(string_to_array((survey_questions.survey_question_options)::text, '|'::text))) = res3.answer_txt))
8. 3.970 51.994 ↓ 80.0 160 1
→

Merge Right Join (cost=25.95..509.10 rows=2 width=104) (actual time=51.411..51.994 rows=160 loops=1)

  • Merge Cond: (survey_questions.survey_question_id = sr.survey_question_id)
9. 43.752 43.752 ↓ 5.0 4200 1
→

Index Scan using survey_question_id on survey_questions (cost=0.00..472.60 rows=841 width=75) (actual time=0.285..43.752 rows=4200 loops=1)

10. 0.196 4.272 ↓ 159.0 159 1
→

Sort (cost=25.95..25.96 rows=1 width=24) (actual time=4.145..4.272 rows=159 loops=1)

  • Sort Key: sr.survey_question_id
  • Sort Method: quicksort Memory: 18kB
11. 0.326 4.076 ↓ 32.0 32 1
→

Nested Loop Left Join (cost=9.33..25.94 rows=1 width=24) (actual time=0.511..4.076 rows=32 loops=1)

  • Join Filter: ((s0.ward_id = sr.ward_id) AND (s0.survey_question_id = sr.survey_question_id))
12. 0.116 0.422 ↓ 32.0 32 1
→

Nested Loop (cost=0.00..16.55 rows=1 width=16) (actual time=0.032..0.422 rows=32 loops=1)

13. 0.082 0.082 ↓ 32.0 32 1
→

Index Scan using idx_record_date on survey_result sr (cost=0.00..8.27 rows=1 width=20) (actual time=0.019..0.082 rows=32 loops=1)

  • Index Cond: ((record_date >= '2012-05-02 12:00:00'::timestamp without time zone) AND (record_date <= '2012-05-02 13:00:00'::timestamp without time zone))
14. 0.224 0.224 ↑ 1.0 1 32
→

Index Scan using survey_master_id on survey_master_record sm (cost=0.00..8.27 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=32)

  • Index Cond: (sm.survey_master_id = sr.survey_master_record_id)
  • Filter: ((NOT sm.hospedia_owned_survey) AND ((sm.survey_poll_schedule)::text <> ''::text))
15. 2.528 3.328 ↓ 8.0 8 32
→

GroupAggregate (cost=9.33..9.36 rows=1 width=31) (actual time=0.027..0.104 rows=8 loops=32)

16. 0.485 0.800 ↓ 16.0 16 32
→

Sort (cost=9.33..9.34 rows=1 width=31) (actual time=0.012..0.025 rows=16 loops=32)

  • Sort Key: s0.ward_id, s0.survey_question_id
  • Sort Method: quicksort Memory: 18kB
17. 0.124 0.315 ↓ 16.0 16 1
→

Nested Loop (cost=0.00..9.32 rows=1 width=31) (actual time=0.021..0.315 rows=16 loops=1)

  • Join Filter: (s0.hospital_id = h0.hospital_id)
18. 0.063 0.063 ↓ 32.0 32 1
→

Index Scan using idx_record_date on survey_result s0 (cost=0.00..8.27 rows=1 width=35) (actual time=0.010..0.063 rows=32 loops=1)

  • Index Cond: ((record_date >= '2012-05-02 12:00:00'::timestamp without time zone) AND (record_date <= '2012-05-02 13:00:00'::timestamp without time zone))
19. 0.128 0.128 ↑ 1.0 1 32
→

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

  • Filter: ((h0.hospital_prefix)::text = 'XW'::text)
20. 0.023 1.933 ↓ 14.0 14 1
→

Hash (cost=19.02..19.02 rows=1 width=56) (actual time=1.933..1.933 rows=14 loops=1)

21. 0.053 1.910 ↓ 14.0 14 1
→

Merge Left Join (cost=18.99..19.02 rows=1 width=56) (actual time=1.752..1.910 rows=14 loops=1)

  • Merge Cond: ((res3.ward_id = res4.ward_id) AND (res3.survey_question_id = res4.survey_question_id))
22. 0.045 1.378 ↓ 14.0 14 1
→

Sort (cost=9.63..9.63 rows=1 width=48) (actual time=1.367..1.378 rows=14 loops=1)

  • Sort Key: res3.ward_id, res3.survey_question_id
  • Sort Method: quicksort Memory: 17kB
23. 0.022 1.333 ↓ 14.0 14 1
→

Subquery Scan res3 (cost=9.59..9.62 rows=1 width=48) (actual time=1.293..1.333 rows=14 loops=1)

24. 0.070 1.311 ↓ 14.0 14 1
→

HashAggregate (cost=9.59..9.61 rows=1 width=40) (actual time=1.290..1.311 rows=14 loops=1)

25. 0.176 1.241 ↓ 16.0 16 1
→

Nested Loop (cost=0.00..9.58 rows=1 width=40) (actual time=0.252..1.241 rows=16 loops=1)

  • Join Filter: (survey_result.hospital_id = h1.hospital_id)
26. 0.905 0.905 ↓ 32.0 32 1
→

Index Scan using idx_record_date on survey_result (cost=0.00..8.52 rows=1 width=36) (actual time=0.224..0.905 rows=32 loops=1)

  • Index Cond: ((record_date >= '2012-05-02 12:00:00'::timestamp without time zone) AND (record_date <= '2012-05-02 13:00:00'::timestamp without time zone))
27. 0.160 0.160 ↑ 1.0 1 32
→

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

  • Filter: ((h1.hospital_prefix)::text = 'XW'::text)
28. 0.026 0.479 ↓ 13.0 13 1
→

Materialize (cost=9.36..9.37 rows=1 width=16) (actual time=0.378..0.479 rows=13 loops=1)

29. 0.017 0.453 ↓ 8.0 8 1
→

Subquery Scan res4 (cost=9.33..9.36 rows=1 width=16) (actual time=0.374..0.453 rows=8 loops=1)

30. 0.030 0.436 ↓ 8.0 8 1
→

Unique (cost=9.33..9.35 rows=1 width=16) (actual time=0.372..0.436 rows=8 loops=1)

31. 0.033 0.406 ↓ 16.0 16 1
→

Group (cost=9.33..9.34 rows=1 width=16) (actual time=0.363..0.406 rows=16 loops=1)

32. 0.054 0.373 ↓ 16.0 16 1
→

Sort (cost=9.33..9.34 rows=1 width=16) (actual time=0.361..0.373 rows=16 loops=1)

  • Sort Key: s3.ward_id, s3.survey_question_id, s3.record_date
  • Sort Method: quicksort Memory: 17kB
33. 0.124 0.319 ↓ 16.0 16 1
→

Nested Loop (cost=0.00..9.32 rows=1 width=16) (actual time=0.026..0.319 rows=16 loops=1)

  • Join Filter: (s3.hospital_id = h3.hospital_id)
34. 0.067 0.067 ↓ 32.0 32 1
→

Index Scan using idx_record_date on survey_result s3 (cost=0.00..8.27 rows=1 width=20) (actual time=0.014..0.067 rows=32 loops=1)

  • Index Cond: ((record_date >= '2012-05-02 12:00:00'::timestamp without time zone) AND (record_date <= '2012-05-02 13:00:00'::timestamp without time zone))
35. 0.128 0.128 ↑ 1.0 1 32
→

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

  • Filter: ((h3.hospital_prefix)::text = 'XW'::text)
36. 0.320 0.320 ↑ 1.0 1 80
→

Index Scan using survey_master_profile_id on survey_master_profile sp (cost=0.00..0.30 rows=1 width=19) (actual time=0.003..0.004 rows=1 loops=80)

  • Index Cond: (survey_questions.survey_master_profile_id = sp.survey_master_profile_id)
37. 0.002 0.435 ↓ 0.0 0 1
→

Hash (cost=17.64..17.64 rows=1 width=52) (actual time=0.435..0.435 rows=0 loops=1)

38. 0.002 0.433 ↓ 0.0 0 1
→

Subquery Scan res2 (cost=17.62..17.64 rows=1 width=52) (actual time=0.433..0.433 rows=0 loops=1)

39. 0.003 0.431 ↓ 0.0 0 1
→

HashAggregate (cost=17.62..17.63 rows=1 width=28) (actual time=0.431..0.431 rows=0 loops=1)

40. 0.028 0.428 ↓ 0.0 0 1
→

Nested Loop (cost=0.00..17.60 rows=1 width=28) (actual time=0.428..0.428 rows=0 loops=1)

41. 0.132 0.320 ↓ 16.0 16 1
→

Nested Loop (cost=0.00..9.32 rows=1 width=28) (actual time=0.024..0.320 rows=16 loops=1)

  • Join Filter: (s2.hospital_id = h2.hospital_id)
42. 0.060 0.060 ↓ 32.0 32 1
→

Index Scan using idx_record_date on survey_result s2 (cost=0.00..8.27 rows=1 width=32) (actual time=0.012..0.060 rows=32 loops=1)

  • Index Cond: ((record_date >= '2012-05-02 12:00:00'::timestamp without time zone) AND (record_date <= '2012-05-02 13:00:00'::timestamp without time zone))
43. 0.128 0.128 ↑ 1.0 1 32
→

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

  • Filter: ((h2.hospital_prefix)::text = 'XW'::text)
44. 0.080 0.080 ↓ 0.0 0 16
→

Index Scan using survey_question_id on survey_questions sq2 (cost=0.00..8.27 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=16)

  • Index Cond: (sq2.survey_question_id = s2.survey_question_id)
  • Filter: ((sq2.survey_question_options)::text = ''::text)