explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3FMA

Settings
# exclusive inclusive rows x rows loops node
1. 169.944 2,008,305.245 ↓ 1,842.0 1,842 1

GroupAggregate (cost=1,557,120.32..1,557,122.28 rows=1 width=700) (actual time=2,008,132.704..2,008,305.245 rows=1,842 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. 37.445 2,008,135.301 ↓ 614.0 1,842 1

Sort (cost=1,557,120.32..1,557,120.33 rows=3 width=2,036) (actual time=2,008,132.421..2,008,135.301 rows=1,842 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: 1,722kB
3. 12.860 2,008,097.856 ↓ 614.0 1,842 1

Merge Left Join (cost=1,512,763.56..1,557,120.30 rows=3 width=2,036) (actual time=1,583,170.318..2,008,097.856 rows=1,842 loops=1)

  • Merge Cond: (chwlist.chw_uuid = "*SELECT* 1_2".reported_by)
  • Join Filter: (chwlist.interval_number = (0))
4. 16.163 2,007,638.612 ↓ 614.0 1,842 1

Merge Left Join (cost=1,510,119.23..1,553,520.11 rows=3 width=1,984) (actual time=1,582,733.416..2,007,638.612 rows=1,842 loops=1)

  • Merge Cond: (chwlist.chw_uuid = useview_home_visit.reported_by)
  • Join Filter: (chwlist.interval_number = (0))
5. 10.019 1,997,317.874 ↓ 614.0 1,842 1

Merge Left Join (cost=1,488,872.15..1,530,753.37 rows=3 width=1,976) (actual time=1,573,190.231..1,997,317.874 rows=1,842 loops=1)

  • Merge Cond: (chwlist.chw_uuid = "*SELECT* 1_1".chw_uuid)
  • Join Filter: (chwlist.interval_number = (0))
6. 11.547 1,990,935.050 ↓ 614.0 1,842 1

Merge Left Join (cost=1,473,483.26..1,515,364.37 rows=3 width=1,968) (actual time=1,566,817.420..1,990,935.050 rows=1,842 loops=1)

  • Merge Cond: (chwlist.chw_uuid = dr.chw)
  • Join Filter: (chwlist.interval_number = (0))
7. 19.575 1,989,548.865 ↓ 614.0 1,842 1

Merge Left Join (cost=1,472,262.17..1,514,137.65 rows=3 width=1,952) (actual time=1,565,443.324..1,989,548.865 rows=1,842 loops=1)

  • Merge Cond: (chwlist.chw_uuid = assess_u1fu_pnc_aggregated.chw)
  • Join Filter: (chwlist.interval_number = (0))
8. 21.184 1,977,259.082 ↓ 614.0 1,842 1

Merge Left Join (cost=1,440,691.28..1,482,546.22 rows=3 width=1,912) (actual time=1,553,681.670..1,977,259.082 rows=1,842 loops=1)

  • Merge Cond: (chwlist.chw_uuid = "*SELECT* 1".reported_by)
  • Join Filter: (chwlist.interval_number = (0))
9. 3,995.492 1,973,913.527 ↓ 614.0 1,842 1

Nested Loop Left Join (cost=1,435,395.99..1,477,122.47 rows=3 width=1,896) (actual time=1,550,405.203..1,973,913.527 rows=1,842 loops=1)

  • Join Filter: ((chwlist.chw_area_uuid = population_all_time.chw_area_uuid) AND (chwlist.interval_number = population_all_time.interval_number))
  • Rows Removed by Join Filter: 4,089,246
10. 23.811 1,867,932.021 ↓ 614.0 1,842 1

Merge Left Join (cost=1,167,365.89..1,208,927.22 rows=3 width=1,920) (actual time=1,451,733.232..1,867,932.021 rows=1,842 loops=1)

  • Merge Cond: (chwlist.chw_uuid = useview_chp_sync.chw_uuid)
  • Join Filter: (chwlist.interval_number = (0))
11. 3,978.353 1,774,738.559 ↓ 614.0 1,842 1

Nested Loop Left Join (cost=1,167,365.34..1,174,515.49 rows=3 width=1,912) (actual time=1,451,422.955..1,774,738.559 rows=1,842 loops=1)

  • Join Filter: ((chwlist.chw_area_uuid = contactview_metadata_1.parent_uuid) AND (chwlist.interval_number = (0)))
  • Rows Removed by Join Filter: 4,120,560
12. 20.567 1,755,631.860 ↓ 1,842.0 1,842 1

Merge Left Join (cost=1,154,578.62..1,157,176.88 rows=1 width=1,868) (actual time=1,441,370.003..1,755,631.860 rows=1,842 loops=1)

  • Merge Cond: (chwlist.chw_uuid = survey.chw)
  • Join Filter: (chwlist.interval_number = (0))
13. 2,359.859 1,752,570.615 ↓ 1,842.0 1,842 1

Nested Loop Left Join (cost=1,139,395.22..1,141,485.84 rows=1 width=1,844) (actual time=1,438,542.610..1,752,570.615 rows=1,842 loops=1)

  • Join Filter: ((chwlist.chw_area_uuid = demographics.chw_area_uuid) AND (chwlist.interval_number = demographics.interval_number))
  • Rows Removed by Join Filter: 2,606,889
14. 2,946.307 1,235,027.302 ↓ 1,842.0 1,842 1

Nested Loop Left Join (cost=984,217.08..986,209.74 rows=1 width=1,732) (actual time=925,737.405..1,235,027.302 rows=1,842 loops=1)

  • Join Filter: ((chwlist.chw_uuid = household_cte.chw_uuid) AND (chwlist.interval_number = household_cte.interval_number))
  • Rows Removed by Join Filter: 3,133,427
15. 28.447 1,228,047.015 ↓ 1,842.0 1,842 1

Nested Loop Left Join (cost=962,539.57..964,435.39 rows=1 width=1,724) (actual time=924,742.601..1,228,047.015 rows=1,842 loops=1)

  • Join Filter: (chwlist.interval_number = 0)
16. 1,365.264 458,510.174 ↓ 1,842.0 1,842 1

Nested Loop Left Join (cost=962,539.29..963,207.64 rows=1 width=1,720) (actual time=440,127.242..458,510.174 rows=1,842 loops=1)

  • Join Filter: ((chwlist.chw_uuid = meta.chw) AND (chwlist.interval_number = (0)))
  • Rows Removed by Join Filter: 1,551,980
17. 22.386 448,609.082 ↓ 1,842.0 1,842 1

Merge Left Join (cost=954,681.28..955,285.86 rows=1 width=1,712) (actual time=433,250.504..448,609.082 rows=1,842 loops=1)

  • Merge Cond: (chwlist.chw_uuid = assess.chw)
  • Join Filter: (chwlist.interval_number = (0))
18. 3,075.335 430,495.792 ↓ 1,842.0 1,842 1

Nested Loop Left Join (cost=938,469.06..939,026.51 rows=1 width=1,704) (actual time=415,187.906..430,495.792 rows=1,842 loops=1)

  • Join Filter: ((chwlist.chw_uuid = assessments.chw_uuid) AND (chwlist.interval_number = assessments.interval_number))
  • Rows Removed by Join Filter: 3,109,487
19. 242.719 421,065.557 ↓ 1,842.0 1,842 1

Nested Loop Left Join (cost=921,039.21..921,500.40 rows=1 width=452) (actual time=411,769.032..421,065.557 rows=1,842 loops=1)

  • Join Filter: ((chwlist.chw_uuid = p.chw) AND (chwlist.interval_number = (0)))
  • Rows Removed by Join Filter: 265,123
20. 1,937.200 414,501.094 ↓ 1,842.0 1,842 1

Nested Loop Left Join (cost=829,936.81..830,391.01 rows=1 width=444) (actual time=405,728.665..414,501.094 rows=1,842 loops=1)

  • Join Filter: ((chwlist.chw_uuid = pnc.chw) AND (chwlist.interval_number = (0)))
  • Rows Removed by Join Filter: 1,754,495
21. 1,766.965 409,633.272 ↓ 1,842.0 1,842 1

Nested Loop Left Join (cost=828,363.06..828,768.36 rows=1 width=348) (actual time=405,691.414..409,633.272 rows=1,842 loops=1)

  • Join Filter: ((chwlist.chw_uuid = useview_pregnancy.chw) AND (chwlist.interval_number = (0)))
  • Rows Removed by Join Filter: 1,888,888
22. 14.040 405,497.495 ↓ 1,842.0 1,842 1

Merge Left Join (cost=828,082.80..828,444.18 rows=1 width=332) (actual time=405,464.046..405,497.495 rows=1,842 loops=1)

  • Merge Cond: ((chwlist.chw_uuid = anc_pnc_u1fu.chw_uuid) AND (chwlist.interval_number = anc_pnc_u1fu.interval_number))
23. 11.652 2,555.393 ↓ 1,842.0 1,842 1

Sort (cost=12,210.28..12,210.28 rows=1 width=284) (actual time=2,551.257..2,555.393 rows=1,842 loops=1)

  • Sort Key: chwlist.chw_uuid, chwlist.interval_number
  • Sort Method: quicksort Memory: 708kB
24. 2.747 2,543.741 ↓ 1,842.0 1,842 1

Hash Right Join (cost=12,146.59..12,210.27 rows=1 width=284) (actual time=2,540.395..2,543.741 rows=1,842 loops=1)

  • Hash Cond: ((contactview_metadata.parent_uuid = chwlist.chw_area_uuid) AND ((0) = chwlist.interval_number))
25. 3.577 1,419.695 ↑ 3.2 714 1

HashAggregate (cost=5,044.67..5,067.82 rows=2,315 width=48) (actual time=1,419.065..1,419.695 rows=714 loops=1)

  • Group Key: contactview_metadata.parent_uuid, 0
26. 936.484 1,416.118 ↑ 1.3 1,804 1

Bitmap Heap Scan on contactview_metadata (cost=2,695.52..5,027.19 rows=2,330 width=76) (actual time=483.907..1,416.118 rows=1,804 loops=1)

  • Recheck Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone) AND (type = 'clinic'::text))
  • Heap Blocks: exact=308
27. 0.103 479.634 ↓ 0.0 0 1

BitmapAnd (cost=2,695.52..2,695.52 rows=2,330 width=0) (actual time=479.634..479.634 rows=0 loops=1)

28. 167.796 167.796 ↑ 1.1 11,167 1

Bitmap Index Scan on contactview_metadata_reported (cost=0.00..154.71 rows=11,825 width=0) (actual time=167.796..167.796 rows=11,167 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
29. 311.735 311.735 ↓ 1.0 238,439 1

Bitmap Index Scan on contactview_metadata_type (cost=0.00..2,539.40 rows=238,396 width=0) (actual time=311.735..311.735 rows=238,439 loops=1)

  • Index Cond: (type = 'clinic'::text)
30. 3.588 1,121.299 ↓ 1,842.0 1,842 1

Hash (cost=7,101.91..7,101.91 rows=1 width=276) (actual time=1,121.299..1,121.299 rows=1,842 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 493kB
31. 6.200 1,117.711 ↓ 1,842.0 1,842 1

Hash Right Join (cost=7,046.68..7,101.91 rows=1 width=276) (actual time=1,110.067..1,117.711 rows=1,842 loops=1)

  • Hash Cond: ((anc.chw = chwlist.chw_uuid) AND ((0) = chwlist.interval_number))
32. 11.602 957.963 ↑ 1.5 1,383 1

HashAggregate (cost=6,925.22..6,945.30 rows=2,008 width=48) (actual time=956.497..957.963 rows=1,383 loops=1)

  • Group Key: anc.chw, 0
33. 946.361 946.361 ↑ 1.1 5,421 1

Index Scan using useview_pregnancy_visit_pregnancy_reported_uuid on useview_pregnancy_visit anc (cost=0.45..6,881.45 rows=5,836 width=40) (actual time=41.182..946.361 rows=5,421 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
34. 3.223 153.548 ↓ 1,842.0 1,842 1

Hash (cost=121.44..121.44 rows=1 width=268) (actual time=153.548..153.548 rows=1,842 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 469kB
35. 2.700 150.325 ↓ 1,842.0 1,842 1

Subquery Scan on chwlist (cost=119.38..121.44 rows=1 width=268) (actual time=91.175..150.325 rows=1,842 loops=1)

36. 8.018 147.625 ↓ 1,842.0 1,842 1

Nested Loop Anti Join (cost=119.38..121.43 rows=1 width=268) (actual time=91.174..147.625 rows=1,842 loops=1)

37.          

CTE chw_hierarchy

38. 9.513 87.948 ↓ 2,452.0 2,452 1

Group (cost=119.07..119.11 rows=1 width=221) (actual time=74.434..87.948 rows=2,452 loops=1)

  • Group Key: cmd_2.uuid, cmd_2.name, chp_1.supervisor_uuid, chp_1.area_uuid, cmeta.name, chp_1.uuid, chp_1.name, chp_1.phone
39. 26.632 78.435 ↓ 2,452.0 2,452 1

Sort (cost=119.07..119.08 rows=1 width=209) (actual time=74.419..78.435 rows=2,452 loops=1)

  • Sort Key: cmd_2.uuid, cmd_2.name, chp_1.supervisor_uuid, chp_1.area_uuid, cmeta.name, chp_1.uuid, chp_1.name, chp_1.phone
  • Sort Method: quicksort Memory: 748kB
40. 7.230 51.803 ↓ 2,452.0 2,452 1

Nested Loop (cost=3.61..119.06 rows=1 width=209) (actual time=0.331..51.803 rows=2,452 loops=1)

41. 5.391 7.793 ↓ 2,452.0 2,452 1

Hash Join (cost=3.18..116.79 rows=1 width=192) (actual time=0.277..7.793 rows=2,452 loops=1)

  • Hash Cond: (chp_1.branch_uuid = cmd_2.uuid)
42. 2.163 2.163 ↑ 1.0 2,452 1

Seq Scan on contactview_chp chp_1 (cost=0.00..104.16 rows=2,516 width=172) (actual time=0.017..2.163 rows=2,452 loops=1)

43. 0.021 0.239 ↓ 1.8 14 1

Hash (cost=3.08..3.08 rows=8 width=53) (actual time=0.239..0.239 rows=14 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
44. 0.218 0.218 ↓ 1.8 14 1

Index Scan using contactview_metadata_type on contactview_metadata cmd_2 (cost=0.43..3.08 rows=8 width=53) (actual time=0.087..0.218 rows=14 loops=1)

  • Index Cond: (type = 'district_hospital'::text)
45. 36.780 36.780 ↑ 1.0 1 2,452

Index Scan using contactview_metadata_uuid on contactview_metadata cmeta (cost=0.43..2.27 rows=1 width=53) (actual time=0.014..0.015 rows=1 loops=2,452)

  • Index Cond: (uuid = chp_1.supervisor_uuid)
46. 95.033 95.033 ↓ 2,346.0 2,346 1

CTE Scan on chw_hierarchy (cost=0.00..0.03 rows=1 width=268) (actual time=74.442..95.033 rows=2,346 loops=1)

  • Filter: ((branch_name <> 'HQ'::text) AND (branch_name <> 'HQ OVC'::text))
  • Rows Removed by Filter: 106
47. 44.574 44.574 ↓ 0.0 0 2,346

Index Only Scan using chp_muting_chw on chp_muting (cost=0.28..2.29 rows=1 width=37) (actual time=0.019..0.019 rows=0 loops=2,346)

  • Index Cond: (chw = chw_hierarchy.chw_uuid)
  • Heap Fetches: 159
48. 2.253 402,928.062 ↑ 1.4 727 1

Materialize (cost=815,872.53..816,228.79 rows=1,018 width=84) (actual time=402,912.782..402,928.062 rows=727 loops=1)

49. 11.298 402,925.809 ↑ 1.4 727 1

GroupAggregate (cost=815,872.53..816,216.07 rows=1,018 width=84) (actual time=402,912.777..402,925.809 rows=727 loops=1)

  • Group Key: anc_pnc_u1fu.chw_uuid, anc_pnc_u1fu.interval_number
50.          

CTE u1s

51. 4.261 324,455.345 ↑ 1.2 2,723 1

Group (cost=151,924.61..151,965.66 rows=3,284 width=81) (actual time=324,448.807..324,455.345 rows=2,723 loops=1)

  • Group Key: useview_population_demographics.family_uuid, useview_population_demographics.uuid, useview_population_demographics.date_of_birth, 0
52. 23.138 324,451.084 ↑ 1.2 2,723 1

Sort (cost=151,924.61..151,932.82 rows=3,284 width=81) (actual time=324,448.803..324,451.084 rows=2,723 loops=1)

  • Sort Key: useview_population_demographics.family_uuid, useview_population_demographics.uuid, useview_population_demographics.date_of_birth
  • Sort Method: quicksort Memory: 479kB
53. 324,427.946 324,427.946 ↑ 1.2 2,723 1

Index Scan using useview_population_demographics_chp_area_reported on useview_population_demographics (cost=0.58..151,732.80 rows=3,284 width=81) (actual time=180.419..324,427.946 rows=2,723 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
  • Filter: ((date_part('years'::text, age(now(), (date_of_birth)::timestamp with time zone)))::integer <= 1)
  • Rows Removed by Filter: 6,541
54.          

CTE family_persons

55. 30.889 64,220.339 ↑ 1.2 2,419 1

GroupAggregate (cost=280,516.19..280,576.37 rows=3,009 width=69) (actual time=64,185.285..64,220.339 rows=2,419 loops=1)

  • Group Key: pd.family_uuid
56. 12.232 64,189.450 ↓ 1.4 4,093 1

Sort (cost=280,516.19..280,523.71 rows=3,009 width=73) (actual time=64,185.252..64,189.450 rows=4,093 loops=1)

  • Sort Key: pd.family_uuid
  • Sort Method: quicksort Memory: 672kB
57. 26.187 64,177.218 ↓ 1.4 4,093 1

Hash Join (cost=280,024.90..280,342.34 rows=3,009 width=73) (actual time=64,148.590..64,177.218 rows=4,093 loops=1)

  • Hash Cond: (u1s.family_uuid = pd.family_uuid)
58. 4.056 4.056 ↑ 1.2 2,723 1

CTE Scan on u1s (cost=0.00..65.68 rows=3,284 width=32) (actual time=0.001..4.056 rows=2,723 loops=1)

59. 1,137.074 64,146.975 ↓ 1.7 310,545 1

Hash (cost=277,794.16..277,794.16 rows=178,459 width=73) (actual time=64,146.975..64,146.975 rows=310,545 loops=1)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 36,243kB
60. 63,009.901 63,009.901 ↓ 1.7 310,545 1

Seq Scan on useview_population_demographics pd (cost=0.00..277,794.16 rows=178,459 width=73) (actual time=20,964.990..63,009.901 rows=310,545 loops=1)

  • Filter: ((sex = 'female'::text) AND ((date_part('years'::text, age(now(), (date_of_birth)::timestamp with time zone)))::integer >= 15))
  • Rows Removed by Filter: 660,020
61.          

CTE pnc_mother_baby

62. 16.806 391,006.170 ↑ 90.4 1,126 1

HashAggregate (cost=9,992.87..11,010.75 rows=101,788 width=176) (actual time=391,004.879..391,006.170 rows=1,126 loops=1)

  • Group Key: pnc_1.chw, u1s_1.family_uuid, fp.person_ids, pnc_1.patient_id, u1s_1.uuid, u1s_1.interval_number
63. 15.054 390,989.364 ↑ 31.9 3,194 1

Merge Join (cost=6,922.81..8,466.05 rows=101,788 width=172) (actual time=390,966.741..390,989.364 rows=3,194 loops=1)

  • Merge Cond: (u1s_1.family_uuid = fp.family_uuid)
64. 8.250 324,468.301 ↑ 1.2 2,721 1

Sort (cost=257.49..265.70 rows=3,284 width=68) (actual time=324,463.916..324,468.301 rows=2,721 loops=1)

  • Sort Key: u1s_1.family_uuid
  • Sort Method: quicksort Memory: 479kB
65. 324,460.051 324,460.051 ↑ 1.2 2,723 1

CTE Scan on u1s u1s_1 (cost=0.00..65.68 rows=3,284 width=68) (actual time=324,448.812..324,460.051 rows=2,723 loops=1)

66. 9.356 66,506.009 ↑ 1.9 3,194 1

Sort (cost=6,665.32..6,680.82 rows=6,199 width=136) (actual time=66,502.818..66,506.009 rows=3,194 loops=1)

  • Sort Key: fp.family_uuid
  • Sort Method: quicksort Memory: 868kB
67. 13.121 66,496.653 ↑ 2.1 2,906 1

Nested Loop (cost=0.42..6,274.85 rows=6,199 width=136) (actual time=64,190.479..66,496.653 rows=2,906 loops=1)

68. 64,226.605 64,226.605 ↑ 1.2 2,419 1

CTE Scan on family_persons fp (cost=0.00..60.18 rows=3,009 width=64) (actual time=64,185.288..64,226.605 rows=2,419 loops=1)

69. 2,256.927 2,256.927 ↑ 2.0 1 2,419

Index Scan using useview_postnatal_patient_id on useview_postnatal_care pnc_1 (cost=0.42..2.05 rows=2 width=72) (actual time=0.550..0.933 rows=1 loops=2,419)

  • Index Cond: (patient_id = fp.person_ids)
70.          

CTE anc_pnc_u1fu

71. 28.719 402,908.196 ↑ 9.0 1,126 1

GroupAggregate (cost=341,890.08..371,438.59 rows=10,179 width=96) (actual time=402,872.718..402,908.196 rows=1,126 loops=1)

  • Group Key: pmb.chw_uuid, pmb.baby_uuid, pmb.interval_number
72. 39.262 402,879.477 ↑ 161.1 10,448 1

Sort (cost=341,890.08..346,096.75 rows=1,682,670 width=179) (actual time=402,872.677..402,879.477 rows=10,448 loops=1)

  • Sort Key: pmb.chw_uuid, pmb.baby_uuid, pmb.interval_number
  • Sort Method: quicksort Memory: 3,160kB
73. 16.012 402,840.215 ↑ 161.1 10,448 1

Hash Left Join (cost=32,692.11..82,432.47 rows=1,682,670 width=179) (actual time=394,459.771..402,840.215 rows=10,448 loops=1)

  • Hash Cond: (pmb.baby_uuid = u1fu.patient_id)
74. 184.954 402,547.011 ↑ 70.5 10,441 1

Merge Right Join (cost=31,535.69..57,087.98 rows=736,137 width=142) (actual time=394,182.512..402,547.011 rows=10,441 loops=1)

  • Merge Cond: (pc_1.patient_id = pmb.mother_uuid)
75. 8,168.035 8,168.035 ↑ 1.0 127,761 1

Index Scan using useview_postnatal_patient_id on useview_postnatal_care pc_1 (cost=0.42..8,320.57 rows=128,610 width=73) (actual time=15.700..8,168.035 rows=127,761 loops=1)

76. 9.609 394,194.022 ↑ 34.2 10,437 1

Materialize (cost=31,535.27..38,297.10 rows=357,308 width=137) (actual time=393,894.909..394,194.022 rows=10,437 loops=1)

77. 144.456 394,184.413 ↑ 101.9 3,505 1

Merge Left Join (cost=31,535.27..37,403.83 rows=357,308 width=137) (actual time=393,894.898..394,184.413 rows=3,505 loops=1)

  • Merge Cond: (pmb.mother_uuid = pv_1.patient_id)
78. 19.353 391,042.215 ↑ 90.4 1,126 1

Sort (cost=10,502.08..10,756.55 rows=101,788 width=100) (actual time=391,041.136..391,042.215 rows=1,126 loops=1)

  • Sort Key: pmb.mother_uuid
  • Sort Method: quicksort Memory: 348kB
79. 391,022.862 391,022.862 ↑ 90.4 1,126 1

CTE Scan on pnc_mother_baby pmb (cost=0.00..2,035.76 rows=101,788 width=100) (actual time=391,004.884..391,022.862 rows=1,126 loops=1)

80. 1,324.289 2,997.742 ↑ 1.0 170,928 1

Sort (cost=21,033.19..21,461.70 rows=171,405 width=73) (actual time=2,853.484..2,997.742 rows=170,928 loops=1)

  • Sort Key: pv_1.patient_id
  • Sort Method: quicksort Memory: 30,163kB
81. 1,673.453 1,673.453 ↑ 1.0 170,836 1

Seq Scan on useview_pregnancy_visit pv_1 (cost=0.00..6,132.05 rows=171,405 width=73) (actual time=2.027..1,673.453 rows=170,836 loops=1)

82. 35.337 277.192 ↑ 1.0 16,807 1

Hash (cost=946.19..946.19 rows=16,819 width=74) (actual time=277.192..277.192 rows=16,807 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,996kB
83. 241.855 241.855 ↑ 1.0 16,807 1

Seq Scan on useview_u1_follow_up u1fu (cost=0.00..946.19 rows=16,819 width=74) (actual time=0.603..241.855 rows=16,807 loops=1)

84. 3.159 402,914.511 ↑ 9.0 1,126 1

Sort (cost=881.16..906.61 rows=10,179 width=60) (actual time=402,912.747..402,914.511 rows=1,126 loops=1)

  • Sort Key: anc_pnc_u1fu.chw_uuid, anc_pnc_u1fu.interval_number
  • Sort Method: quicksort Memory: 207kB
85. 402,911.352 402,911.352 ↑ 9.0 1,126 1

CTE Scan on anc_pnc_u1fu (cost=0.00..203.58 rows=10,179 width=60) (actual time=402,872.723..402,911.352 rows=1,126 loops=1)

86. 2,146.590 2,368.812 ↑ 1.2 1,026 1,842

HashAggregate (cost=280.26..292.81 rows=1,255 width=56) (actual time=0.125..1.286 rows=1,026 loops=1,842)

  • Group Key: useview_pregnancy.chw, 0
87. 222.222 222.222 ↑ 1.1 1,752 1

Index Scan using useview_pregnancy_reported on useview_pregnancy (cost=0.32..243.46 rows=1,840 width=78) (actual time=8.664..222.222 rows=1,752 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
88. 2,910.582 2,930.622 ↑ 1.5 953 1,842

HashAggregate (cost=1,573.75..1,587.72 rows=1,397 width=136) (actual time=0.023..1.591 rows=953 loops=1,842)

  • Group Key: pnc.chw, 0
89. 20.040 20.040 ↑ 1.5 1,555 1

Index Scan using useview_postnatal_care_count_reported_uuid on useview_postnatal_care pnc (cost=0.45..1,307.61 rows=2,265 width=102) (actual time=13.049..20.040 rows=1,555 loops=1)

  • Index Cond: ((follow_up_count = '1'::text) AND (reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
90. 281.985 6,321.744 ↑ 1.4 144 1,842

HashAggregate (cost=91,102.40..91,104.40 rows=200 width=48) (actual time=3.283..3.432 rows=144 loops=1,842)

  • Group Key: p.chw, 0
91. 53.154 6,039.759 ↑ 4,273.9 157 1

Merge Right Join (cost=26,508.87..86,069.86 rows=671,005 width=76) (actual time=5,651.874..6,039.759 rows=157 loops=1)

  • Merge Cond: (useview_postnatal_care.patient_id = p.patient_id)
  • Filter: (((max(useview_postnatal_care.reported)) IS NULL) OR ((@ ((CASE WHEN (max(useview_pregnancy_visit.new_edd) <> ''::text) THEN (max(useview_pregnancy_visit.new_edd))::date ELSE max(p.edd) END) - ((max(useview_postnatal_care.reported)))::date)) > 60))
  • Rows Removed by Filter: 665
92. 93.606 334.675 ↓ 1.1 41,224 1

GroupAggregate (cost=0.42..9,258.52 rows=38,065 width=44) (actual time=0.416..334.675 rows=41,224 loops=1)

  • Group Key: useview_postnatal_care.patient_id
93. 241.069 241.069 ↑ 1.0 46,332 1

Index Scan using useview_postnatal_patient_id on useview_postnatal_care (cost=0.42..8,642.09 rows=47,156 width=44) (actual time=0.021..241.069 rows=46,332 loops=1)

  • Filter: (follow_up_count = '1'::text)
  • Rows Removed by Filter: 81,424
94. 4.658 5,651.930 ↑ 12.7 822 1

Sort (cost=26,508.45..26,534.63 rows=10,472 width=76) (actual time=5,650.947..5,651.930 rows=822 loops=1)

  • Sort Key: p.patient_id
  • Sort Method: quicksort Memory: 140kB
95. 95.313 5,647.272 ↑ 12.7 822 1

HashAggregate (cost=25,521.24..25,704.50 rows=10,472 width=76) (actual time=5,592.446..5,647.272 rows=822 loops=1)

  • Group Key: p.chw, p.patient_id
  • Filter: ((max(p.edd) >= date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval))) AND CASE WHEN (date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) = date_trunc('day'::text, now())) THEN (max(p.edd) <= (((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval) - '3 days'::interval)) ELSE (max(p.edd) <= (((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval) + '1 day'::interval)) END)
  • Rows Removed by Filter: 13,863
96. 52.119 5,551.959 ↓ 1.5 15,808 1

Merge Anti Join (cost=20,083.80..25,311.80 rows=10,472 width=78) (actual time=1,031.619..5,551.959 rows=15,808 loops=1)

  • Merge Cond: (p.patient_id = useview_pregnancy_visit_1.patient_id)
97. 153.887 5,244.599 ↓ 1.8 24,032 1

Merge Join (cost=15,615.00..20,529.14 rows=13,418 width=78) (actual time=804.442..5,244.599 rows=24,032 loops=1)

  • Merge Cond: (useview_pregnancy_visit.patient_id = p.patient_id)
  • Join Filter: (p.reported < useview_pregnancy_visit.reported)
  • Rows Removed by Join Filter: 97
98. 57.173 900.998 ↑ 1.7 22,306 1

Unique (cost=15,614.59..15,915.00 rows=38,096 width=47) (actual time=785.890..900.998 rows=22,306 loops=1)

99. 496.402 843.825 ↓ 1.1 64,172 1

Sort (cost=15,614.59..15,764.79 rows=60,082 width=47) (actual time=785.881..843.825 rows=64,172 loops=1)

  • Sort Key: useview_pregnancy_visit.patient_id, useview_pregnancy_visit.reported DESC
  • Sort Method: quicksort Memory: 10,558kB
100. 347.423 347.423 ↓ 1.1 64,172 1

Seq Scan on useview_pregnancy_visit (cost=0.00..10,845.69 rows=60,082 width=47) (actual time=0.025..347.423 rows=64,172 loops=1)

  • Filter: (((edd_updated IS TRUE) AND (reported > ((date_trunc('month'::text, now()) - '3 mons'::interval) - '9 mons'::interval))) OR (reported > (((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval) - '9 mons'::interval)))
  • Rows Removed by Filter: 106,664
101. 4,189.714 4,189.714 ↑ 1.0 58,810 1

Index Scan using useview_pregnancy_patient_id on useview_pregnancy p (cost=0.41..3,487.73 rows=58,821 width=84) (actual time=16.185..4,189.714 rows=58,810 loops=1)

102. 11.135 255.241 ↑ 1.5 8,209 1

Materialize (cost=4,468.80..4,689.10 rows=12,222 width=36) (actual time=222.907..255.241 rows=8,209 loops=1)

103. 12.630 244.106 ↑ 1.5 8,209 1

Unique (cost=4,468.80..4,536.32 rows=12,222 width=44) (actual time=222.901..244.106 rows=8,209 loops=1)

104. 71.767 231.476 ↑ 1.3 10,478 1

Sort (cost=4,468.80..4,502.56 rows=13,505 width=44) (actual time=222.899..231.476 rows=10,478 loops=1)

  • Sort Key: useview_pregnancy_visit_1.patient_id, useview_pregnancy_visit_1.reported DESC
  • Sort Method: quicksort Memory: 1,203kB
105. 159.709 159.709 ↑ 1.3 10,478 1

Index Scan using useview_pregnancy_visit_continue_follow_up on useview_pregnancy_visit useview_pregnancy_visit_1 (cost=0.42..3,542.27 rows=13,505 width=44) (actual time=13.398..159.709 rows=10,478 loops=1)

  • Index Cond: (continue_follow_up = false)
  • Filter: ((continue_follow_up IS FALSE) AND ((reported > ((date_trunc('month'::text, now()) - '3 mons'::interval) - '9 mons'::interval)) OR (reported > (((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval) - '9 mons'::interval))))
  • Rows Removed by Filter: 13,832
106. 6,354.900 6,354.900 ↑ 1.3 1,689 1,842

CTE Scan on assessments (cost=17,429.85..17,472.63 rows=2,139 width=1,288) (actual time=1.842..3.450 rows=1,689 loops=1,842)

107.          

CTE assessments

108. 216.570 3,403.762 ↑ 1.3 1,689 1

HashAggregate (cost=17,189.21..17,429.85 rows=2,139 width=1,292) (actual time=3,388.570..3,403.762 rows=1,689 loops=1)

  • Group Key: chwview_assessment.chw_uuid, 0
109. 3,187.192 3,187.192 ↑ 1.0 40,002 1

Index Scan using chwview_assessment_reported_chw_uuid on chwview_assessment (cost=0.46..12,891.56 rows=40,930 width=352) (actual time=15.182..3,187.192 rows=40,002 loops=1)

  • Index Cond: ((reported_day >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported_day < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
110. 3.071 18,090.904 ↑ 1.1 1,209 1

Materialize (cost=16,212.22..16,255.90 rows=1,344 width=48) (actual time=18,062.591..18,090.904 rows=1,209 loops=1)

111. 17.932 18,087.833 ↑ 1.1 1,209 1

GroupAggregate (cost=16,212.22..16,239.10 rows=1,344 width=48) (actual time=18,062.582..18,087.833 rows=1,209 loops=1)

  • Group Key: assess.chw, 0
112. 34.490 18,069.901 ↓ 4.4 5,952 1

Sort (cost=16,212.22..16,215.58 rows=1,344 width=77) (actual time=18,062.565..18,069.901 rows=5,952 loops=1)

  • Sort Key: assess.chw
  • Sort Method: quicksort Memory: 1,030kB
113. 38.429 18,035.411 ↓ 4.4 5,952 1

Nested Loop (cost=2,627.26..16,142.39 rows=1,344 width=77) (actual time=338.336..18,035.411 rows=5,952 loops=1)

114. 1,491.970 1,787.837 ↓ 1.3 7,585 1

Bitmap Heap Scan on useview_assessment assess (cost=2,626.71..8,271.51 rows=5,696 width=81) (actual time=302.117..1,787.837 rows=7,585 loops=1)

  • Recheck Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '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=2,284
115. 1.291 295.867 ↓ 0.0 0 1

BitmapAnd (cost=2,626.71..2,626.71 rows=5,696 width=0) (actual time=295.867..295.867 rows=0 loops=1)

116. 80.579 80.579 ↑ 1.0 33,751 1

Bitmap Index Scan on useview_assessment_reported_age_uuid (cost=0.00..953.95 rows=34,204 width=0) (actual time=80.579..80.579 rows=33,751 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone) AND (patient_age_in_years < 5))
117. 213.997 213.997 ↓ 1.0 166,844 1

Bitmap Index Scan on useview_assessment_referral_follow_up (cost=0.00..1,669.65 rows=162,564 width=0) (actual time=213.997..213.997 rows=166,844 loops=1)

  • Index Cond: (referral_follow_up = 'true'::text)
118. 16,209.145 16,209.145 ↑ 1.0 1 7,585

Index Only Scan using useview_assessment_follow_up_source_date_uuid on useview_assessment_follow_up follow_up (cost=0.55..1.37 rows=1 width=45) (actual time=2.118..2.137 rows=1 loops=7,585)

  • Index Cond: (form_source_id = assess.uuid)
  • Filter: ((date(date_trunc('day'::text, reported)) - date(date_trunc('day'::text, assess.reported))) <= 2)
  • Rows Removed by Filter: 0
  • Heap Fetches: 112
119. 1,667.994 8,535.828 ↑ 2.2 843 1,842

HashAggregate (cost=7,858.01..7,876.23 rows=1,822 width=48) (actual time=3.735..4.634 rows=843 loops=1,842)

  • Group Key: meta.chw, 0
120. 4,880.602 6,867.834 ↑ 1.7 2,094 1

Bitmap Heap Scan on form_metadata meta (cost=4,116.64..7,830.82 rows=3,625 width=76) (actual time=1,989.166..6,867.834 rows=2,094 loops=1)

  • Recheck Cond: ((formname = 'community_event'::text) AND (reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
  • Heap Blocks: exact=1,704
121. 5.940 1,987.232 ↓ 0.0 0 1

BitmapAnd (cost=4,116.64..4,116.64 rows=3,625 width=0) (actual time=1,987.232..1,987.232 rows=0 loops=1)

122. 410.123 410.123 ↑ 1.0 50,154 1

Bitmap Index Scan on form_metadata_formname (cost=0.00..639.68 rows=51,234 width=0) (actual time=410.123..410.123 rows=50,154 loops=1)

  • Index Cond: (formname = 'community_event'::text)
123. 1,571.169 1,571.169 ↑ 1.1 245,835 1

Bitmap Index Scan on form_metadata_reported (cost=0.00..3,474.89 rows=261,643 width=0) (actual time=1,571.169..1,571.169 rows=245,835 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
124. 100.035 769,508.394 ↑ 1.0 1 1,842

Index Only Scan using contactview_chp_uuid on contactview_chp chp (cost=0.28..1,227.73 rows=1 width=40) (actual time=417.749..417.757 rows=1 loops=1,842)

  • Index Cond: (uuid = chwlist.chw_uuid)
  • Heap Fetches: 603
125.          

SubPlan (for Index Only Scan)

126. 69.078 275,307.162 ↑ 9.0 1 1,842

Nested Loop Semi Join (cost=0.56..1,700.35 rows=9 width=0) (actual time=149.461..149.461 rows=1 loops=1,842)

  • Join Filter: (meta_1.formname = "*VALUES*".column1)
  • Rows Removed by Join Filter: 18
127. 275,202.168 275,202.168 ↑ 19.0 6 1,842

Index Scan using form_metadata_chw on form_metadata meta_1 (cost=0.56..1,695.18 rows=114 width=16) (actual time=132.329..149.404 rows=6 loops=1,842)

  • Index Cond: (chw = chp.uuid)
  • Filter: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 331
128. 35.908 35.916 ↑ 1.0 3 11,972

Materialize (cost=0.00..0.05 rows=3 width=32) (actual time=0.001..0.003 rows=3 loops=11,972)

129. 0.008 0.008 ↑ 1.0 3 1

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

130. 0.000 0.000 ↓ 0.0 0

Hash Semi Join (cost=0.54..12,617.08 rows=20,126 width=36) (never executed)

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

Index Scan using form_metadata_reported on form_metadata meta_2 (cost=0.46..11,706.29 rows=261,643 width=52) (never executed)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
132. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.04..0.04 rows=3 width=32) (never executed)

133. 0.000 0.000 ↓ 0.0 0

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

134. 0.593 11,369.657 ↓ 0.0 0 191

Nested Loop (cost=2.40..11.01 rows=1 width=0) (actual time=59.527..59.527 rows=0 loops=191)

135. 0.402 11,366.028 ↓ 0.0 0 191

Nested Loop (cost=1.84..8.41 rows=1 width=647) (actual time=59.508..59.508 rows=0 loops=191)

136. 1.337 11,362.017 ↓ 0.0 0 191

Nested Loop (cost=1.28..5.81 rows=1 width=36) (actual time=59.487..59.487 rows=0 loops=191)

  • Join Filter: (chw.parent_uuid = cmd.parent_uuid)
137. 3.820 2,171.670 ↑ 1.0 1 191

Nested Loop (cost=0.85..4.90 rows=1 width=108) (actual time=11.319..11.370 rows=1 loops=191)

138. 1,154.213 1,154.213 ↑ 1.0 1 191

Index Scan using contactview_metadata_uuid on contactview_metadata chw (cost=0.43..2.45 rows=1 width=72) (actual time=6.042..6.043 rows=1 loops=191)

  • Index Cond: (uuid = chp.uuid)
139. 1,013.637 1,013.637 ↑ 1.0 1 191

Index Only Scan using contactview_metadata_uuid on contactview_metadata chwarea (cost=0.43..2.45 rows=1 width=36) (actual time=5.260..5.307 rows=1 loops=191)

  • Index Cond: (uuid = chw.parent_uuid)
  • Heap Fetches: 10
140. 9,189.010 9,189.010 ↓ 0.0 0 191

Index Scan using contactview_metadata_parent_uuid on contactview_metadata cmd (cost=0.43..0.90 rows=1 width=36) (actual time=48.110..48.110 rows=0 loops=191)

  • Index Cond: (parent_uuid = chwarea.uuid)
  • Filter: ((type = 'clinic'::text) AND (reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 94
141. 3.609 3.609 ↑ 1.0 1 1

Index Scan using couchdb_doc_uuid on couchdb (cost=0.56..2.59 rows=1 width=647) (actual time=3.609..3.609 rows=1 loops=1)

  • Index Cond: ((doc ->> '_id'::text) = chp.uuid)
  • Filter: (((doc ->> 'type'::text) = 'person'::text) AND ((doc ->> 'type'::text) = ANY ('{clinic,district_hospital,health_center,person}'::text[])))
142. 3.036 3.036 ↑ 1.0 1 1

Index Scan using couchdb_doc_uuid on couchdb couchdb_1 (cost=0.56..2.59 rows=1 width=647) (actual time=3.036..3.036 rows=1 loops=1)

  • Index Cond: ((doc ->> '_id'::text) = (couchdb.doc #>> '{parent,_id}'::text[]))
  • Filter: (((doc ->> 'type'::text) = 'health_center'::text) AND ((doc ->> 'type'::text) = ANY ('{clinic,district_hospital,health_center,person}'::text[])))
143. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=2,868.26..70,539.83 rows=1 width=32) (never executed)

  • Hash Cond: ((couchdb_2.doc #>> '{parent,_id}'::text[]) = (couchdb_3.doc ->> '_id'::text))
144. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2,696.93..70,362.13 rows=1,018 width=647) (never executed)

145. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2,696.37..9,721.72 rows=50,853 width=36) (never executed)

146. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2,695.95..8,146.34 rows=2,330 width=72) (never executed)

147. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on contactview_metadata cmd_1 (cost=2,695.52..5,027.19 rows=2,330 width=36) (never executed)

  • Recheck Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone) AND (type = 'clinic'::text))
148. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=2,695.52..2,695.52 rows=2,330 width=0) (never executed)

149. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on contactview_metadata_reported (cost=0.00..154.71 rows=11,825 width=0) (never executed)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
150. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on contactview_metadata_type (cost=0.00..2,539.40 rows=238,396 width=0) (never executed)

  • Index Cond: (type = 'clinic'::text)
151. 0.000 0.000 ↓ 0.0 0

Index Only Scan using contactview_metadata_uuid on contactview_metadata chwarea_1 (cost=0.43..1.33 rows=1 width=36) (never executed)

  • Index Cond: (uuid = cmd_1.parent_uuid)
  • Heap Fetches: 0
152. 0.000 0.000 ↓ 0.0 0

Index Scan using contactview_metadata_parent_uuid on contactview_metadata chw_1 (cost=0.43..0.60 rows=8 width=72) (never executed)

  • Index Cond: (parent_uuid = chwarea_1.uuid)
153. 0.000 0.000 ↓ 0.0 0

Index Scan using couchdb_doc_uuid on couchdb couchdb_2 (cost=0.56..1.18 rows=1 width=647) (never executed)

  • Index Cond: ((doc ->> '_id'::text) = chw_1.uuid)
  • Filter: (((doc ->> 'type'::text) = 'person'::text) AND ((doc ->> 'type'::text) = ANY ('{clinic,district_hospital,health_center,person}'::text[])))
154. 0.000 0.000 ↓ 0.0 0

Hash (cost=169.07..169.07 rows=180 width=647) (never executed)

155. 0.000 0.000 ↓ 0.0 0

Index Scan using couchdb_doc_type on couchdb couchdb_3 (cost=0.43..169.07 rows=180 width=647) (never executed)

  • Index Cond: (((doc ->> 'type'::text) = ANY ('{clinic,district_hospital,health_center,person}'::text[])) AND ((doc ->> 'type'::text) = 'health_center'::text))
156. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.53..1,025.01 rows=1 width=0) (never executed)

157. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.97..965.07 rows=92 width=683) (never executed)

158. 0.000 0.000 ↓ 0.0 0

Index Scan using couchdb_doc_form_postnatal_care_follow_up_count on couchdb form_3 (cost=0.42..621.98 rows=207 width=647) (never executed)

  • Index Cond: ((doc #>> '{fields,follow_up_count}'::text[]) = '1'::text)
159. 0.000 0.000 ↓ 0.0 0

Index Only Scan using form_metadata_uuid on form_metadata fm (cost=0.56..1.65 rows=1 width=36) (never executed)

  • Index Cond: (uuid = (form_3.doc ->> '_id'::text))
  • Heap Fetches: 0
160. 0.000 0.000 ↓ 0.0 0

Index Scan using form_metadata_uuid on form_metadata meta_3 (cost=0.56..0.64 rows=1 width=36) (never executed)

  • Index Cond: (uuid = fm.uuid)
  • Filter: ((chw = chp.uuid) AND (reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
161. 137.781 482,731.540 ↓ 222.1 1,555 1

Nested Loop (cost=1.53..1,024.78 rows=7 width=36) (actual time=2,630.494..482,731.540 rows=1,555 loops=1)

162. 162,320.856 395,045.311 ↓ 504.0 46,371 1

Nested Loop (cost=0.97..965.07 rows=92 width=683) (actual time=293.275..395,045.311 rows=46,371 loops=1)

163. 126,201.463 126,201.463 ↓ 224.5 46,476 1

Index Scan using couchdb_doc_form_postnatal_care_follow_up_count on couchdb form_4 (cost=0.42..621.98 rows=207 width=647) (actual time=7.054..126,201.463 rows=46,476 loops=1)

  • Index Cond: ((doc #>> '{fields,follow_up_count}'::text[]) = '1'::text)
164. 106,522.992 106,522.992 ↑ 1.0 1 46,476

Index Only Scan using form_metadata_uuid on form_metadata fm_1 (cost=0.56..1.65 rows=1 width=36) (actual time=2.290..2.292 rows=1 loops=46,476)

  • Index Cond: (uuid = (form_4.doc ->> '_id'::text))
  • Heap Fetches: 1,824
165. 87,548.448 87,548.448 ↓ 0.0 0 46,371

Index Scan using form_metadata_uuid on form_metadata meta_4 (cost=0.56..0.64 rows=1 width=72) (actual time=1.887..1.888 rows=0 loops=46,371)

  • Index Cond: (uuid = fm_1.uuid)
  • Filter: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 1
166. 4,033.980 4,033.980 ↑ 1.3 1,702 1,842

CTE Scan on household_cte (cost=21,677.51..21,720.55 rows=2,152 width=44) (actual time=0.542..2.190 rows=1,702 loops=1,842)

167.          

CTE household_cte

168. 150.559 1,204.403 ↑ 1.3 1,702 1

GroupAggregate (cost=20,907.49..21,677.51 rows=2,152 width=48) (actual time=994.789..1,204.403 rows=1,702 loops=1)

  • Group Key: useview_households_visited.chw, 0
169. 236.549 1,053.844 ↑ 1.1 68,641 1

Sort (cost=20,907.49..21,094.61 rows=74,850 width=76) (actual time=994.766..1,053.844 rows=68,641 loops=1)

  • Sort Key: useview_households_visited.chw
  • Sort Method: quicksort Memory: 12,725kB
170. 817.295 817.295 ↑ 1.1 68,641 1

Index Only Scan using households_date_visited_chw_hh on useview_households_visited (cost=0.58..14,847.74 rows=74,850 width=76) (actual time=25.326..817.295 rows=68,641 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
  • Heap Fetches: 68,641
171. 515,183.454 515,183.454 ↑ 1.5 1,416 1,842

CTE Scan on demographics (cost=155,178.14..155,221.68 rows=2,177 width=148) (actual time=0.386..279.687 rows=1,416 loops=1,842)

172.          

CTE demographics

173. 287.211 512,793.573 ↑ 1.5 1,416 1

GroupAggregate (cost=0.58..155,178.14 rows=2,177 width=152) (actual time=708.769..512,793.573 rows=1,416 loops=1)

  • Group Key: population_1.chp_area_uuid, 0
174. 512,506.362 512,506.362 ↑ 1.1 9,264 1

Index Scan using useview_population_demographics_chp_area_reported on useview_population_demographics population_1 (cost=0.58..151,585.02 rows=9,852 width=86) (actual time=213.578..512,506.362 rows=9,264 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
175. 8.703 3,040.678 ↑ 1.9 1,139 1

Materialize (cost=15,183.40..15,685.53 rows=2,145 width=64) (actual time=2,827.382..3,040.678 rows=1,139 loops=1)

176. 180.768 3,031.975 ↑ 1.9 1,139 1

GroupAggregate (cost=15,183.40..15,658.72 rows=2,145 width=64) (actual time=2,827.376..3,031.975 rows=1,139 loops=1)

  • Group Key: survey.chw, 0
177. 87.633 2,851.207 ↑ 1.0 19,737 1

Sort (cost=15,183.40..15,233.83 rows=20,172 width=88) (actual time=2,827.300..2,851.207 rows=19,737 loops=1)

  • Sort Key: survey.chw
  • Sort Method: quicksort Memory: 3,544kB
178. 2,763.574 2,763.574 ↑ 1.0 19,737 1

Index Scan using useview_family_survey_uuid_reported_chw on useview_family_survey survey (cost=0.45..13,741.09 rows=20,172 width=88) (actual time=91.586..2,763.574 rows=19,737 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
179. 5,443.941 15,128.346 ↑ 58.1 2,238 1,842

HashAggregate (cost=12,786.72..14,087.26 rows=130,054 width=48) (actual time=5.460..8.213 rows=2,238 loops=1,842)

  • Group Key: contactview_metadata_1.parent_uuid, 0
180. 9,684.405 9,684.405 ↑ 1.0 238,382 1

Index Scan using contactview_metadata_type on contactview_metadata contactview_metadata_1 (cost=0.43..10,998.75 rows=238,396 width=40) (actual time=45.868..9,684.405 rows=238,382 loops=1)

  • Index Cond: (type = 'clinic'::text)
181. 5.953 93,169.651 ↓ 1.0 1,870 1

Materialize (cost=0.55..34,406.75 rows=1,851 width=48) (actual time=310.267..93,169.651 rows=1,870 loops=1)

182. 1,124.125 93,163.698 ↓ 1.0 1,870 1

GroupAggregate (cost=0.55..34,383.61 rows=1,851 width=48) (actual time=310.260..93,163.698 rows=1,870 loops=1)

  • Group Key: useview_chp_sync.chw_uuid, 0
183. 92,039.573 92,039.573 ↑ 1.0 771,523 1

Index Only Scan using useview_chp_sync_chw_uuid_latest_replication_date on useview_chp_sync (cost=0.55..28,375.58 rows=796,135 width=48) (actual time=12.779..92,039.573 rows=771,523 loops=1)

  • Heap Fetches: 504,345
184. 3,313.594 101,986.014 ↓ 1.0 2,221 1,842

Materialize (cost=268,030.11..268,085.16 rows=2,202 width=48) (actual time=53.567..55.367 rows=2,221 loops=1,842)

185. 2.105 98,672.420 ↓ 1.0 2,221 1

Subquery Scan on population_all_time (cost=268,030.11..268,074.15 rows=2,202 width=48) (actual time=98,668.554..98,672.420 rows=2,221 loops=1)

186. 1,766.049 98,670.315 ↓ 1.0 2,221 1

HashAggregate (cost=268,030.11..268,052.13 rows=2,202 width=48) (actual time=98,668.551..98,670.315 rows=2,221 loops=1)

  • Group Key: population.chp_area_uuid, 0
187. 96,904.266 96,904.266 ↑ 1.0 970,565 1

Seq Scan on useview_population_demographics population (cost=0.00..260,707.06 rows=976,406 width=76) (actual time=20,240.002..96,904.266 rows=970,565 loops=1)

188. 3.098 3,324.371 ↓ 6.9 1,373 1

Materialize (cost=5,295.29..5,423.20 rows=200 width=56) (actual time=3,276.459..3,324.371 rows=1,373 loops=1)

189. 36.359 3,321.273 ↓ 6.9 1,373 1

GroupAggregate (cost=5,295.29..5,420.70 rows=200 width=56) (actual time=3,276.454..3,321.273 rows=1,373 loops=1)

  • Group Key: "*SELECT* 1".reported_by, 0
190. 52.487 3,284.914 ↑ 1.1 6,634 1

Sort (cost=5,295.29..5,312.77 rows=6,995 width=80) (actual time=3,276.348..3,284.914 rows=6,634 loops=1)

  • Sort Key: "*SELECT* 1".reported_by
  • Sort Method: quicksort Memory: 1,125kB
191. 21.390 3,232.427 ↑ 1.1 6,634 1

Result (cost=0.00..4,848.58 rows=6,995 width=80) (actual time=384.267..3,232.427 rows=6,634 loops=1)

192. 13.109 3,211.037 ↑ 1.1 6,634 1

Append (cost=0.00..4,778.63 rows=6,995 width=76) (actual time=384.258..3,211.037 rows=6,634 loops=1)

193. 0.142 391.041 ↑ 1.0 81 1

Subquery Scan on *SELECT* 1 (cost=0.00..369.81 rows=82 width=78) (actual time=384.256..391.041 rows=81 loops=1)

194. 390.899 390.899 ↑ 1.0 81 1

Seq Scan on useview_fp_registration (cost=0.00..368.99 rows=82 width=256) (actual time=384.253..390.899 rows=81 loops=1)

  • Filter: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 4,719
195. 0.944 177.486 ↑ 1.1 684 1

Subquery Scan on *SELECT* 2 (cost=0.44..419.02 rows=735 width=78) (actual time=97.053..177.486 rows=684 loops=1)

196. 176.542 176.542 ↑ 1.1 684 1

Index Scan using index_useview_person_fp_registration_reported_chw_patient_id on useview_person_fp_registration (cost=0.44..411.67 rows=735 width=256) (actual time=97.051..176.542 rows=684 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
  • Filter: (patient_age_in_years >= 15)
  • Rows Removed by Filter: 124
197. 33.092 2,629.401 ↑ 1.1 5,869 1

Subquery Scan on *SELECT* 3 (cost=0.45..3,989.80 rows=6,178 width=76) (actual time=68.778..2,629.401 rows=5,869 loops=1)

198. 2,596.309 2,596.309 ↑ 1.1 5,869 1

Index Scan using useview_postnatal_reported on useview_postnatal_care useview_postnatal_care_1 (cost=0.45..3,928.02 rows=6,178 width=254) (actual time=68.776..2,596.309 rows=5,869 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
199. 3.931 12,270.208 ↓ 8.5 1,698 1

Materialize (cost=31,570.89..31,590.89 rows=200 width=76) (actual time=11,761.644..12,270.208 rows=1,698 loops=1)

200. 403.217 12,266.277 ↓ 8.5 1,698 1

GroupAggregate (cost=31,570.89..31,588.39 rows=200 width=76) (actual time=11,761.638..12,266.277 rows=1,698 loops=1)

  • Group Key: assess_u1fu_pnc_aggregated.chw, 0
201.          

CTE assess_u1fu_pnc

202. 58.250 11,084.973 ↓ 1.0 46,788 1

Append (cost=0.46..26,832.66 rows=46,128 width=99) (actual time=35.999..11,084.973 rows=46,788 loops=1)

203. 10,636.582 10,636.582 ↑ 1.1 40,002 1

Index Scan using useview_assessment_reported on useview_assessment (cost=0.46..22,021.24 rows=43,070 width=94) (actual time=35.997..10,636.582 rows=40,002 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
204. 238.481 238.481 ↑ 1.1 920 1

Index Scan using useview_u1_follow_up_reported_chw_uuid on useview_u1_follow_up (cost=0.44..350.06 rows=999 width=161) (actual time=90.152..238.481 rows=920 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
205. 151.660 151.660 ↓ 2.8 5,866 1

Index Scan using useview_postnatal_reported on useview_postnatal_care useview_postnatal_care_2 (cost=0.45..4,000.08 rows=2,059 width=185) (actual time=3.350..151.660 rows=5,866 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
  • Filter: is_date((date_of_birth)::character varying)
  • Rows Removed by Filter: 3
206.          

CTE assess_u1fu_pnc_aggregated

207. 55.720 11,573.571 ↓ 215.2 43,038 1

Unique (cost=4,495.95..4,726.59 rows=200 width=204) (actual time=11,482.141..11,573.571 rows=43,038 loops=1)

208. 330.923 11,517.851 ↓ 1.0 46,788 1

Sort (cost=4,495.95..4,611.27 rows=46,128 width=204) (actual time=11,482.139..11,517.851 rows=46,788 loops=1)

  • Sort Key: assess_u1fu_pnc.patient_id, assess_u1fu_pnc.reported DESC
  • Sort Method: quicksort Memory: 9,174kB
209. 11,186.928 11,186.928 ↓ 1.0 46,788 1

CTE Scan on assess_u1fu_pnc (cost=0.00..922.56 rows=46,128 width=204) (actual time=36.006..11,186.928 rows=46,788 loops=1)

210. 230.398 11,863.060 ↓ 215.2 43,038 1

Sort (cost=11.64..12.14 rows=200 width=200) (actual time=11,761.273..11,863.060 rows=43,038 loops=1)

  • Sort Key: assess_u1fu_pnc_aggregated.chw
  • Sort Method: quicksort Memory: 8,368kB
211. 11,632.662 11,632.662 ↓ 215.2 43,038 1

CTE Scan on assess_u1fu_pnc_aggregated (cost=0.00..4.00 rows=200 width=200) (actual time=11,482.147..11,632.662 rows=43,038 loops=1)

212. 0.029 1,374.638 ↑ 7.5 11 1

Materialize (cost=1,221.09..1,226.46 rows=82 width=56) (actual time=1,374.090..1,374.638 rows=11 loops=1)

213. 0.579 1,374.609 ↑ 7.5 11 1

GroupAggregate (cost=1,221.09..1,225.44 rows=82 width=56) (actual time=1,374.084..1,374.609 rows=11 loops=1)

  • Group Key: dr.chw, 0
214. 0.094 1,374.030 ↑ 6.4 13 1

Sort (cost=1,221.09..1,221.30 rows=83 width=687) (actual time=1,374.005..1,374.030 rows=13 loops=1)

  • Sort Key: dr.chw
  • Sort Method: quicksort Memory: 51kB
215. 0.087 1,373.936 ↑ 6.4 13 1

Nested Loop Left Join (cost=0.99..1,218.44 rows=83 width=687) (actual time=120.600..1,373.936 rows=13 loops=1)

216. 1,138.224 1,138.224 ↑ 6.4 13 1

Index Scan using form_metadata_formname on form_metadata dr (cost=0.43..1,003.47 rows=83 width=72) (actual time=92.142..1,138.224 rows=13 loops=1)

  • Index Cond: (formname = 'death_report'::text)
  • Filter: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 252
217. 235.625 235.625 ↑ 1.0 1 13

Index Scan using couchdb_doc_uuid on couchdb form (cost=0.56..2.58 rows=1 width=647) (actual time=18.124..18.125 rows=1 loops=13)

  • Index Cond: (dr.uuid = (doc ->> '_id'::text))
  • Filter: ((doc ->> 'form'::text) = 'death_report'::text)
218. 0.004 6,372.805 ↓ 0.0 0 1

Materialize (cost=15,388.88..15,388.95 rows=2 width=48) (actual time=6,372.805..6,372.805 rows=0 loops=1)

219. 0.003 6,372.801 ↓ 0.0 0 1

GroupAggregate (cost=15,388.88..15,388.92 rows=2 width=48) (actual time=6,372.801..6,372.801 rows=0 loops=1)

  • Group Key: "*SELECT* 1_1".chw_uuid, 0
220. 0.013 6,372.798 ↓ 0.0 0 1

Sort (cost=15,388.88..15,388.89 rows=2 width=40) (actual time=6,372.798..6,372.798 rows=0 loops=1)

  • Sort Key: "*SELECT* 1_1".chw_uuid
  • Sort Method: quicksort Memory: 25kB
221. 0.002 6,372.785 ↓ 0.0 0 1

Result (cost=11,400.72..15,388.87 rows=2 width=40) (actual time=6,372.785..6,372.785 rows=0 loops=1)

222. 0.004 6,372.783 ↓ 0.0 0 1

Append (cost=11,400.72..15,388.85 rows=2 width=36) (actual time=6,372.783..6,372.783 rows=0 loops=1)

223. 0.031 5,192.532 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1_1 (cost=11,400.72..11,401.77 rows=1 width=36) (actual time=5,192.532..5,192.532 rows=0 loops=1)

  • Filter: (("*SELECT* 1_1".reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND ("*SELECT* 1_1".reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 5
224. 0.011 5,192.501 ↑ 4.2 5 1

Unique (cost=11,400.72..11,400.83 rows=21 width=80) (actual time=5,192.482..5,192.501 rows=5 loops=1)

225. 0.063 5,192.490 ↑ 1.9 11 1

Sort (cost=11,400.72..11,400.78 rows=21 width=80) (actual time=5,192.480..5,192.490 rows=11 loops=1)

  • Sort Key: pv.patient_id
  • Sort Method: quicksort Memory: 26kB
226. 280.570 5,192.427 ↑ 1.9 11 1

Hash Join (cost=3,982.90..11,400.26 rows=21 width=80) (actual time=3,208.920..5,192.427 rows=11 loops=1)

  • Hash Cond: (pv.patient_id = (form_1.doc #>> '{fields,inputs,contact,_id}'::text[]))
227. 2,509.702 2,509.702 ↑ 1.0 170,751 1

Seq Scan on useview_pregnancy_visit pv (cost=0.00..6,560.56 rows=171,318 width=36) (actual time=90.278..2,509.702 rows=170,751 loops=1)

  • Filter: (patient_id <> ''::text)
  • Rows Removed by Filter: 85
228. 3.884 2,402.155 ↓ 44.2 265 1

Hash (cost=3,982.82..3,982.82 rows=6 width=691) (actual time=2,402.155..2,402.155 rows=265 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 409kB
229. 1.566 2,398.271 ↓ 44.2 265 1

Nested Loop (cost=0.99..3,982.82 rows=6 width=691) (actual time=6.203..2,398.271 rows=265 loops=1)

230. 1.105 1.105 ↑ 4.4 265 1

Index Scan using form_metadata_formname on form_metadata dr_1 (cost=0.43..962.52 rows=1,170 width=80) (actual time=0.023..1.105 rows=265 loops=1)

  • Index Cond: (formname = 'death_report'::text)
231. 2,395.600 2,395.600 ↑ 1.0 1 265

Index Scan using couchdb_doc_uuid on couchdb form_1 (cost=0.56..2.57 rows=1 width=647) (actual time=8.997..9.040 rows=1 loops=265)

  • Index Cond: ((doc ->> '_id'::text) = dr_1.uuid)
  • Filter: ((doc ->> 'form'::text) = 'death_report'::text)
232. 0.031 1,180.247 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=3,986.88..3,987.08 rows=1 width=36) (actual time=1,180.247..1,180.247 rows=0 loops=1)

  • Filter: (("*SELECT* 2_1".reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND ("*SELECT* 2_1".reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 6
233. 0.017 1,180.216 ↓ 1.5 6 1

Unique (cost=3,986.88..3,986.90 rows=4 width=80) (actual time=1,180.192..1,180.216 rows=6 loops=1)

234. 0.064 1,180.199 ↓ 4.0 16 1

Sort (cost=3,986.88..3,986.89 rows=4 width=80) (actual time=1,180.190..1,180.199 rows=16 loops=1)

  • Sort Key: pc.patient_id
  • Sort Method: quicksort Memory: 27kB
235. 3.787 1,180.135 ↓ 4.0 16 1

Nested Loop (cost=1.41..3,986.84 rows=4 width=80) (actual time=225.282..1,180.135 rows=16 loops=1)

236. 1.464 12.998 ↓ 44.2 265 1

Nested Loop (cost=0.99..3,982.82 rows=6 width=691) (actual time=0.080..12.998 rows=265 loops=1)

237. 1.199 1.199 ↑ 4.4 265 1

Index Scan using form_metadata_formname on form_metadata dr_2 (cost=0.43..962.52 rows=1,170 width=80) (actual time=0.039..1.199 rows=265 loops=1)

  • Index Cond: (formname = 'death_report'::text)
238. 10.335 10.335 ↑ 1.0 1 265

Index Scan using couchdb_doc_uuid on couchdb form_2 (cost=0.56..2.57 rows=1 width=647) (actual time=0.037..0.039 rows=1 loops=265)

  • Index Cond: ((doc ->> '_id'::text) = dr_2.uuid)
  • Filter: ((doc ->> 'form'::text) = 'death_report'::text)
239. 1,163.350 1,163.350 ↓ 0.0 0 265

Index Scan using useview_postnatal_patient_id on useview_postnatal_care pc (cost=0.42..0.66 rows=1 width=36) (actual time=4.287..4.390 rows=0 loops=265)

  • Index Cond: (patient_id = (form_2.doc #>> '{fields,inputs,contact,_id}'::text[]))
  • Filter: ((patient_id <> ''::text) AND (date_part('year'::text, age((('now'::cstring)::date)::timestamp with time zone, ((date_of_birth)::date)::timestamp with time zone)) >= '12'::double precision))
240. 13.856 10,304.575 ↑ 1.1 1,695 1

Materialize (cost=21,247.08..22,761.89 rows=1,800 width=48) (actual time=9,543.177..10,304.575 rows=1,695 loops=1)

241. 576.612 10,290.719 ↑ 1.1 1,695 1

GroupAggregate (cost=21,247.08..22,739.39 rows=1,800 width=48) (actual time=9,543.170..10,290.719 rows=1,695 loops=1)

  • Group Key: useview_home_visit.reported_by, 0
242. 742.178 9,714.107 ↑ 1.0 144,837 1

Sort (cost=21,247.08..21,615.66 rows=147,431 width=77) (actual time=9,542.983..9,714.107 rows=144,837 loops=1)

  • Sort Key: useview_home_visit.reported_by
  • Sort Method: quicksort Memory: 26,512kB
243. 8,971.929 8,971.929 ↑ 1.0 144,837 1

Index Only Scan using index_useview_home_visit_reported_family_uuid_reported_by on useview_home_visit (cost=0.58..8,590.36 rows=147,431 width=77) (actual time=7.892..8,971.929 rows=144,837 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
  • Heap Fetches: 324
244. 0.276 446.384 ↑ 1.7 115 1

Materialize (cost=2,644.33..3,599.64 rows=200 width=97) (actual time=436.894..446.384 rows=115 loops=1)

245. 6.985 446.108 ↑ 1.7 115 1

GroupAggregate (cost=2,644.33..3,597.14 rows=200 width=97) (actual time=436.889..446.108 rows=115 loops=1)

  • Group Key: "*SELECT* 1_2".reported_by, 0
246. 5.938 439.123 ↑ 10.1 2,205 1

Sort (cost=2,644.33..2,700.26 rows=22,372 width=178) (actual time=436.748..439.123 rows=2,205 loops=1)

  • Sort Key: "*SELECT* 1_2".reported_by
  • Sort Method: quicksort Memory: 682kB
247. 4.985 433.185 ↑ 10.1 2,205 1

Merge Right Join (cost=683.58..1,028.02 rows=22,372 width=178) (actual time=425.685..433.185 rows=2,205 loops=1)

  • Merge Cond: (useview_fp_follow_up_prospective_ke.source_id = "*SELECT* 1_2".form_uuid)
248. 9.231 51.360 ↓ 1.0 1,838 1

Sort (cost=163.33..167.76 rows=1,772 width=89) (actual time=50.190..51.360 rows=1,838 loops=1)

  • Sort Key: useview_fp_follow_up_prospective_ke.source_id
  • Sort Method: quicksort Memory: 307kB
249. 1.929 42.129 ↓ 1.0 1,839 1

Append (cost=0.00..67.72 rows=1,772 width=89) (actual time=4.011..42.129 rows=1,839 loops=1)

250. 14.714 14.714 ↑ 1.0 775 1

Seq Scan on useview_fp_follow_up_prospective_ke (cost=0.00..29.87 rows=787 width=88) (actual time=4.009..14.714 rows=775 loops=1)

251. 20.887 20.887 ↑ 1.0 513 1

Seq Scan on useview_fp_follow_up_short_term_ke (cost=0.00..19.13 rows=513 width=89) (actual time=11.442..20.887 rows=513 loops=1)

252. 4.599 4.599 ↓ 1.2 551 1

Seq Scan on useview_fp_follow_up_long_term_ke (cost=0.00..18.72 rows=472 width=89) (actual time=2.230..4.599 rows=551 loops=1)

253. 15.369 376.840 ↑ 1.1 2,205 1

Sort (cost=520.25..526.56 rows=2,525 width=122) (actual time=375.482..376.840 rows=2,205 loops=1)

  • Sort Key: "*SELECT* 1_2".form_uuid
  • Sort Method: quicksort Memory: 682kB
254. 5.456 361.471 ↑ 1.1 2,205 1

Append (cost=0.30..377.56 rows=2,525 width=122) (actual time=21.994..361.471 rows=2,205 loops=1)

255. 0.299 50.163 ↑ 1.2 277 1

Subquery Scan on *SELECT* 1_2 (cost=0.30..81.17 rows=330 width=122) (actual time=21.992..50.163 rows=277 loops=1)

256. 0.428 49.864 ↑ 1.2 277 1

Result (cost=0.30..77.87 rows=330 width=122) (actual time=21.990..49.864 rows=277 loops=1)

257. 0.290 49.436 ↑ 1.2 277 1

Append (cost=0.30..74.57 rows=330 width=121) (actual time=21.987..49.436 rows=277 loops=1)

258. 30.718 30.718 ↑ 1.2 115 1

Index Scan using index_useview_fp_follow_up_prospective_ke_reported_patient_id_u on useview_fp_follow_up_prospective_ke useview_fp_follow_up_prospective_ke_1 (cost=0.30..24.55 rows=141 width=121) (actual time=21.985..30.718 rows=115 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
259. 15.572 15.572 ↑ 1.3 72 1

Index Scan using index_useview_fp_follow_up_short_term_ke_reported_patient_id_uu on useview_fp_follow_up_short_term_ke useview_fp_follow_up_short_term_ke_1 (cost=0.30..14.78 rows=92 width=121) (actual time=14.929..15.572 rows=72 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
260. 2.856 2.856 ↑ 1.1 90 1

Seq Scan on useview_fp_follow_up_long_term_ke useview_fp_follow_up_long_term_ke_1 (cost=0.00..35.24 rows=97 width=121) (actual time=0.024..2.856 rows=90 loops=1)

  • Filter: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 461
261. 2.947 305.852 ↑ 1.1 1,928 1

Subquery Scan on *SELECT* 2_2 (cost=0.44..296.39 rows=2,195 width=122) (actual time=29.404..305.852 rows=1,928 loops=1)

262. 302.905 302.905 ↑ 1.1 1,928 1

Index Scan using index_useview_fp_registration_ke_reported_patient_id_uuid on useview_fp_registration_ke (cost=0.44..274.44 rows=2,195 width=122) (actual time=29.402..302.905 rows=1,928 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((date_trunc('month'::text, now()) - '2 mons'::interval) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
Planning time : 1,683.325 ms
Execution time : 2,008,340.779 ms