explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3bLN

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=10,333,954.16..10,333,956.10 rows=1 width=456) (actual rows= loops=)

  • Group Key: chwlist.branch_uuid, chwlist.branch_name, 'multiple'::text, 'multiple'::text, 'multiple'::text, 'multiple'::text, 'multiple'::text, (date(chwlist.interval_start)), chwlist.interval_number
2.          

CTE chwlist

3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=8.51..353.34 rows=1 width=213) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=8.51..353.29 rows=1 width=201) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=8.23..352.97 rows=1 width=201) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Hash Join (cost=7.68..350.58 rows=1 width=185) (actual rows= loops=)

  • Hash Cond: (chp.branch_uuid = cmd.uuid)
7. 0.000 0.000 ↓ 0.0

Seq Scan on contactview_chp chp (cost=0.00..313.74 rows=7,774 width=166) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Hash (cost=6.58..6.58 rows=88 width=52) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Index Scan using contactview_metadata_type on contactview_metadata cmd (cost=0.43..6.58 rows=88 width=52) (actual rows= loops=)

  • Index Cond: (type = 'district_hospital'::text)
10. 0.000 0.000 ↓ 0.0

Index Scan using contactview_metadata_uuid on contactview_metadata cmeta (cost=0.56..2.38 rows=1 width=52) (actual rows= loops=)

  • Index Cond: (uuid = chp.supervisor_uuid)
11. 0.000 0.000 ↓ 0.0

Index Only Scan using chp_muting_chw on chp_muting (cost=0.28..0.30 rows=1 width=36) (actual rows= loops=)

  • Index Cond: (chw = chp.uuid)
12. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.02 rows=1 width=12) (actual rows= loops=)

13.          

CTE familyreg

14. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=61,219.93..61,219.95 rows=1 width=48) (actual rows= loops=)

  • Group Key: contactview_metadata.parent_uuid, 0
15. 0.000 0.000 ↓ 0.0

Sort (cost=61,219.93..61,219.94 rows=1 width=40) (actual rows= loops=)

  • Sort Key: contactview_metadata.parent_uuid
16. 0.000 0.000 ↓ 0.0

Index Scan using contactview_metadata_type on contactview_metadata (cost=0.43..61,219.92 rows=1 width=40) (actual rows= loops=)

  • Index Cond: (type = 'clinic'::text)
  • Filter: ((reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
17.          

CTE allpreg

18. 0.000 0.000 ↓ 0.0

HashAggregate (cost=305.33..309.18 rows=385 width=47) (actual rows= loops=)

  • Group Key: visit.chw, 0
19. 0.000 0.000 ↓ 0.0

Index Scan using useview_pregnancy_reported on useview_pregnancy visit (cost=0.45..302.33 rows=399 width=39) (actual rows= loops=)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
20.          

CTE pncvisit

21. 0.000 0.000 ↓ 0.0

HashAggregate (cost=243.09..245.30 rows=221 width=103) (actual rows= loops=)

  • Group Key: useview_postnatal_care.chw, 0
22. 0.000 0.000 ↓ 0.0

Index Scan using useview_postnatal_care_count_reported_uuid on useview_postnatal_care (cost=0.45..209.19 rows=226 width=61) (actual rows= loops=)

  • Index Cond: ((follow_up_count = '1'::text) AND (reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
23.          

CTE eddnopnc

24. 0.000 0.000 ↓ 0.0

HashAggregate (cost=575,091.42..575,093.42 rows=200 width=47) (actual rows= loops=)

  • Group Key: p.chw, 0
25. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=199,730.27..542,447.03 rows=4,352,585 width=39) (actual rows= loops=)

  • Merge Cond: (p.patient_id = useview_postnatal_care_1.patient_id)
  • Filter: (((max(useview_postnatal_care_1.reported)) IS NULL) OR ((@ ((CASE WHEN (max(pv.new_edd) <> ''::text) THEN (max(pv.new_edd))::date ELSE max(p.edd) END) - ((max(useview_postnatal_care_1.reported)))::date)) > 60))
26. 0.000 0.000 ↓ 0.0

Sort (cost=199,729.85..199,782.78 rows=21,172 width=75) (actual rows= loops=)

  • Sort Key: p.patient_id
27. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=197,150.05..197,996.93 rows=21,172 width=75) (actual rows= loops=)

  • Group Key: p.chw, p.patient_id
  • Filter: ((max(p.edd) >= date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone)) AND CASE WHEN (date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) = date_trunc('day'::text, now())) THEN (max(p.edd) <= (((now())::timestamp without time zone)::timestamp with time zone - '3 days'::interval)) ELSE (max(p.edd) <= (((now())::timestamp without time zone)::timestamp with time zone + '1 day'::interval)) END)
28. 0.000 0.000 ↓ 0.0

Sort (cost=197,150.05..197,202.98 rows=21,172 width=79) (actual rows= loops=)

  • Sort Key: p.chw, p.patient_id
29. 0.000 0.000 ↓ 0.0

Merge Anti Join (cost=193,675.48..195,628.86 rows=21,172 width=79) (actual rows= loops=)

  • Merge Cond: (p.patient_id = useview_pregnancy_visit_1.patient_id)
30. 0.000 0.000 ↓ 0.0

Sort (cost=89,985.64..90,091.50 rows=42,344 width=79) (actual rows= loops=)

  • Sort Key: p.patient_id
31. 0.000 0.000 ↓ 0.0

Hash Join (cost=70,716.05..86,731.53 rows=42,344 width=79) (actual rows= loops=)

  • Hash Cond: (p.patient_id = pv.patient_id)
  • Join Filter: (p.reported < pv.reported)
32. 0.000 0.000 ↓ 0.0

Seq Scan on useview_pregnancy p (cost=0.00..13,186.79 rows=330,879 width=83) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Hash (cost=69,362.32..69,362.32 rows=108,299 width=48) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Subquery Scan on pv (cost=67,544.26..69,362.32 rows=108,299 width=48) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Unique (cost=67,544.26..68,279.33 rows=108,299 width=80) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Sort (cost=67,544.26..67,911.79 rows=147,014 width=80) (actual rows= loops=)

  • Sort Key: useview_pregnancy_visit.patient_id, useview_pregnancy_visit.reported DESC
37. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on useview_pregnancy_visit (cost=4,976.74..54,926.34 rows=147,014 width=80) (actual rows= loops=)

  • Recheck Cond: ((reported > ((((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone - '9 mons'::interval)) OR (reported > (((now())::timestamp without time zone)::timestamp with time zone - '9 mons'::interval)))
  • Filter: (((edd_updated = 'TRUE'::text) AND (reported > ((((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone - '9 mons'::interval))) OR (reported > (((now())::timestamp without time zone)::timestamp with time zone - '9 mons'::interval)))
38. 0.000 0.000 ↓ 0.0

BitmapOr (cost=4,976.74..4,976.74 rows=308,315 width=0) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on useview_pregnancy_visit_reported_chw (cost=0.00..2,565.20 rows=161,301 width=0) (actual rows= loops=)

  • Index Cond: (reported > ((((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone - '9 mons'::interval))
40. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on useview_pregnancy_visit_reported_chw (cost=0.00..2,338.04 rows=147,014 width=0) (actual rows= loops=)

  • Index Cond: (reported > (((now())::timestamp without time zone)::timestamp with time zone - '9 mons'::interval))
41. 0.000 0.000 ↓ 0.0

Materialize (cost=103,689.84..105,064.76 rows=91,661 width=36) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Sort (cost=103,689.84..103,919.00 rows=91,661 width=44) (actual rows= loops=)

  • Sort Key: useview_pregnancy_visit_1.patient_id, useview_pregnancy_visit_1.reported DESC
43. 0.000 0.000 ↓ 0.0

Append (cost=64,437.31..96,135.14 rows=91,661 width=44) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Unique (cost=64,437.31..65,004.70 rows=89,710 width=44) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Sort (cost=64,437.31..64,721.01 rows=113,479 width=44) (actual rows= loops=)

  • Sort Key: useview_pregnancy_visit_1.patient_id
46. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on useview_pregnancy_visit useview_pregnancy_visit_1 (cost=4,959.97..54,909.57 rows=113,479 width=44) (actual rows= loops=)

  • Recheck Cond: ((reported > ((((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone - '9 mons'::interval)) OR (reported > (((now())::timestamp without time zone)::timestamp with time zone - '9 mons'::interval)))
  • Filter: (continue_follow_up <> 'yes'::text)
47. 0.000 0.000 ↓ 0.0

BitmapOr (cost=4,959.97..4,959.97 rows=308,315 width=0) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on useview_pregnancy_visit_reported_chw (cost=0.00..2,565.20 rows=161,301 width=0) (actual rows= loops=)

  • Index Cond: (reported > ((((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone - '9 mons'::interval))
49. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on useview_pregnancy_visit_reported_chw (cost=0.00..2,338.04 rows=147,014 width=0) (actual rows= loops=)

  • Index Cond: (reported > (((now())::timestamp without time zone)::timestamp with time zone - '9 mons'::interval))
50. 0.000 0.000 ↓ 0.0

Unique (cost=30,203.96..30,213.82 rows=1,951 width=44) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Sort (cost=30,203.96..30,208.89 rows=1,972 width=44) (actual rows= loops=)

  • Sort Key: useview_delivery_check.patient_id
52. 0.000 0.000 ↓ 0.0

Seq Scan on useview_delivery_check (cost=0.00..30,096.04 rows=1,972 width=44) (actual rows= loops=)

  • Filter: ((pregnancy_outcome = 'miscarriage'::text) AND ((reported > ((((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone - '9 mons'::interval)) OR (reported > (((now())::timestamp without time zone)::timestamp with time zone - '9 mons'::interval))))
53. 0.000 0.000 ↓ 0.0

Materialize (cost=0.42..19,704.89 rows=122,128 width=44) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=0.42..18,178.29 rows=122,128 width=44) (actual rows= loops=)

  • Group Key: useview_postnatal_care_1.patient_id
55. 0.000 0.000 ↓ 0.0

Index Scan using useview_postnatal_follow_up_count_patient_id on useview_postnatal_care useview_postnatal_care_1 (cost=0.42..16,084.50 rows=174,502 width=44) (actual rows= loops=)

  • Index Cond: (follow_up_count = '1'::text)
56.          

CTE assess

57. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=9,922.95..16,234.24 rows=5,075 width=279) (actual rows= loops=)

  • Group Key: useview_assessment.chw, 0
58. 0.000 0.000 ↓ 0.0

Sort (cost=9,922.95..9,974.69 rows=20,696 width=127) (actual rows= loops=)

  • Sort Key: useview_assessment.chw
59. 0.000 0.000 ↓ 0.0

Index Scan using useview_assessment_reported on useview_assessment (cost=0.46..8,439.35 rows=20,696 width=127) (actual rows= loops=)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
60.          

CTE on_time_follow_ups

61. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=13,690.92..13,702.52 rows=580 width=47) (actual rows= loops=)

  • Group Key: assess_1.chw, 0
62. 0.000 0.000 ↓ 0.0

Sort (cost=13,690.92..13,692.37 rows=580 width=39) (actual rows= loops=)

  • Sort Key: assess_1.chw
63. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4,939.05..13,664.30 rows=580 width=39) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on useview_assessment assess_1 (cost=4,938.50..8,298.50 rows=3,240 width=80) (actual rows= loops=)

  • Recheck Cond: ((reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone) AND (patient_age_in_years < 5) AND (referral_follow_up = 'true'::text))
65. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=4,938.50..4,938.50 rows=3,240 width=0) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on useview_assessment_reported_age_uuid (cost=0.00..464.29 rows=19,105 width=0) (actual rows= loops=)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone) AND (patient_age_in_years < 5))
67. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on useview_assessment_referral_follow_up (cost=0.00..4,472.35 rows=432,122 width=0) (actual rows= loops=)

  • Index Cond: (referral_follow_up = 'true'::text)
68. 0.000 0.000 ↓ 0.0

Index Only Scan using useview_assessment_follow_up_source_date_uuid on useview_assessment_follow_up follow_up (cost=0.55..1.65 rows=1 width=44) (actual rows= loops=)

  • Index Cond: (form_source_id = assess_1.uuid)
  • Filter: ((date(date_trunc('day'::text, reported)) - date(date_trunc('day'::text, assess_1.reported))) <= 2)
69.          

CTE familyregtotal

70. 0.000 0.000 ↓ 0.0

HashAggregate (cost=106,240.78..108,967.27 rows=272,649 width=56) (actual rows= loops=)

  • Group Key: cmeta_1.parent_uuid, 0
71. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=71,258.05..98,033.11 rows=820,767 width=76) (actual rows= loops=)

  • Hash Cond: (useview_family_survey.family_id = cmeta_1.uuid)
72. 0.000 0.000 ↓ 0.0

HashAggregate (cost=18,886.39..22,985.71 rows=409,932 width=36) (actual rows= loops=)

  • Group Key: useview_family_survey.family_id
73. 0.000 0.000 ↓ 0.0

Index Only Scan using useview_family_survey_equity_family_id on useview_family_survey (cost=0.43..17,371.77 rows=605,847 width=36) (actual rows= loops=)

  • Index Cond: (is_equity_survey = true)
  • Filter: is_equity_survey
74. 0.000 0.000 ↓ 0.0

Hash (cost=32,493.08..32,493.08 rows=820,767 width=72) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Index Scan using contactview_metadata_type on contactview_metadata cmeta_1 (cost=0.43..32,493.08 rows=820,767 width=72) (actual rows= loops=)

  • Index Cond: (type = 'clinic'::text)
76.          

CTE communityevent

77. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2,510.13..2,516.11 rows=598 width=47) (actual rows= loops=)

  • Group Key: meta.chw, 0
78. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on form_metadata meta (cost=1,843.32..2,505.39 rows=633 width=39) (actual rows= loops=)

  • Recheck Cond: ((formname = 'community_event'::text) AND (reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
79. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=1,843.32..1,843.32 rows=633 width=0) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on form_metadata_formname (cost=0.00..695.60 rows=59,089 width=0) (actual rows= loops=)

  • Index Cond: (formname = 'community_event'::text)
81. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on form_metadata_reported (cost=0.00..1,147.15 rows=89,169 width=0) (actual rows= loops=)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
82.          

CTE active_in_range

83. 0.000 0.000 ↓ 0.0

Seq Scan on contactview_chp chp_1 (cost=0.00..9,467,626.94 rows=7,774 width=44) (actual rows= loops=)

84.          

SubPlan (for Seq Scan)

85. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=1,171.42..1,188.95 rows=1 width=0) (actual rows= loops=)

  • Join Filter: (meta_1.formname = "*VALUES*".column1)
86. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on form_metadata meta_1 (cost=1,171.42..1,188.18 rows=16 width=14) (actual rows= loops=)

  • Recheck Cond: ((chw = chp_1.uuid) AND (reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
87. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=1,171.42..1,171.42 rows=16 width=0) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on form_metadata_chw (cost=0.00..24.01 rows=1,527 width=0) (actual rows= loops=)

  • Index Cond: (chw = chp_1.uuid)
89. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on form_metadata_reported (cost=0.00..1,147.15 rows=89,169 width=0) (actual rows= loops=)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
90. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..0.05 rows=3 width=32) (actual rows= loops=)

91. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=32) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=0.54..4,246.16 rows=7,431 width=35) (actual rows= loops=)

  • Hash Cond: (meta_2.formname = "*VALUES*_1".column1)
93. 0.000 0.000 ↓ 0.0

Index Scan using form_metadata_reported on form_metadata meta_2 (cost=0.46..3,929.35 rows=89,169 width=49) (actual rows= loops=)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
94. 0.000 0.000 ↓ 0.0

Hash (cost=0.04..0.04 rows=3 width=32) (actual rows= loops=)

95. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*_1" (cost=0.00..0.04 rows=3 width=32) (actual rows= loops=)

96. 0.000 0.000 ↓ 0.0

Index Scan using contactview_metadata_parent_uuid on contactview_metadata clinic (cost=0.56..14.18 rows=1 width=0) (actual rows= loops=)

  • Index Cond: (parent_uuid = chp_1.area_uuid)
  • Filter: ((reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
97. 0.000 0.000 ↓ 0.0

Seq Scan on contactview_metadata clinic_1 (cost=0.00..209,831.65 rows=1 width=36) (actual rows= loops=)

  • Filter: ((reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
98. 0.000 0.000 ↓ 0.0

Index Scan using useview_postnatal_reported_chw_patient_id on useview_postnatal_care pnc (cost=0.58..14.69 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone) AND (chw = chp_1.uuid))
  • Filter: (follow_up_count = '1'::text)
99. 0.000 0.000 ↓ 0.0

Index Scan using useview_postnatal_care_count_reported_uuid on useview_postnatal_care pnc_1 (cost=0.45..209.19 rows=226 width=35) (actual rows= loops=)

  • Index Cond: ((follow_up_count = '1'::text) AND (reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
100.          

CTE chp_visits

101. 0.000 0.000 ↓ 0.0

HashAggregate (cost=3,760.75..3,791.14 rows=2,431 width=40) (actual rows= loops=)

  • Group Key: (couchdb.doc #>> '{fields,inputs,contact,_id}'::text[]), 0
102. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.62..3,748.59 rows=2,431 width=36) (actual rows= loops=)

103. 0.000 0.000 ↓ 0.0

Unique (cost=0.04..0.04 rows=2 width=32) (actual rows= loops=)

104. 0.000 0.000 ↓ 0.0

Sort (cost=0.04..0.04 rows=2 width=32) (actual rows= loops=)

  • Sort Key: "*VALUES*_2".column1
105. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*_2" (cost=0.00..0.03 rows=2 width=32) (actual rows= loops=)

106. 0.000 0.000 ↓ 0.0

Index Scan using couchdb_doc_reported_date_form_contact on couchdb (cost=0.59..1,859.08 rows=1,216 width=455) (actual rows= loops=)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone) AND ((doc ->> 'form'::text) = "*VALUES*_2".column1))
107.          

CTE fp

108. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=3,410.92..3,666.92 rows=1,940 width=47) (actual rows= loops=)

  • Group Key: visit_1.chw, 0
109. 0.000 0.000 ↓ 0.0

Sort (cost=3,410.92..3,444.72 rows=13,520 width=81) (actual rows= loops=)

  • Sort Key: visit_1.chw
110. 0.000 0.000 ↓ 0.0

Index Scan using useview_fp_visit_reported_xmlforms_uuid on useview_fp_visit visit_1 (cost=0.45..2,483.26 rows=13,520 width=81) (actual rows= loops=)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
111.          

CTE sync

112. 0.000 0.000 ↓ 0.0

HashAggregate (cost=71,658.79..71,775.25 rows=5,823 width=48) (actual rows= loops=)

  • Group Key: useview_chp_sync.chw_uuid, 0
113. 0.000 0.000 ↓ 0.0

Seq Scan on useview_chp_sync (cost=0.00..55,513.74 rows=2,152,674 width=48) (actual rows= loops=)

114. 0.000 0.000 ↓ 0.0

Sort (cost=8,452.58..8,452.59 rows=7 width=624) (actual rows= loops=)

  • Sort Key: chwlist.branch_uuid, chwlist.branch_name, (date(chwlist.interval_start)), chwlist.interval_number
115. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=813.18..8,452.48 rows=7 width=624) (actual rows= loops=)

  • Hash Cond: ((chwlist.chw_uuid = sync.chw_uuid) AND (chwlist.interval_number = sync.interval_number))