explain.depesz.com

A tool for finding a real cause for slow queries.

Result: g5K

options
# exclusive inclusive rows x rows loops node
1. 0.315 84.091 ↓ 50.0 50 1
→

Group (cost=555.41..555.46 rows=1 width=734) (actual time=83.732..84.091 rows=50 loops=1)

2. 4.341 83.776 ↓ 80.0 80 1
→

Sort (cost=555.41..555.41 rows=1 width=734) (actual time=83.718..83.776 rows=80 loops=1)

  • Sort Key: sr.ward_id, sp.survey_profile_title, sq.survey_question_no, sr.hospital_id, ho.hospital_prefix, ho.name, sr.ward_type_id, sq.survey_question_id, sp.survey_master_profile_id, 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: 31kB
3. 0.879 79.435 ↓ 80.0 80 1
→

Nested Loop Left Join (cost=62.44..555.40 rows=1 width=734) (actual time=49.267..79.435 rows=80 loops=1)

  • Join Filter: ((s0.ward_id = sr.ward_id) AND (s0.survey_question_id = sr.survey_question_id))
4. 0.239 70.876 ↓ 80.0 80 1
→

Nested Loop Left Join (cost=53.11..546.01 rows=1 width=730) (actual time=48.776..70.876 rows=80 loops=1)

  • Join Filter: ((s2.ward_id = sr.ward_id) AND (s2.survey_question_id = sr.survey_question_id))
5. 0.283 70.157 ↓ 80.0 80 1
→

Nested Loop Left Join (cost=35.49..528.36 rows=1 width=686) (actual time=48.352..70.157 rows=80 loops=1)

6. 0.336 69.474 ↓ 80.0 80 1
→

Nested Loop Left Join (cost=35.49..528.05 rows=1 width=671) (actual time=48.339..69.474 rows=80 loops=1)

7. 0.692 68.658 ↓ 80.0 80 1
→

Nested Loop (cost=35.49..519.77 rows=1 width=636) (actual time=48.326..68.658 rows=80 loops=1)

  • Join Filter: (sr.hospital_id = ho.hospital_id)
8. 1.613 67.166 ↓ 160.0 160 1
→

Nested Loop Left Join (cost=35.49..518.72 rows=1 width=100) (actual time=48.315..67.166 rows=160 loops=1)

  • Join Filter: ((survey_result.ward_id = s3.ward_id) AND (survey_result.survey_question_id = s3.survey_question_id))
9. 2.463 54.033 ↓ 160.0 160 1
→

Nested Loop Left Join (cost=26.16..509.35 rows=1 width=100) (actual time=47.875..54.033 rows=160 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. 3.936 47.250 ↓ 160.0 160 1
→

Merge Right Join (cost=16.56..499.71 rows=1 width=52) (actual time=46.593..47.250 rows=160 loops=1)

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

Index Scan using survey_question_id on survey_questions (cost=0.00..472.60 rows=841 width=44) (actual time=0.341..42.760 rows=4200 loops=1)

12. 0.196 0.554 ↓ 159.0 159 1
→

Sort (cost=16.56..16.57 rows=1 width=16) (actual time=0.419..0.554 rows=159 loops=1)

  • Sort Key: sr.survey_question_id
  • Sort Method: quicksort Memory: 18kB
13. 0.122 0.358 ↓ 32.0 32 1
→

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

14. 0.076 0.076 ↓ 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.022..0.076 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))
15. 0.160 0.160 ↑ 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.004..0.005 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))
16. 3.101 4.320 ↓ 14.0 14 160
→

HashAggregate (cost=9.59..9.61 rows=1 width=40) (actual time=0.009..0.027 rows=14 loops=160)

17. 0.164 1.219 ↓ 16.0 16 1
→

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

  • Join Filter: (survey_result.hospital_id = h1.hospital_id)
18. 0.895 0.895 ↓ 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.210..0.895 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.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)
20. 3.840 11.520 ↓ 8.0 8 160
→

Unique (cost=9.33..9.35 rows=1 width=16) (actual time=0.007..0.072 rows=8 loops=160)

21. 5.120 7.680 ↓ 16.0 16 160
→

Group (cost=9.33..9.34 rows=1 width=16) (actual time=0.005..0.048 rows=16 loops=160)

22. 2.222 2.560 ↓ 16.0 16 160
→

Sort (cost=9.33..9.34 rows=1 width=16) (actual time=0.003..0.016 rows=16 loops=160)

  • Sort Key: s3.ward_id, s3.survey_question_id, s3.record_date
  • Sort Method: quicksort Memory: 17kB
23. 0.146 0.338 ↓ 16.0 16 1
→

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

  • Join Filter: (s3.hospital_id = h3.hospital_id)
24. 0.064 0.064 ↓ 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.064 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))
25. 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)
26. 0.800 0.800 ↑ 1.0 1 160
→

Seq Scan on hospitals ho (cost=0.00..1.04 rows=1 width=540) (actual time=0.004..0.005 rows=1 loops=160)

  • Filter: ((ho.hospital_prefix)::text = 'XW'::text)
27. 0.480 0.480 ↑ 1.0 1 80
→

Index Scan using survey_question_id on survey_questions sq (cost=0.00..8.27 rows=1 width=35) (actual time=0.005..0.006 rows=1 loops=80)

  • Index Cond: (sr.survey_question_id = sq.survey_question_id)
28. 0.400 0.400 ↑ 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.005 rows=1 loops=80)

  • Index Cond: (sq.survey_master_profile_id = sp.survey_master_profile_id)
29. 0.062 0.480 ↓ 0.0 0 80
→

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

30. 0.037 0.418 ↓ 0.0 0 1
→

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

31. 0.129 0.317 ↓ 16.0 16 1
→

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

  • Join Filter: (s2.hospital_id = h2.hospital_id)
32. 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.010..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))
33. 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)
34. 0.064 0.064 ↓ 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.004..0.004 rows=0 loops=16)

  • Index Cond: (sq2.survey_question_id = s2.survey_question_id)
  • Filter: ((sq2.survey_question_options)::text = ''::text)
35. 6.240 7.680 ↓ 8.0 8 80
→

GroupAggregate (cost=9.33..9.36 rows=1 width=31) (actual time=0.021..0.096 rows=8 loops=80)

36. 1.130 1.440 ↓ 16.0 16 80
→

Sort (cost=9.33..9.34 rows=1 width=31) (actual time=0.005..0.018 rows=16 loops=80)

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

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

  • Join Filter: (s0.hospital_id = h0.hospital_id)
38. 0.058 0.058 ↓ 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.011..0.058 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))
39. 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)