explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pTUO

Settings
# exclusive inclusive rows x rows loops node
1. 107.548 12,249.968 ↓ 5,488.0 5,488 1

GroupAggregate (cost=3,507,024.94..3,507,026.89 rows=1 width=456) (actual time=12,141.168..12,249.968 rows=5,488 loops=1)

  • Group Key: chwlist.branch_uuid, chwlist.branch_name, chwlist.supervisor_uuid, chwlist.supervisor_name, chwlist.chw_uuid, chwlist.chw_name, chwlist.chw_phone, (date(chwlist.interval_start)), chwlist.interval_number
2.          

CTE chwlist

3. 1.231 83.102 ↓ 5,597.0 5,597 1

Nested Loop (cost=8.80..353.63 rows=1 width=213) (actual time=0.125..83.102 rows=5,597 loops=1)

4. 0.000 76.274 ↓ 5,597.0 5,597 1

Nested Loop Anti Join (cost=8.80..353.59 rows=1 width=201) (actual time=0.111..76.274 rows=5,597 loops=1)

5. 2.258 53.587 ↓ 7,567.0 7,567 1

Nested Loop (cost=8.52..353.27 rows=1 width=201) (actual time=0.096..53.587 rows=7,567 loops=1)

6. 4.102 5.651 ↓ 7,613.0 7,613 1

Hash Join (cost=7.97..350.87 rows=1 width=185) (actual time=0.071..5.651 rows=7,613 loops=1)

  • Hash Cond: (chp.branch_uuid = cmd.uuid)
7. 1.504 1.504 ↑ 1.0 7,616 1

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

8. 0.012 0.045 ↑ 3.8 25 1

Hash (cost=6.79..6.79 rows=94 width=52) (actual time=0.045..0.045 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
9. 0.033 0.033 ↑ 3.8 25 1

Index Scan using contactview_metadata_type on contactview_metadata cmd (cost=0.43..6.79 rows=94 width=52) (actual time=0.020..0.033 rows=25 loops=1)

  • Index Cond: (type = 'district_hospital'::text)
10. 45.678 45.678 ↑ 1.0 1 7,613

Index Scan using contactview_metadata_uuid on contactview_metadata cmeta (cost=0.56..2.39 rows=1 width=52) (actual time=0.006..0.006 rows=1 loops=7,613)

  • Index Cond: (uuid = chp.supervisor_uuid)
11. 22.701 22.701 ↓ 0.0 0 7,567

Index Only Scan using chp_muting_chw on chp_muting (cost=0.28..0.30 rows=1 width=36) (actual time=0.003..0.003 rows=0 loops=7,567)

  • Index Cond: (chw = chp.uuid)
  • Heap Fetches: 195
12. 5.597 5.597 ↑ 1.0 1 5,597

Result (cost=0.00..0.02 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=5,597)

13.          

CTE familyreg

14. 11.882 998.511 ↑ 4.2 2,858 1

HashAggregate (cost=62,756.85..62,877.10 rows=12,025 width=48) (actual time=997.570..998.511 rows=2,858 loops=1)

  • Group Key: contactview_metadata.parent_uuid, 0
15. 986.629 986.629 ↓ 2.4 29,495 1

Index Scan using contactview_metadata_type on contactview_metadata (cost=0.43..62,664.85 rows=12,267 width=40) (actual time=0.578..986.629 rows=29,495 loops=1)

  • Index Cond: (type = 'clinic'::text)
  • Filter: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 mon'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 782425
16.          

CTE allpreg

17. 4.052 11.765 ↓ 1.0 3,060 1

HashAggregate (cost=2,712.66..2,741.92 rows=2,926 width=47) (actual time=11.154..11.765 rows=3,060 loops=1)

  • Group Key: visit.chw, 0
18. 7.713 7.713 ↓ 2.3 9,563 1

Index Scan using useview_pregnancy_reported on useview_pregnancy visit (cost=0.45..2,681.16 rows=4,200 width=39) (actual time=0.055..7.713 rows=9,563 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 mon'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
19.          

CTE pncvisit

20. 31.582 39.884 ↓ 1.5 2,618 1

HashAggregate (cost=2,130.79..2,147.96 rows=1,717 width=103) (actual time=38.994..39.884 rows=2,618 loops=1)

  • Group Key: useview_postnatal_care.chw, 0
21. 8.302 8.302 ↓ 2.7 5,757 1

Index Scan using useview_postnatal_care_count_reported_uuid on useview_postnatal_care (cost=0.45..1,813.99 rows=2,112 width=61) (actual time=0.092..8.302 rows=5,757 loops=1)

  • Index Cond: ((follow_up_count = '1'::text) AND (reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 mon'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
22.          

CTE eddnopnc

23. 1.970 2,418.105 ↓ 7.4 1,476 1

HashAggregate (cost=599,821.02..599,823.02 rows=200 width=47) (actual time=2,417.756..2,418.105 rows=1,476 loops=1)

  • Group Key: p.chw, 0
24. 53.061 2,416.135 ↑ 1,963.9 2,349 1

Merge Left Join (cost=203,141.14..565,221.54 rows=4,613,263 width=39) (actual time=2,067.197..2,416.135 rows=2,349 loops=1)

  • 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))
  • Rows Removed by Filter: 3787
25. 11.408 2,068.031 ↑ 3.7 6,136 1

Sort (cost=203,140.72..203,196.82 rows=22,440 width=75) (actual time=2,066.312..2,068.031 rows=6,136 loops=1)

  • Sort Key: p.patient_id
  • Sort Method: quicksort Memory: 1055kB
26. 156.717 2,056.623 ↑ 3.7 6,136 1

GroupAggregate (cost=200,397.01..201,294.61 rows=22,440 width=75) (actual time=1,884.882..2,056.623 rows=6,136 loops=1)

  • Group Key: p.chw, p.patient_id
  • Filter: ((max(p.edd) >= date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 mon'::interval))) AND CASE WHEN (date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 day'::interval)) = date_trunc('day'::text, now())) THEN (max(p.edd) <= ((date_trunc('month'::text, now()) - '1 day'::interval) - '3 days'::interval)) ELSE (max(p.edd) <= ((date_trunc('month'::text, now()) - '1 day'::interval) + '1 day'::interval)) END)
  • Rows Removed by Filter: 51032
27. 206.307 1,899.906 ↓ 2.9 65,382 1

Sort (cost=200,397.01..200,453.11 rows=22,440 width=79) (actual time=1,884.834..1,899.906 rows=65,382 loops=1)

  • Sort Key: p.chw, p.patient_id
  • Sort Method: quicksort Memory: 10731kB
28. 36.522 1,693.599 ↓ 2.9 65,382 1

Merge Anti Join (cost=196,710.65..198,775.29 rows=22,440 width=79) (actual time=1,634.915..1,693.599 rows=65,382 loops=1)

  • Merge Cond: (p.patient_id = useview_pregnancy_visit_1.patient_id)
29. 225.068 1,290.207 ↓ 1.8 82,901 1

Sort (cost=92,307.64..92,419.84 rows=44,881 width=79) (actual time=1,272.042..1,290.207 rows=82,901 loops=1)

  • Sort Key: p.patient_id
  • Sort Method: quicksort Memory: 14730kB
30. 135.948 1,065.139 ↓ 1.8 82,901 1

Hash Join (cost=72,729.11..88,839.72 rows=44,881 width=79) (actual time=888.736..1,065.139 rows=82,901 loops=1)

  • Hash Cond: (p.patient_id = pv.patient_id)
  • Join Filter: (p.reported < pv.reported)
  • Rows Removed by Join Filter: 774
31. 40.787 40.787 ↑ 1.0 329,458 1

Seq Scan on useview_pregnancy p (cost=0.00..13,186.79 rows=330,879 width=83) (actual time=0.008..40.787 rows=329,458 loops=1)

32. 17.450 888.404 ↑ 1.6 71,786 1

Hash (cost=71,294.26..71,294.26 rows=114,788 width=48) (actual time=888.404..888.404 rows=71,786 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 7265kB
33. 11.276 870.954 ↑ 1.6 71,786 1

Subquery Scan on pv (cost=69,345.20..71,294.26 rows=114,788 width=48) (actual time=799.478..870.954 rows=71,786 loops=1)

34. 24.838 859.678 ↑ 1.6 71,786 1

Unique (cost=69,345.20..70,146.38 rows=114,788 width=80) (actual time=799.476..859.678 rows=71,786 loops=1)

35. 529.731 834.840 ↓ 1.1 181,404 1

Sort (cost=69,345.20..69,745.79 rows=160,236 width=80) (actual time=799.471..834.840 rows=181,404 loops=1)

  • Sort Key: useview_pregnancy_visit.patient_id, useview_pregnancy_visit.reported DESC
  • Sort Method: quicksort Memory: 31654kB
36. 270.383 305.109 ↓ 1.1 181,404 1

Bitmap Heap Scan on useview_pregnancy_visit (cost=5,379.32..55,492.93 rows=160,236 width=80) (actual time=38.916..305.109 rows=181,404 loops=1)

  • Recheck Cond: ((reported > ((date_trunc('month'::text, now()) - '1 mon'::interval) - '9 mons'::interval)) OR (reported > ((date_trunc('month'::text, now()) - '1 day'::interval) - '9 mons'::interval)))
  • Filter: (((edd_updated = 'TRUE'::text) AND (reported > ((date_trunc('month'::text, now()) - '1 mon'::interval) - '9 mons'::interval))) OR (reported > ((date_trunc('month'::text, now()) - '1 day'::interval) - '9 mons'::interval)))
  • Rows Removed by Filter: 12149
  • Heap Blocks: exact=23665
37. 0.002 34.726 ↓ 0.0 0 1

BitmapOr (cost=5,379.32..5,379.32 rows=333,243 width=0) (actual time=34.726..34.726 rows=0 loops=1)

38. 19.217 19.217 ↓ 1.1 193,555 1

Bitmap Index Scan on useview_pregnancy_visit_reported_chw (cost=0.00..2,750.99 rows=173,008 width=0) (actual time=19.217..19.217 rows=193,555 loops=1)

  • Index Cond: (reported > ((date_trunc('month'::text, now()) - '1 mon'::interval) - '9 mons'::interval))
39. 15.507 15.507 ↓ 1.1 181,406 1

Bitmap Index Scan on useview_pregnancy_visit_reported_chw (cost=0.00..2,548.20 rows=160,236 width=0) (actual time=15.507..15.507 rows=181,406 loops=1)

  • Index Cond: (reported > ((date_trunc('month'::text, now()) - '1 day'::interval) - '9 mons'::interval))
40. 2.165 366.870 ↑ 6.1 15,724 1

Materialize (cost=104,403.01..105,848.91 rows=96,393 width=36) (actual time=362.864..366.870 rows=15,724 loops=1)

41. 50.648 364.705 ↑ 6.1 15,724 1

Sort (cost=104,403.01..104,643.99 rows=96,393 width=44) (actual time=362.860..364.705 rows=15,724 loops=1)

  • Sort Key: useview_pregnancy_visit_1.patient_id, useview_pregnancy_visit_1.reported DESC
  • Sort Method: quicksort Memory: 1613kB
42. 1.692 314.057 ↑ 6.1 15,724 1

Append (cost=65,720.59..96,423.29 rows=96,393 width=44) (actual time=178.380..314.057 rows=15,724 loops=1)

43. 3.365 184.029 ↑ 6.0 15,712 1

Unique (cost=65,720.59..66,327.33 rows=94,358 width=44) (actual time=178.378..184.029 rows=15,712 loops=1)

44. 35.513 180.664 ↑ 7.4 16,341 1

Sort (cost=65,720.59..66,023.96 rows=121,348 width=44) (actual time=178.377..180.664 rows=16,341 loops=1)

  • Sort Key: useview_pregnancy_visit_1.patient_id
  • Sort Method: quicksort Memory: 1661kB
45. 107.384 145.151 ↑ 7.4 16,341 1

Bitmap Heap Scan on useview_pregnancy_visit useview_pregnancy_visit_1 (cost=5,359.87..55,473.49 rows=121,348 width=44) (actual time=42.493..145.151 rows=16,341 loops=1)

  • Recheck Cond: ((reported > ((date_trunc('month'::text, now()) - '1 mon'::interval) - '9 mons'::interval)) OR (reported > ((date_trunc('month'::text, now()) - '1 day'::interval) - '9 mons'::interval)))
  • Filter: (continue_follow_up <> 'yes'::text)
  • Rows Removed by Filter: 177212
  • Heap Blocks: exact=23665
46. 0.001 37.767 ↓ 0.0 0 1

BitmapOr (cost=5,359.87..5,359.87 rows=333,243 width=0) (actual time=37.767..37.767 rows=0 loops=1)

47. 20.598 20.598 ↓ 1.1 193,555 1

Bitmap Index Scan on useview_pregnancy_visit_reported_chw (cost=0.00..2,750.99 rows=173,008 width=0) (actual time=20.598..20.598 rows=193,555 loops=1)

  • Index Cond: (reported > ((date_trunc('month'::text, now()) - '1 mon'::interval) - '9 mons'::interval))
48. 17.168 17.168 ↓ 1.1 181,406 1

Bitmap Index Scan on useview_pregnancy_visit_reported_chw (cost=0.00..2,548.20 rows=160,236 width=0) (actual time=17.168..17.168 rows=181,406 loops=1)

  • Index Cond: (reported > ((date_trunc('month'::text, now()) - '1 day'::interval) - '9 mons'::interval))
49. 0.004 128.336 ↑ 169.6 12 1

Unique (cost=29,121.74..29,132.03 rows=2,035 width=44) (actual time=128.332..128.336 rows=12 loops=1)

50. 0.034 128.332 ↑ 171.5 12 1

Sort (cost=29,121.74..29,126.88 rows=2,058 width=44) (actual time=128.332..128.332 rows=12 loops=1)

  • Sort Key: useview_delivery_check.patient_id
  • Sort Method: quicksort Memory: 25kB
51. 128.298 128.298 ↑ 171.5 12 1

Seq Scan on useview_delivery_check (cost=0.00..29,008.47 rows=2,058 width=44) (actual time=53.031..128.298 rows=12 loops=1)

  • Filter: ((pregnancy_outcome = 'miscarriage'::text) AND ((reported > ((date_trunc('month'::text, now()) - '1 mon'::interval) - '9 mons'::interval)) OR (reported > ((date_trunc('month'::text, now()) - '1 day'::interval) - '9 mons'::interval))))
  • Rows Removed by Filter: 435262
52. 29.680 295.043 ↓ 1.3 152,698 1

Materialize (cost=0.42..19,704.89 rows=122,128 width=44) (actual time=0.593..295.043 rows=152,698 loops=1)

53. 96.600 265.363 ↓ 1.3 152,698 1

GroupAggregate (cost=0.42..18,178.29 rows=122,128 width=44) (actual time=0.590..265.363 rows=152,698 loops=1)

  • Group Key: useview_postnatal_care_1.patient_id
54. 168.763 168.763 ↑ 1.0 173,638 1

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 time=0.039..168.763 rows=173,638 loops=1)

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

CTE assess

56. 1,758.766 2,306.020 ↑ 1.4 3,726 1

GroupAggregate (cost=57,818.90..104,497.32 rows=5,168 width=279) (actual time=509.076..2,306.020 rows=3,726 loops=1)

  • Group Key: useview_assessment.chw, 0
57. 353.886 547.254 ↓ 1.0 155,269 1

Sort (cost=57,818.90..58,204.24 rows=154,138 width=127) (actual time=508.780..547.254 rows=155,269 loops=1)

  • Sort Key: useview_assessment.chw
  • Sort Method: quicksort Memory: 45495kB
58. 193.368 193.368 ↓ 1.0 155,269 1

Index Scan using useview_assessment_reported on useview_assessment (cost=0.46..44,536.93 rows=154,138 width=127) (actual time=0.073..193.368 rows=155,269 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 mon'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
59.          

CTE on_time_follow_ups

60. 4.217 339.193 ↑ 1.7 2,555 1

GroupAggregate (cost=64,890.93..64,977.29 rows=4,318 width=47) (actual time=333.157..339.193 rows=2,555 loops=1)

  • Group Key: assess_1.chw, 0
61. 24.889 334.976 ↓ 4.4 19,115 1

Sort (cost=64,890.93..64,901.73 rows=4,318 width=39) (actual time=333.151..334.976 rows=19,115 loops=1)

  • Sort Key: assess_1.chw
  • Sort Method: quicksort Memory: 2262kB
62. 16.973 310.087 ↓ 4.4 19,115 1

Nested Loop (cost=7,936.52..64,630.21 rows=4,318 width=39) (actual time=88.777..310.087 rows=19,115 loops=1)

63. 29.334 116.714 ↑ 1.1 22,050 1

Bitmap Heap Scan on useview_assessment assess_1 (cost=7,935.97..31,048.05 rows=24,128 width=80) (actual time=88.690..116.714 rows=22,050 loops=1)

  • Recheck Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 mon'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone) AND (patient_age_in_years < 5) AND (referral_follow_up = 'true'::text))
  • Heap Blocks: exact=8521
64. 2.168 87.380 ↓ 0.0 0 1

BitmapAnd (cost=7,935.97..7,935.97 rows=24,128 width=0) (actual time=87.380..87.380 rows=0 loops=1)

65. 37.165 37.165 ↓ 1.0 144,338 1

Bitmap Index Scan on useview_assessment_reported_age_uuid (cost=0.00..3,451.31 rows=142,288 width=0) (actual time=37.164..37.165 rows=144,338 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 mon'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone) AND (patient_age_in_years < 5))
66. 48.047 48.047 ↓ 1.0 439,624 1

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

  • Index Cond: (referral_follow_up = 'true'::text)
67. 176.400 176.400 ↑ 1.0 1 22,050

Index Only Scan using useview_assessment_follow_up_source_date_uuid on useview_assessment_follow_up follow_up (cost=0.55..1.38 rows=1 width=44) (actual time=0.008..0.008 rows=1 loops=22,050)

  • Index Cond: (form_source_id = assess_1.uuid)
  • Filter: ((date(date_trunc('day'::text, reported)) - date(date_trunc('day'::text, assess_1.reported))) <= 2)
  • Rows Removed by Filter: 0
  • Heap Fetches: 13091
68.          

CTE familyregtotal

69. 316.804 1,841.647 ↑ 44.6 6,148 1

HashAggregate (cost=110,383.02..113,127.37 rows=274,435 width=56) (actual time=1,838.793..1,841.647 rows=6,148 loops=1)

  • Group Key: cmeta_1.parent_uuid, 0
70. 447.168 1,524.843 ↑ 1.1 811,920 1

Hash Right Join (cost=74,356.70..101,694.75 rows=868,827 width=76) (actual time=910.075..1,524.843 rows=811,920 loops=1)

  • Hash Cond: (useview_family_survey.family_id = cmeta_1.uuid)
71. 383.443 552.264 ↓ 1.2 488,219 1

HashAggregate (cost=18,886.39..22,985.71 rows=409,932 width=36) (actual time=383.352..552.264 rows=488,219 loops=1)

  • Group Key: useview_family_survey.family_id
72. 168.821 168.821 ↓ 1.0 627,619 1

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 time=0.035..168.821 rows=627,619 loops=1)

  • Index Cond: (is_equity_survey = true)
  • Filter: is_equity_survey
  • Heap Fetches: 73348
73. 255.266 525.411 ↑ 1.1 811,920 1

Hash (cost=34,427.97..34,427.97 rows=868,827 width=72) (actual time=525.411..525.411 rows=811,920 loops=1)

  • Buckets: 524288 Batches: 2 Memory Usage: 45670kB
74. 270.145 270.145 ↑ 1.1 811,920 1

Index Scan using contactview_metadata_type on contactview_metadata cmeta_1 (cost=0.43..34,427.97 rows=868,827 width=72) (actual time=0.388..270.145 rows=811,920 loops=1)

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

CTE communityevent

76. 0.502 71.690 ↑ 6.2 472 1

HashAggregate (cost=12,630.41..12,659.63 rows=2,922 width=47) (actual time=71.572..71.690 rows=472 loops=1)

  • Group Key: meta.chw, 0
77. 3.124 71.188 ↑ 4.9 857 1

Bitmap Heap Scan on form_metadata meta (cost=8,276.70..12,599.03 rows=4,185 width=39) (actual time=68.246..71.188 rows=857 loops=1)

  • Recheck Cond: ((formname = 'community_event'::text) AND (reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 mon'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
  • Heap Blocks: exact=814
78. 6.069 68.064 ↓ 0.0 0 1

BitmapAnd (cost=8,276.70..8,276.70 rows=4,185 width=0) (actual time=68.064..68.064 rows=0 loops=1)

79. 7.608 7.608 ↑ 1.0 57,011 1

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

  • Index Cond: (formname = 'community_event'::text)
80. 54.387 54.387 ↑ 1.0 584,846 1

Bitmap Index Scan on form_metadata_reported (cost=0.00..7,578.76 rows=589,430 width=0) (actual time=54.387..54.387 rows=584,846 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 mon'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
81.          

CTE active_in_range

82. 64.549 946.490 ↑ 1.0 7,616 1

Seq Scan on contactview_chp chp_1 (cost=0.00..2,454,285.28 rows=7,774 width=44) (actual time=594.462..946.490 rows=7,616 loops=1)

83.          

SubPlan (for Seq Scan)

84. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.56..1,614.64 rows=9 width=0) (never executed)

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

Index Scan using form_metadata_chw on form_metadata meta_1 (cost=0.56..1,609.73 rows=108 width=14) (never executed)

  • Index Cond: (chw = chp_1.uuid)
  • Filter: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 mon'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
86. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..0.05 rows=3 width=32) (never executed)

87. 0.000 0.000 ↓ 0.0 0

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

88. 132.004 541.779 ↓ 3.4 164,832 1

Hash Semi Join (cost=0.54..27,724.00 rows=49,119 width=35) (actual time=0.025..541.779 rows=164,832 loops=1)

  • Hash Cond: (meta_2.formname = "*VALUES*_1".column1)
89. 409.770 409.770 ↑ 1.0 584,846 1

Index Scan using form_metadata_reported on form_metadata meta_2 (cost=0.46..25,630.22 rows=589,430 width=49) (actual time=0.015..409.770 rows=584,846 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 mon'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
90. 0.003 0.005 ↑ 1.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
91. 0.002 0.002 ↑ 1.0 3 1

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

92. 335.298 335.298 ↓ 0.0 0 3,854

Index Scan using contactview_metadata_parent_uuid on contactview_metadata clinic (cost=0.56..14.15 rows=1 width=0) (actual time=0.087..0.087 rows=0 loops=3,854)

  • Index Cond: (parent_uuid = chp_1.area_uuid)
  • Filter: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 mon'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 54
93. 0.000 0.000 ↓ 0.0 0

Seq Scan on contactview_metadata clinic_1 (cost=0.00..213,338.82 rows=49,585 width=36) (never executed)

  • Filter: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 mon'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
94. 0.000 0.000 ↓ 0.0 0

Index Scan using useview_postnatal_reported_chw_patient_id on useview_postnatal_care pnc (cost=0.57..121.61 rows=1 width=0) (never executed)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 mon'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone) AND (chw = chp_1.uuid))
  • Filter: (follow_up_count = '1'::text)
95. 4.864 4.864 ↓ 2.7 5,757 1

Index Scan using useview_postnatal_care_count_reported_uuid on useview_postnatal_care pnc_1 (cost=0.45..1,813.99 rows=2,112 width=35) (actual time=0.014..4.864 rows=5,757 loops=1)

  • Index Cond: ((follow_up_count = '1'::text) AND (reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 mon'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
96.          

CTE chp_visits

97. 2.882 161.353 ↓ 1.3 3,199 1

HashAggregate (cost=3,774.41..3,804.90 rows=2,439 width=40) (actual time=160.584..161.353 rows=3,199 loops=1)

  • Group Key: (couchdb.doc #>> '{fields,inputs,contact,_id}'::text[]), 0
98. 82.478 158.471 ↓ 1.7 4,124 1

Nested Loop (cost=0.62..3,762.21 rows=2,439 width=36) (actual time=0.345..158.471 rows=4,124 loops=1)

99. 0.002 0.023 ↑ 1.0 2 1

Unique (cost=0.04..0.04 rows=2 width=32) (actual time=0.018..0.023 rows=2 loops=1)

100. 0.018 0.021 ↑ 1.0 2 1

Sort (cost=0.04..0.04 rows=2 width=32) (actual time=0.018..0.021 rows=2 loops=1)

  • Sort Key: "*VALUES*_2".column1
  • Sort Method: quicksort Memory: 25kB
101. 0.003 0.003 ↑ 1.0 2 1

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

102. 75.970 75.970 ↓ 1.7 2,062 2

Index Scan using couchdb_doc_reported_date_form_contact on couchdb (cost=0.59..1,865.84 rows=1,220 width=455) (actual time=0.179..37.985 rows=2,062 loops=2)

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

CTE fp

104. 14.925 69.722 ↑ 1.2 1,589 1

GroupAggregate (cost=4,487.67..4,912.47 rows=1,941 width=47) (actual time=52.474..69.722 rows=1,589 loops=1)

  • Group Key: visit_1.chw, 0
105. 32.783 54.797 ↑ 1.0 22,575 1

Sort (cost=4,487.67..4,545.58 rows=23,165 width=81) (actual time=52.426..54.797 rows=22,575 loops=1)

  • Sort Key: visit_1.chw
  • Sort Method: quicksort Memory: 4009kB
106. 22.014 22.014 ↑ 1.0 22,575 1

Index Scan using useview_fp_visit_reported_xmlforms_uuid on useview_fp_visit visit_1 (cost=0.45..2,808.25 rows=23,165 width=81) (actual time=0.051..22.014 rows=22,575 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 mon'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
107.          

CTE sync

108. 692.365 1,113.900 ↓ 1.3 7,812 1

HashAggregate (cost=71,658.79..71,775.25 rows=5,823 width=48) (actual time=1,108.306..1,113.900 rows=7,812 loops=1)

  • Group Key: useview_chp_sync.chw_uuid, 0
109. 421.535 421.535 ↑ 1.0 2,152,674 1

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

110. 28.844 12,142.420 ↓ 784.0 5,488 1

Sort (cost=9,041.80..9,041.81 rows=7 width=624) (actual time=12,141.036..12,142.420 rows=5,488 loops=1)

  • Sort Key: chwlist.branch_uuid, chwlist.branch_name, chwlist.supervisor_uuid, chwlist.supervisor_name, chwlist.chw_uuid, chwlist.chw_name, chwlist.chw_phone, (date(chwlist.interval_start)), chwlist.interval_number
  • Sort Method: quicksort Memory: 3718kB
111. 4.311 12,113.576 ↓ 784.0 5,488 1

Hash Left Join (cost=1,457.87..9,041.70 rows=7 width=624) (actual time=12,086.467..12,113.576 rows=5,488 loops=1)

  • Hash Cond: ((chwlist.chw_uuid = sync.chw_uuid) AND (chwlist.interval_number = sync.interval_number))
112. 2.536 10,989.618 ↓ 784.0 5,488 1

Hash Left Join (cost=1,254.06..8,827.64 rows=7 width=620) (actual time=10,966.802..10,989.618 rows=5,488 loops=1)

  • Hash Cond: ((chwlist.chw_uuid = fp.chw_uuid) AND (chwlist.interval_number = fp.interval_number))
113. 2.921 10,916.190 ↓ 784.0 5,488 1

Hash Left Join (cost=1,186.12..8,756.27 rows=7 width=612) (actual time=10,895.888..10,916.190 rows=5,488 loops=1)

  • Hash Cond: ((chwlist.chw_area_uuid = chp_visits.chw_area_uuid) AND (chwlist.interval_number = chp_visits.interval_number))
114. 3.673 10,749.964 ↓ 784.0 5,488 1

Hash Left Join (cost=1,100.76..8,666.59 rows=7 width=640) (actual time=10,732.564..10,749.964 rows=5,488 loops=1)

  • Hash Cond: ((chwlist.chw_uuid = active_in_range.chw_uuid) AND (chwlist.interval_number = active_in_range.interval_number))
115. 2.285 9,793.274 ↓ 784.0 5,488 1

Hash Left Join (cost=828.67..8,380.86 rows=7 width=636) (actual time=9,779.526..9,793.274 rows=5,488 loops=1)

  • Hash Cond: ((chwlist.chw_uuid = communityevent.chw_uuid) AND (chwlist.interval_number = communityevent.interval_number))
116. 6.087 9,718.983 ↓ 784.0 5,488 1

Hash Right Join (cost=726.40..8,273.43 rows=7 width=628) (actual time=9,707.498..9,718.983 rows=5,488 loops=1)

  • Hash Cond: ((familyregtotal.chw_area_uuid = chwlist.chw_area_uuid) AND (familyregtotal.interval_number = chwlist.interval_number))
117. 1,844.217 1,844.217 ↑ 44.6 6,148 1

CTE Scan on familyregtotal (cost=0.00..5,488.70 rows=274,435 width=52) (actual time=1,838.795..1,844.217 rows=6,148 loops=1)

118. 5.746 7,868.679 ↓ 5,488.0 5,488 1

Hash (cost=726.38..726.38 rows=1 width=580) (actual time=7,868.679..7,868.679 rows=5,488 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2285kB
119. 918.344 7,862.933 ↓ 5,488.0 5,488 1

Nested Loop Left Join (cost=388.69..726.38 rows=1 width=580) (actual time=6,244.347..7,862.933 rows=5,488 loops=1)

  • Join Filter: ((chwlist.chw_uuid = eddnopnc.chw_uuid) AND (chwlist.interval_number = eddnopnc.interval_number))
  • Rows Removed by Join Filter: 8098817
120. 4.843 3,832.893 ↓ 5,488.0 5,488 1

Hash Right Join (cost=388.69..719.38 rows=1 width=572) (actual time=3,826.174..3,832.893 rows=5,488 loops=1)

  • Hash Cond: ((familyreg.chw_area_uuid = chwlist.chw_area_uuid) AND (familyreg.interval_number = chwlist.interval_number))
121. 999.469 999.469 ↑ 4.2 2,858 1

CTE Scan on familyreg (cost=0.00..240.50 rows=12,025 width=44) (actual time=997.573..999.469 rows=2,858 loops=1)

122. 6.103 2,828.581 ↓ 5,488.0 5,488 1

Hash (cost=388.67..388.67 rows=1 width=564) (actual time=2,828.581..2,828.581 rows=5,488 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2249kB
123. 6.316 2,822.478 ↓ 5,488.0 5,488 1

Hash Right Join (cost=246.54..388.67 rows=1 width=564) (actual time=1,012.792..2,822.478 rows=5,488 loops=1)

  • Hash Cond: ((assess.chw_uuid = chwlist.chw_uuid) AND (assess.interval_number = chwlist.interval_number))
124. 2,312.467 2,312.467 ↑ 1.4 3,726 1

CTE Scan on assess (cost=0.00..103.36 rows=5,168 width=252) (actual time=509.079..2,312.467 rows=3,726 loops=1)

125. 3.400 503.695 ↓ 5,488.0 5,488 1

Hash (cost=246.53..246.53 rows=1 width=348) (actual time=503.695..503.695 rows=5,488 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1619kB
126. 2.978 500.295 ↓ 5,488.0 5,488 1

Hash Right Join (cost=127.77..246.53 rows=1 width=348) (actual time=490.277..500.295 rows=5,488 loops=1)

  • Hash Cond: ((on_time_follow_ups.chw_uuid = chwlist.chw_uuid) AND (on_time_follow_ups.interval_number = chwlist.interval_number))
127. 340.220 340.220 ↑ 1.7 2,555 1

CTE Scan on on_time_follow_ups (cost=0.00..86.36 rows=4,318 width=44) (actual time=333.160..340.220 rows=2,555 loops=1)

128. 2.855 157.097 ↓ 5,488.0 5,488 1

Hash (cost=127.76..127.76 rows=1 width=340) (actual time=157.097..157.097 rows=5,488 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1594kB
129. 2.657 154.242 ↓ 5,488.0 5,488 1

Hash Right Join (cost=47.28..127.76 rows=1 width=340) (actual time=149.932..154.242 rows=5,488 loops=1)

  • Hash Cond: ((allpreg.chw_uuid = chwlist.chw_uuid) AND (allpreg.interval_number = chwlist.interval_number))
130. 12.824 12.824 ↓ 1.0 3,060 1

CTE Scan on allpreg (cost=0.00..58.52 rows=2,926 width=44) (actual time=11.155..12.824 rows=3,060 loops=1)

131. 2.819 138.761 ↓ 5,488.0 5,488 1

Hash (cost=47.27..47.27 rows=1 width=332) (actual time=138.761..138.761 rows=5,488 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1563kB
132. 2.709 135.942 ↓ 5,488.0 5,488 1

Hash Right Join (cost=0.04..47.27 rows=1 width=332) (actual time=131.182..135.942 rows=5,488 loops=1)

  • Hash Cond: ((pncvisit.chw_uuid = chwlist.chw_uuid) AND (pncvisit.interval_number = chwlist.interval_number))
133. 41.074 41.074 ↓ 1.5 2,618 1

CTE Scan on pncvisit (cost=0.00..34.34 rows=1,717 width=100) (actual time=38.996..41.074 rows=2,618 loops=1)

134. 3.140 92.159 ↓ 5,488.0 5,488 1

Hash (cost=0.03..0.03 rows=1 width=268) (actual time=92.159..92.159 rows=5,488 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1361kB
135. 89.019 89.019 ↓ 5,488.0 5,488 1

CTE Scan on chwlist (cost=0.00..0.03 rows=1 width=268) (actual time=0.132..89.019 rows=5,488 loops=1)

  • Filter: ((branch_name <> 'HQ'::text) AND (branch_name <> 'HQ OVC'::text))
  • Rows Removed by Filter: 109
136. 3,111.696 3,111.696 ↓ 7.4 1,476 5,488

CTE Scan on eddnopnc (cost=0.00..4.00 rows=200 width=44) (actual time=0.441..0.567 rows=1,476 loops=5,488)

137. 0.124 72.006 ↑ 6.2 472 1

Hash (cost=58.44..58.44 rows=2,922 width=44) (actual time=72.006..72.006 rows=472 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 71kB
138. 71.882 71.882 ↑ 6.2 472 1

CTE Scan on communityevent (cost=0.00..58.44 rows=2,922 width=44) (actual time=71.575..71.882 rows=472 loops=1)

139. 2.636 953.017 ↑ 1.0 7,616 1

Hash (cost=155.48..155.48 rows=7,774 width=40) (actual time=953.017..953.017 rows=7,616 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 654kB
140. 950.381 950.381 ↑ 1.0 7,616 1

CTE Scan on active_in_range (cost=0.00..155.48 rows=7,774 width=40) (actual time=594.466..950.381 rows=7,616 loops=1)

141. 0.782 163.305 ↓ 1.3 3,199 1

Hash (cost=48.78..48.78 rows=2,439 width=40) (actual time=163.305..163.305 rows=3,199 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 281kB
142. 162.523 162.523 ↓ 1.3 3,199 1

CTE Scan on chp_visits (cost=0.00..48.78 rows=2,439 width=40) (actual time=160.588..162.523 rows=3,199 loops=1)

143. 0.415 70.892 ↑ 1.2 1,589 1

Hash (cost=38.82..38.82 rows=1,941 width=44) (actual time=70.892..70.892 rows=1,589 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 145kB
144. 70.477 70.477 ↑ 1.2 1,589 1

CTE Scan on fp (cost=0.00..38.82 rows=1,941 width=44) (actual time=52.477..70.477 rows=1,589 loops=1)

145. 2.138 1,119.647 ↓ 1.3 7,812 1

Hash (cost=116.46..116.46 rows=5,823 width=44) (actual time=1,119.647..1,119.647 rows=7,812 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 702kB
146. 1,117.509 1,117.509 ↓ 1.3 7,812 1

CTE Scan on sync (cost=0.00..116.46 rows=5,823 width=44) (actual time=1,108.309..1,117.509 rows=7,812 loops=1)

Planning time : 31.253 ms
Execution time : 12,263.814 ms