explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JvWs

Settings
# exclusive inclusive rows x rows loops node
1. 62.330 113,588.416 ↓ 1,842.0 1,842 1

GroupAggregate (cost=856,543.89..856,545.45 rows=1 width=650) (actual time=113,525.911..113,588.416 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, chwlist.interval_number, (date(chwlist.interval_start))
  • Functions: 916
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 164.476 ms, Inlining 55.290 ms, Optimization 9317.889 ms, Emission 6677.545 ms, Total 16215.200 ms
2.          

CTE fp

3. 5.674 136.326 ↓ 6.6 1,312 1

GroupAggregate (cost=4,249.75..4,360.44 rows=200 width=56) (actual time=130.215..136.326 rows=1,312 loops=1)

  • Group Key: "*SELECT* 1_3".reported_by, 0
4. 9.767 130.652 ↑ 1.0 6,137 1

Sort (cost=4,249.75..4,265.13 rows=6,154 width=80) (actual time=130.166..130.652 rows=6,137 loops=1)

  • Sort Key: "*SELECT* 1_3".reported_by
  • Sort Method: quicksort Memory: 1056kB
5. 1.174 120.885 ↑ 1.0 6,137 1

Result (cost=0.00..3,862.44 rows=6,154 width=80) (actual time=1.814..120.885 rows=6,137 loops=1)

6. 0.868 119.711 ↑ 1.0 6,137 1

Append (cost=0.00..3,800.90 rows=6,154 width=76) (actual time=1.811..119.711 rows=6,137 loops=1)

7. 0.011 5.354 ↓ 1.0 46 1

Subquery Scan on *SELECT* 1_3 (cost=0.00..350.30 rows=44 width=78) (actual time=1.810..5.354 rows=46 loops=1)

8. 5.343 5.343 ↓ 1.0 46 1

Seq Scan on useview_fp_registration (cost=0.00..349.86 rows=44 width=256) (actual time=1.807..5.343 rows=46 loops=1)

  • 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: 4816
9. 0.059 4.663 ↑ 1.0 294 1

Subquery Scan on *SELECT* 2_3 (cost=0.44..334.22 rows=298 width=78) (actual time=0.066..4.663 rows=294 loops=1)

10. 4.604 4.604 ↑ 1.0 294 1

Index Scan using index_useview_person_fp_registration_reported_chw_patient_id on useview_person_fp_registration (cost=0.44..331.24 rows=298 width=256) (actual time=0.064..4.604 rows=294 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))
  • Filter: (patient_age_in_years >= 15)
  • Rows Removed by Filter: 46
11. 1.133 108.826 ↑ 1.0 5,797 1

Subquery Scan on *SELECT* 3_1 (cost=0.45..3,085.61 rows=5,812 width=76) (actual time=0.097..108.826 rows=5,797 loops=1)

12. 107.693 107.693 ↑ 1.0 5,797 1

Index Scan using useview_postnatal_reported on useview_postnatal_care useview_postnatal_care_2 (cost=0.45..3,027.49 rows=5,812 width=254) (actual time=0.095..107.693 rows=5,797 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))
13. 15.346 113,526.086 ↓ 921.0 1,842 1

Sort (cost=852,183.45..852,183.45 rows=2 width=1,986) (actual time=113,525.813..113,526.086 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, chwlist.interval_number, (date(chwlist.interval_start))
  • Sort Method: quicksort Memory: 1648kB
14. 7.090 113,510.740 ↓ 921.0 1,842 1

Merge Left Join (cost=842,259.69..852,183.44 rows=2 width=1,986) (actual time=21,722.392..113,510.740 rows=1,842 loops=1)

  • Merge Cond: (chwlist.chw_uuid = "*SELECT* 1_2".reported_by)
  • Join Filter: (chwlist.interval_number = (0))
15. 6.074 113,490.002 ↓ 921.0 1,842 1

Merge Left Join (cost=840,123.35..849,332.57 rows=2 width=1,934) (actual time=21,710.858..113,490.002 rows=1,842 loops=1)

  • Merge Cond: (chwlist.chw_uuid = useview_home_visit.reported_by)
  • Join Filter: (chwlist.interval_number = (0))
16. 3.568 112,943.073 ↓ 921.0 1,842 1

Merge Left Join (cost=800,321.44..808,012.02 rows=2 width=1,926) (actual time=21,363.251..112,943.073 rows=1,842 loops=1)

  • Merge Cond: (chwlist.chw_uuid = "*SELECT* 1_1".chw_uuid)
  • Join Filter: (chwlist.interval_number = (0))
17. 4.143 112,835.599 ↓ 921.0 1,842 1

Merge Left Join (cost=792,140.84..799,831.33 rows=2 width=1,918) (actual time=21,259.338..112,835.599 rows=1,842 loops=1)

  • Merge Cond: (chwlist.chw_uuid = dr.chw)
  • Join Filter: (chwlist.interval_number = (0))
18. 8.032 112,817.118 ↓ 921.0 1,842 1

Merge Left Join (cost=791,889.76..799,576.73 rows=2 width=1,902) (actual time=21,248.508..112,817.118 rows=1,842 loops=1)

  • Merge Cond: (chwlist.chw_uuid = assess_u1fu_pnc_aggregated.chw)
  • Join Filter: (chwlist.interval_number = (0))
19. 243.179 112,445.818 ↓ 921.0 1,842 1

Nested Loop Left Join (cost=773,722.51..781,388.95 rows=2 width=1,862) (actual time=20,979.636..112,445.818 rows=1,842 loops=1)

  • Join Filter: ((chwlist.chw_uuid = fp.chw_uuid) AND (chwlist.interval_number = fp.interval_number))
  • Rows Removed by Join Filter: 2415417
20. 199.881 111,791.873 ↓ 921.0 1,842 1

Nested Loop Left Join (cost=773,722.51..781,376.95 rows=2 width=1,846) (actual time=20,842.524..111,791.873 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: 2043592
21. 230.976 111,032.024 ↓ 921.0 1,842 1

Nested Loop Left Join (cost=713,184.00..720,713.03 rows=2 width=1,874) (actual time=20,419.846..111,032.024 rows=1,842 loops=1)

  • Join Filter: ((chwlist.chw_uuid = sync.chw_uuid) AND (chwlist.interval_number = sync.interval_number))
  • Rows Removed by Join Filter: 2495082
22. 185.810 108,592.490 ↓ 921.0 1,842 1

Nested Loop Left Join (cost=543,964.16..551,338.28 rows=2 width=1,866) (actual time=18,377.854..108,592.490 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: 2066260
23. 8.310 107,225.958 ↓ 1,842.0 1,842 1

Merge Left Join (cost=532,601.48..537,170.74 rows=1 width=1,822) (actual time=18,252.150..107,225.958 rows=1,842 loops=1)

  • Merge Cond: (chwlist.chw_uuid = survey.chw)
  • Join Filter: (chwlist.interval_number = (0))
24. 209.665 107,158.619 ↓ 1,842.0 1,842 1

Nested Loop Left Join (cost=524,114.99..528,325.46 rows=1 width=1,798) (actual time=18,228.826..107,158.619 rows=1,842 loops=1)

  • Join Filter: ((chwlist.chw_area_uuid = population.chp_area_uuid) AND (chwlist.interval_number = (0)))
  • Rows Removed by Join Filter: 2292077
25. 330.658 105,814.282 ↓ 1,842.0 1,842 1

Nested Loop Left Join (cost=515,666.33..519,784.96 rows=1 width=1,686) (actual time=18,204.034..105,814.282 rows=1,842 loops=1)

  • Join Filter: ((chwlist.chw_uuid = useview_households_visited.chw) AND (chwlist.interval_number = (0)))
  • Rows Removed by Join Filter: 3039541
26. 16.714 23,160.960 ↓ 1,842.0 1,842 1

Nested Loop Left Join (cost=480,921.17..484,347.64 rows=1 width=1,678) (actual time=18,118.862..23,160.960 rows=1,842 loops=1)

  • Join Filter: (chwlist.interval_number = 0)
27. 89.795 20,585.708 ↓ 1,842.0 1,842 1

Nested Loop Left Join (cost=480,920.89..481,335.93 rows=1 width=1,674) (actual time=18,118.122..20,585.708 rows=1,842 loops=1)

  • Join Filter: ((chwlist.chw_uuid = meta.chw) AND (chwlist.interval_number = (0)))
  • Rows Removed by Join Filter: 930914
28. 6.681 20,206.719 ↓ 1,842.0 1,842 1

Merge Left Join (cost=476,727.47..477,080.94 rows=1 width=1,666) (actual time=18,066.996..20,206.719 rows=1,842 loops=1)

  • Merge Cond: (chwlist.chw_uuid = assess.chw)
  • Join Filter: (chwlist.interval_number = (0))
29. 59.996 20,120.113 ↓ 1,842.0 1,842 1

Nested Loop Left Join (cost=458,052.77..458,366.83 rows=1 width=1,658) (actual time=17,993.103..20,120.113 rows=1,842 loops=1)

  • Join Filter: ((chwlist.chw_uuid = p.chw) AND (chwlist.interval_number = (0)))
  • Rows Removed by Join Filter: 597072
30. 117.781 19,489.097 ↓ 1,842.0 1,842 1

Nested Loop Left Join (cost=424,042.55..424,349.60 rows=1 width=1,650) (actual time=17,550.184..19,489.097 rows=1,842 loops=1)

  • Join Filter: ((chwlist.chw_uuid = pnc_1.chw) AND (chwlist.interval_number = (0)))
  • Rows Removed by Join Filter: 1276243
31. 112.233 18,787.402 ↓ 1,842.0 1,842 1

Nested Loop Left Join (cost=422,237.83..422,498.93 rows=1 width=1,554) (actual time=17,541.856..18,787.402 rows=1,842 loops=1)

  • Join Filter: ((chwlist.chw_uuid = useview_pregnancy.chw) AND (chwlist.interval_number = (0)))
  • Rows Removed by Join Filter: 1229722
32. 76.391 18,347.293 ↓ 1,842.0 1,842 1

Nested Loop Left Join (cost=421,841.59..422,065.17 rows=1 width=1,538) (actual time=17,540.089..18,347.293 rows=1,842 loops=1)

  • Join Filter: ((chwlist.chw_area_uuid = contactview_metadata.parent_uuid) AND (chwlist.interval_number = (0)))
  • Rows Removed by Join Filter: 789071
33. 10.050 17,314.904 ↓ 1,842.0 1,842 1

Merge Left Join (cost=404,249.88..404,420.19 rows=1 width=1,530) (actual time=17,298.813..17,314.904 rows=1,842 loops=1)

  • Merge Cond: ((chwlist.chw_uuid = anc_pnc_u1fu.chw_uuid) AND (chwlist.interval_number = anc_pnc_u1fu.interval_number))
34. 5.732 16,185.396 ↓ 1,842.0 1,842 1

Sort (cost=32,500.32..32,500.32 rows=1 width=1,482) (actual time=16,183.943..16,185.396 rows=1,842 loops=1)

  • Sort Key: chwlist.chw_uuid, chwlist.interval_number
  • Sort Method: quicksort Memory: 938kB
35. 1.049 16,179.664 ↓ 1,842.0 1,842 1

Hash Right Join (cost=32,205.60..32,500.31 rows=1 width=1,482) (actual time=16,173.573..16,179.664 rows=1,842 loops=1)

  • Hash Cond: ((chwview_assessment.chw_uuid = chwlist.chw_uuid) AND ((0) = chwlist.interval_number))
36. 27.607 48.213 ↑ 1.3 1,624 1

HashAggregate (cost=23,967.51..24,204.32 rows=2,105 width=1,292) (actual time=43.155..48.213 rows=1,624 loops=1)

  • Group Key: chwview_assessment.chw_uuid, 0
37. 14.004 20.606 ↑ 1.1 33,495 1

Bitmap Heap Scan on chwview_assessment (cost=682.66..20,258.07 rows=35,328 width=352) (actual time=6.919..20.606 rows=33,495 loops=1)

  • Recheck Cond: ((reported_day >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 mon'::interval)))::timestamp without time zone) AND (reported_day < ((date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
  • Heap Blocks: exact=2182
38. 6.602 6.602 ↑ 1.1 33,495 1

Bitmap Index Scan on chwview_assessment_reported_chw_uuid (cost=0.00..673.83 rows=35,328 width=0) (actual time=6.602..6.602 rows=33,495 loops=1)

  • Index Cond: ((reported_day >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 mon'::interval)))::timestamp without time zone) AND (reported_day < ((date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
39. 0.998 16,130.402 ↓ 1,842.0 1,842 1

Hash (cost=8,238.07..8,238.07 rows=1 width=230) (actual time=16,130.402..16,130.402 rows=1,842 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 492kB
40. 0.818 16,129.404 ↓ 1,842.0 1,842 1

Hash Right Join (cost=8,185.15..8,238.07 rows=1 width=230) (actual time=16,128.264..16,129.404 rows=1,842 loops=1)

  • Hash Cond: ((anc.chw = chwlist.chw_uuid) AND ((0) = chwlist.interval_number))
41. 1.931 24.732 ↑ 1.4 1,341 1

HashAggregate (cost=7,983.42..8,002.66 rows=1,924 width=48) (actual time=24.395..24.732 rows=1,341 loops=1)

  • Group Key: anc.chw, 0
42. 1.224 22.801 ↓ 1.0 5,256 1

Bitmap Heap Scan on useview_pregnancy_visit anc (cost=4,341.75..7,944.26 rows=5,221 width=40) (actual time=21.616..22.801 rows=5,256 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))
  • Heap Blocks: exact=155
43. 21.577 21.577 ↓ 1.0 5,256 1

Bitmap Index Scan on useview_pregnancy_visit_pregnancy_reported_uuid (cost=0.00..4,340.45 rows=5,221 width=0) (actual time=21.577..21.577 rows=5,256 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))
44. 0.994 16,103.854 ↓ 1,842.0 1,842 1

Hash (cost=201.72..201.72 rows=1 width=222) (actual time=16,103.854..16,103.854 rows=1,842 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 469kB
45. 0.259 16,102.860 ↓ 1,842.0 1,842 1

Subquery Scan on chwlist (cost=191.48..201.72 rows=1 width=222) (actual time=16,094.442..16,102.860 rows=1,842 loops=1)

46. 2.077 16,102.601 ↓ 1,842.0 1,842 1

Nested Loop Anti Join (cost=191.48..201.71 rows=1 width=222) (actual time=16,094.440..16,102.601 rows=1,842 loops=1)

47. 1.115 16,095.832 ↓ 469.2 2,346 1

Group (cost=191.21..191.35 rows=5 width=222) (actual time=16,094.407..16,095.832 rows=2,346 loops=1)

  • Group Key: cmd.uuid, cmd.name, chp_1.supervisor_uuid, chp_1.area_uuid, cmeta.name, chp_1.uuid, chp_1.name, chp_1.phone, (date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 mon'::interval))), (0)
48. 8.517 16,094.717 ↓ 469.2 2,346 1

Sort (cost=191.21..191.22 rows=5 width=222) (actual time=16,094.387..16,094.717 rows=2,346 loops=1)

  • Sort Key: cmd.uuid, cmd.name, chp_1.supervisor_uuid, chp_1.area_uuid, cmeta.name, chp_1.uuid, chp_1.name, chp_1.phone, (date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 mon'::interval))), (0)
  • Sort Method: quicksort Memory: 720kB
49. 0.376 16,086.200 ↓ 469.2 2,346 1

Hash Join (cost=149.85..191.15 rows=5 width=222) (actual time=16,085.849..16,086.200 rows=2,346 loops=1)

  • Hash Cond: ((row_number() OVER (?)) = (row_number() OVER (?)))
50. 0.009 16,064.848 ↑ 1,000.0 1 1

WindowAgg (cost=59.85..87.35 rows=1,000 width=24) (actual time=16,064.847..16,064.848 rows=1 loops=1)

51. 0.006 16,064.839 ↑ 1,000.0 1 1

Sort (cost=59.85..62.35 rows=1,000 width=8) (actual time=16,064.839..16,064.839 rows=1 loops=1)

  • Sort Key: generate_series.generate_series
  • Sort Method: quicksort Memory: 25kB
52. 16,064.833 16,064.833 ↑ 1,000.0 1 1

Function Scan on generate_series (cost=0.02..10.02 rows=1,000 width=8) (actual time=16,064.833..16,064.833 rows=1 loops=1)

53. 1.194 20.976 ↓ 2,346.0 2,346 1

Hash (cost=89.99..89.99 rows=1 width=222) (actual time=20.975..20.976 rows=2,346 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 608kB
54. 1.114 19.782 ↓ 2,346.0 2,346 1

Nested Loop (cost=4.76..89.99 rows=1 width=222) (actual time=0.114..19.782 rows=2,346 loops=1)

55. 2.303 16.322 ↓ 2,346.0 2,346 1

Nested Loop (cost=4.69..89.88 rows=1 width=210) (actual time=0.091..16.322 rows=2,346 loops=1)

56. 0.633 2.289 ↓ 2,346.0 2,346 1

Nested Loop (cost=4.26..87.42 rows=1 width=193) (actual time=0.075..2.289 rows=2,346 loops=1)

57. 0.044 0.044 ↓ 13.0 13 1

Index Scan using contactview_metadata_type on contactview_metadata cmd (cost=0.43..2.65 rows=1 width=54) (actual time=0.032..0.044 rows=13 loops=1)

  • Index Cond: (type = 'district_hospital'::text)
  • Filter: ((name <> 'HQ'::text) AND (name <> 'HQ OVC'::text))
  • Rows Removed by Filter: 1
58. 1.300 1.612 ↓ 1.0 180 13

Bitmap Heap Scan on contactview_chp chp_1 (cost=3.84..83.02 rows=175 width=172) (actual time=0.033..0.124 rows=180 loops=13)

  • Recheck Cond: (branch_uuid = cmd.uuid)
  • Heap Blocks: exact=883
59. 0.312 0.312 ↓ 1.0 180 13

Bitmap Index Scan on contactview_chp_branch_uuid (cost=0.00..3.79 rows=175 width=0) (actual time=0.024..0.024 rows=180 loops=13)

  • Index Cond: (branch_uuid = cmd.uuid)
60. 11.730 11.730 ↑ 1.0 1 2,346

Index Scan using contactview_metadata_uuid on contactview_metadata cmeta (cost=0.43..2.45 rows=1 width=54) (actual time=0.005..0.005 rows=1 loops=2,346)

  • Index Cond: (uuid = chp_1.supervisor_uuid)
61. 2.346 2.346 ↑ 1.0 1 2,346

WindowAgg (cost=0.07..0.09 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=2,346)

62. 0.000 0.000 ↑ 1.0 1 2,346

Sort (cost=0.07..0.08 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=2,346)

  • Sort Key: generate_series_1.generate_series
  • Sort Method: quicksort Memory: 25kB
63. 0.013 0.013 ↑ 1.0 1 1

Function Scan on generate_series generate_series_1 (cost=0.05..0.06 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1)

64. 4.692 4.692 ↓ 0.0 0 2,346

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

  • Index Cond: (chw = chp_1.uuid)
  • Heap Fetches: 504
65. 3.972 1,119.458 ↓ 1.4 680 1

GroupAggregate (cost=371,749.56..371,912.61 rows=483 width=88) (actual time=1,114.838..1,119.458 rows=680 loops=1)

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

CTE u1s

67. 1.818 7.988 ↓ 1.1 2,230 1

HashAggregate (cost=6,393.30..6,413.21 rows=1,991 width=82) (actual time=7.393..7.988 rows=2,230 loops=1)

  • Group Key: useview_population_demographics.family_uuid, useview_population_demographics.uuid, useview_population_demographics.date_of_birth, 0
68. 6.170 6.170 ↓ 1.1 2,230 1

Index Scan using useview_population_demographics_reported_idx on useview_population_demographics (cost=0.45..6,373.39 rows=1,991 width=82) (actual time=0.050..6.170 rows=2,230 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))
  • Filter: ((date_part('years'::text, age(now(), (date_of_birth)::timestamp with time zone)))::integer <= 1)
  • Rows Removed by Filter: 2901
69. 1.045 1,115.486 ↑ 4.7 1,029 1

Sort (cost=365,336.35..365,348.43 rows=4,831 width=64) (actual time=1,114.823..1,115.486 rows=1,029 loops=1)

  • Sort Key: anc_pnc_u1fu.chw_uuid, anc_pnc_u1fu.interval_number
  • Sort Method: quicksort Memory: 193kB
70. 0.151 1,114.441 ↑ 4.7 1,029 1

Subquery Scan on anc_pnc_u1fu (cost=341,723.25..365,040.74 rows=4,831 width=64) (actual time=1,104.047..1,114.441 rows=1,029 loops=1)

71. 9.605 1,114.290 ↑ 4.7 1,029 1

GroupAggregate (cost=341,723.25..364,992.43 rows=4,831 width=100) (actual time=1,104.045..1,114.290 rows=1,029 loops=1)

  • Group Key: pmb.chw_uuid, pmb.baby_uuid, pmb.interval_number
72. 12.785 1,104.685 ↑ 151.2 8,776 1

Sort (cost=341,723.25..345,040.52 rows=1,326,907 width=183) (actual time=1,103.994..1,104.685 rows=8,776 loops=1)

  • Sort Key: pmb.chw_uuid, pmb.baby_uuid, pmb.interval_number
  • Sort Method: quicksort Memory: 2716kB
73. 2.275 1,091.900 ↑ 151.2 8,776 1

Hash Left Join (cost=87,670.39..137,712.67 rows=1,326,907 width=183) (actual time=959.977..1,091.900 rows=8,776 loops=1)

  • Hash Cond: (pmb.baby_uuid = u1fu.patient_id)
74. 2.930 1,079.494 ↑ 62.2 8,776 1

Hash Left Join (cost=86,450.92..117,086.99 rows=545,523 width=146) (actual time=949.743..1,079.494 rows=8,776 loops=1)

  • Hash Cond: (pmb.mother_uuid = pv.patient_id)
75. 32.941 987.360 ↑ 54.9 2,171 1

Merge Right Join (cost=77,848.72..89,477.05 rows=119,143 width=145) (actual time=860.382..987.360 rows=2,171 loops=1)

  • Merge Cond: (pc.patient_id = pmb.mother_uuid)
76. 94.058 94.058 ↑ 1.0 135,691 1

Index Scan using useview_postnatal_patient_id on useview_postnatal_care pc (cost=0.42..9,502.20 rows=135,760 width=73) (actual time=0.015..94.058 rows=135,691 loops=1)

77. 1.731 860.361 ↑ 22.3 2,171 1

Sort (cost=77,848.30..77,969.08 rows=48,312 width=108) (actual time=860.040..860.361 rows=2,171 loops=1)

  • Sort Key: pmb.mother_uuid
  • Sort Method: quicksort Memory: 322kB
78. 0.123 858.630 ↑ 47.0 1,029 1

Subquery Scan on pmb (cost=73,123.36..74,089.60 rows=48,312 width=108) (actual time=857.979..858.630 rows=1,029 loops=1)

79. 2.300 858.507 ↑ 47.0 1,029 1

HashAggregate (cost=73,123.36..73,606.48 rows=48,312 width=176) (actual time=857.977..858.507 rows=1,029 loops=1)

  • Group Key: pnc.chw, u1s.family_uuid, (string_agg(DISTINCT pd.uuid, ','::text)), pnc.patient_id, u1s.uuid, u1s.interval_number
80. 1.649 856.207 ↑ 22.3 2,171 1

Merge Join (cost=66,362.42..72,398.68 rows=48,312 width=172) (actual time=834.194..856.207 rows=2,171 loops=1)

  • Merge Cond: (u1s.family_uuid = pd.family_uuid)
81. 3.500 12.340 ↓ 1.1 2,230 1

Sort (cost=148.92..153.90 rows=1,991 width=68) (actual time=11.974..12.340 rows=2,230 loops=1)

  • Sort Key: u1s.family_uuid
  • Sort Method: quicksort Memory: 410kB
82. 8.840 8.840 ↓ 1.1 2,230 1

CTE Scan on u1s (cost=0.00..39.82 rows=1,991 width=68) (actual time=7.398..8.840 rows=2,230 loops=1)

83. 0.951 842.218 ↑ 2.2 2,171 1

Materialize (cost=66,213.50..71,527.26 rows=4,853 width=141) (actual time=822.199..842.218 rows=2,171 loops=1)

84. 1.653 841.267 ↑ 2.5 1,978 1

Nested Loop (cost=66,213.50..71,515.13 rows=4,853 width=141) (actual time=822.196..841.267 rows=1,978 loops=1)

85. 5.183 827.794 ↓ 1.0 1,970 1

GroupAggregate (cost=66,213.08..66,252.44 rows=1,968 width=69) (actual time=822.166..827.794 rows=1,970 loops=1)

  • Group Key: pd.family_uuid
86. 5.029 822.611 ↓ 1.8 3,467 1

Sort (cost=66,213.08..66,218.00 rows=1,968 width=74) (actual time=822.139..822.611 rows=3,467 loops=1)

  • Sort Key: pd.family_uuid
  • Sort Method: quicksort Memory: 584kB
87. 1.675 817.582 ↓ 1.8 3,467 1

Hash Join (cost=66,028.48..66,105.40 rows=1,968 width=74) (actual time=815.761..817.582 rows=3,467 loops=1)

  • Hash Cond: (u1s_1.family_uuid = pd.family_uuid)
88. 0.311 0.311 ↓ 1.1 2,230 1

CTE Scan on u1s u1s_1 (cost=0.00..39.82 rows=1,991 width=32) (actual time=0.001..0.311 rows=2,230 loops=1)

89. 120.239 815.596 ↓ 1.8 314,130 1

Hash (cost=63,815.50..63,815.50 rows=177,039 width=74) (actual time=815.596..815.596 rows=314,130 loops=1)

  • Buckets: 524288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 36614kB
90. 695.357 695.357 ↓ 1.8 314,130 1

Index Scan using useview_population_demographics_sex_date_of_birth_idx on useview_population_demographics pd (cost=0.42..63,815.50 rows=177,039 width=74) (actual time=0.031..695.357 rows=314,130 loops=1)

  • Index Cond: (sex = 'female'::text)
  • Filter: ((date_part('years'::text, age(now(), (date_of_birth)::timestamp with time zone)))::integer >= 15)
  • Rows Removed by Filter: 219593
91. 11.820 11.820 ↑ 2.0 1 1,970

Index Scan using useview_postnatal_patient_id on useview_postnatal_care pnc (cost=0.42..2.64 rows=2 width=72) (actual time=0.006..0.006 rows=1 loops=1,970)

  • Index Cond: (patient_id = (string_agg(DISTINCT pd.uuid, ','::text)))
92. 52.436 89.204 ↓ 1.0 177,936 1

Hash (cost=6,378.20..6,378.20 rows=177,920 width=73) (actual time=89.204..89.204 rows=177,936 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 20464kB
93. 36.768 36.768 ↓ 1.0 177,936 1

Seq Scan on useview_pregnancy_visit pv (cost=0.00..6,378.20 rows=177,920 width=73) (actual time=0.011..36.768 rows=177,936 loops=1)

94. 5.247 10.131 ↑ 1.0 17,710 1

Hash (cost=998.10..998.10 rows=17,710 width=74) (actual time=10.131..10.131 rows=17,710 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2090kB
95. 4.884 4.884 ↑ 1.0 17,710 1

Seq Scan on useview_u1_follow_up u1fu (cost=0.00..998.10 rows=17,710 width=74) (actual time=0.012..4.884 rows=17,710 loops=1)

96. 528.654 955.998 ↑ 2.8 429 1,842

GroupAggregate (cost=17,591.72..17,615.44 rows=1,182 width=48) (actual time=0.136..0.519 rows=429 loops=1,842)

  • Group Key: contactview_metadata.parent_uuid, 0
97. 187.219 427.344 ↑ 1.6 743 1,842

Sort (cost=17,591.72..17,594.69 rows=1,190 width=77) (actual time=0.131..0.232 rows=743 loops=1,842)

  • Sort Key: contactview_metadata.parent_uuid
  • Sort Method: quicksort Memory: 144kB
98. 240.125 240.125 ↑ 1.4 852 1

Index Scan using contactview_metadata_type on contactview_metadata (cost=0.43..17,530.93 rows=1,190 width=77) (actual time=0.158..240.125 rows=852 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: 238660
99. 326.911 327.876 ↑ 1.6 668 1,842

HashAggregate (cost=396.23..406.95 rows=1,072 width=56) (actual time=0.002..0.178 rows=668 loops=1,842)

  • Group Key: useview_pregnancy.chw, 0
100. 0.965 0.965 ↑ 1.0 1,398 1

Index Scan using useview_pregnancy_reported on useview_pregnancy (cost=0.32..366.89 rows=1,467 width=79) (actual time=0.027..0.965 rows=1,398 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))
101. 579.578 583.914 ↑ 1.9 693 1,842

HashAggregate (cost=1,804.72..1,817.85 rows=1,313 width=136) (actual time=0.006..0.317 rows=693 loops=1,842)

  • Group Key: pnc_1.chw, 0
102. 4.336 4.336 ↑ 1.4 1,482 1

Index Scan using useview_postnatal_reported on useview_postnatal_care pnc_1 (cost=0.45..1,559.96 rows=2,083 width=102) (actual time=0.058..4.336 rows=1,482 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))
  • Filter: (follow_up_count = '1'::text)
  • Rows Removed by Filter: 4315
103. 128.414 571.020 ↓ 1.6 324 1,842

HashAggregate (cost=34,010.23..34,012.23 rows=200 width=48) (actual time=0.242..0.310 rows=324 loops=1,842)

  • Group Key: p.chw, 0
104. 0.529 442.606 ↑ 216.6 434 1

Hash Left Join (cost=32,679.51..33,305.12 rows=94,014 width=76) (actual time=427.091..442.606 rows=434 loops=1)

  • Hash Cond: (p.patient_id = useview_postnatal_care.patient_id)
  • Filter: (((max(useview_postnatal_care.reported)) IS NULL) OR ((@ ((CASE WHEN (max(pv_1.new_edd) <> ''::text) THEN (max(pv_1.new_edd))::date ELSE max(p.edd) END) - ((max(useview_postnatal_care.reported)))::date)) > 60))
  • Rows Removed by Filter: 317
105. 21.343 363.411 ↑ 2.0 751 1

HashAggregate (cost=24,330.38..24,936.57 rows=1,525 width=76) (actual time=348.254..363.411 rows=751 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: 9750
106. 5.348 342.068 ↓ 1.3 11,506 1

Hash Anti Join (cost=20,996.74..24,147.38 rows=9,150 width=78) (actual time=308.813..342.068 rows=11,506 loops=1)

  • Hash Cond: (p.patient_id = upv.patient_id)
107. 19.655 287.734 ↓ 1.6 18,563 1

Hash Join (cost=15,927.82..18,954.64 rows=11,364 width=78) (actual time=259.766..287.734 rows=18,563 loops=1)

  • Hash Cond: (p.patient_id = pv_1.patient_id)
  • Join Filter: (p.reported < pv_1.reported)
  • Rows Removed by Join Filter: 47
108. 8.427 8.427 ↓ 1.0 60,660 1

Seq Scan on useview_pregnancy p (cost=0.00..2,867.59 rows=60,659 width=84) (actual time=0.007..8.427 rows=60,660 loops=1)

109. 4.157 259.652 ↑ 1.8 16,977 1

Hash (cost=15,539.13..15,539.13 rows=31,095 width=46) (actual time=259.652..259.652 rows=16,977 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1613kB
110. 2.324 255.495 ↑ 1.8 16,977 1

Subquery Scan on pv_1 (cost=14,964.39..15,539.13 rows=31,095 width=46) (actual time=239.208..255.495 rows=16,977 loops=1)

111. 7.264 253.171 ↑ 1.8 16,977 1

Unique (cost=14,964.39..15,228.18 rows=31,095 width=47) (actual time=239.188..253.171 rows=16,977 loops=1)

112. 124.875 245.907 ↑ 1.1 48,688 1

Sort (cost=14,964.39..15,096.28 rows=52,759 width=47) (actual time=239.185..245.907 rows=48,688 loops=1)

  • Sort Key: useview_pregnancy_visit.patient_id, useview_pregnancy_visit.reported DESC
  • Sort Method: quicksort Memory: 8342kB
113. 121.032 121.032 ↑ 1.1 48,688 1

Seq Scan on useview_pregnancy_visit (cost=0.00..10,826.20 rows=52,759 width=47) (actual time=42.677..121.032 rows=48,688 loops=1)

  • Filter: (((edd_updated IS TRUE) 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: 129248
114. 1.687 48.986 ↑ 1.6 6,783 1

Hash (cost=4,934.17..4,934.17 rows=10,780 width=36) (actual time=48.985..48.986 rows=6,783 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 586kB
115. 0.857 47.299 ↑ 1.6 6,783 1

Subquery Scan on upv (cost=4,765.42..4,934.17 rows=10,780 width=36) (actual time=44.021..47.299 rows=6,783 loops=1)

116. 1.794 46.442 ↑ 1.6 6,783 1

Unique (cost=4,765.42..4,826.37 rows=10,780 width=44) (actual time=44.012..46.442 rows=6,783 loops=1)

117. 16.104 44.648 ↑ 1.5 8,347 1

Sort (cost=4,765.42..4,795.90 rows=12,189 width=44) (actual time=44.011..44.648 rows=8,347 loops=1)

  • Sort Key: useview_pregnancy_visit_1.patient_id, useview_pregnancy_visit_1.reported DESC
  • Sort Method: quicksort Memory: 1036kB
118. 28.544 28.544 ↑ 1.5 8,347 1

Index Scan using useview_pregnancy_visit_continue_follow_up on useview_pregnancy_visit useview_pregnancy_visit_1 (cost=0.42..3,938.20 rows=12,189 width=44) (actual time=10.338..28.544 rows=8,347 loops=1)

  • Index Cond: (continue_follow_up = false)
  • Filter: ((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: 17455
119. 11.316 78.666 ↓ 1.2 43,086 1

Hash (cost=7,891.34..7,891.34 rows=36,623 width=44) (actual time=78.666..78.666 rows=43,086 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3879kB
120. 32.750 67.350 ↓ 1.2 43,086 1

HashAggregate (cost=7,158.88..7,525.11 rows=36,623 width=44) (actual time=57.260..67.350 rows=43,086 loops=1)

  • Group Key: useview_postnatal_care.patient_id
121. 34.600 34.600 ↑ 1.0 48,409 1

Index Scan using useview_postnatal_care_count_reported_uuid on useview_postnatal_care (cost=0.42..6,915.60 rows=48,656 width=44) (actual time=0.021..34.600 rows=48,409 loops=1)

  • Index Cond: (follow_up_count = '1'::text)
122. 1.194 79.925 ↑ 1.1 1,033 1

Materialize (cost=18,674.69..18,711.22 rows=1,124 width=48) (actual time=73.886..79.925 rows=1,033 loops=1)

123. 3.203 78.731 ↑ 1.1 1,033 1

GroupAggregate (cost=18,674.69..18,697.17 rows=1,124 width=48) (actual time=73.883..78.731 rows=1,033 loops=1)

  • Group Key: assess.chw, 0
124. 6.416 75.528 ↓ 3.7 4,151 1

Sort (cost=18,674.69..18,677.50 rows=1,124 width=77) (actual time=73.864..75.528 rows=4,151 loops=1)

  • Sort Key: assess.chw
  • Sort Method: quicksort Memory: 776kB
125. 3.221 69.112 ↓ 3.7 4,151 1

Nested Loop (cost=2,645.98..18,617.74 rows=1,124 width=77) (actual time=23.521..69.112 rows=4,151 loops=1)

126. 3.406 26.677 ↓ 1.2 5,602 1

Bitmap Heap Scan on useview_assessment assess (cost=2,645.56..7,799.87 rows=4,841 width=81) (actual time=23.481..26.677 rows=5,602 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=1591
127. 0.097 23.271 ↓ 0.0 0 1

BitmapAnd (cost=2,645.56..2,645.56 rows=4,841 width=0) (actual time=23.270..23.271 rows=0 loops=1)

128. 7.039 7.039 ↓ 1.1 29,891 1

Bitmap Index Scan on useview_assessment_reported_age_uuid (cost=0.00..802.00 rows=28,246 width=0) (actual time=7.039..7.039 rows=29,891 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))
129. 16.135 16.135 ↓ 1.0 175,711 1

Bitmap Index Scan on useview_assessment_referral_follow_up (cost=0.00..1,840.88 rows=174,848 width=0) (actual time=16.135..16.135 rows=175,711 loops=1)

  • Index Cond: (referral_follow_up = 'true'::text)
130. 39.214 39.214 ↑ 1.0 1 5,602

Index Scan using useview_assessment_follow_up_form_source_id on useview_assessment_follow_up follow_up (cost=0.42..2.22 rows=1 width=45) (actual time=0.007..0.007 rows=1 loops=5,602)

  • 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
131. 238.784 289.194 ↑ 3.5 506 1,842

HashAggregate (cost=4,193.43..4,211.02 rows=1,759 width=48) (actual time=0.029..0.157 rows=506 loops=1,842)

  • Group Key: meta.chw, 0
132. 50.410 50.410 ↑ 2.3 1,528 1

Index Scan using form_metadata_formname on form_metadata meta (cost=0.43..4,167.51 rows=3,455 width=76) (actual time=11.451..50.410 rows=1,528 loops=1)

  • Index Cond: (formname = 'community_event'::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: 50621
133. 43.172 2,558.538 ↑ 1.0 1 1,842

Index Only Scan using contactview_chp_uuid on contactview_chp chp (cost=0.28..3,011.69 rows=1 width=40) (actual time=1.389..1.389 rows=1 loops=1,842)

  • Index Cond: (uuid = chwlist.chw_uuid)
  • Heap Fetches: 1842
134.          

SubPlan (for Index Only Scan)

135. 5.139 2,096.196 ↑ 8.0 1 1,842

Nested Loop Semi Join (cost=0.56..2,080.50 rows=8 width=0) (actual time=1.138..1.138 rows=1 loops=1,842)

  • Join Filter: (meta_1.formname = "*VALUES*".column1)
  • Rows Removed by Join Filter: 2
136. 2,088.828 2,088.828 ↑ 113.0 1 1,842

Index Scan using form_metadata_chw on form_metadata meta_1 (cost=0.56..2,075.37 rows=113 width=15) (actual time=1.105..1.134 rows=1 loops=1,842)

  • Index Cond: (chw = chp.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: 606
137. 2.224 2.229 ↑ 1.5 2 2,229

Materialize (cost=0.00..0.05 rows=3 width=32) (actual time=0.001..0.001 rows=2 loops=2,229)

138. 0.005 0.005 ↑ 1.0 3 1

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

139. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.47..22,334.67 rows=18,104 width=36) (never executed)

140. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=0.05..0.08 rows=3 width=32) (never executed)

  • Group Key: "*VALUES*_1".column1
141. 0.000 0.000 ↓ 0.0 0

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

142. 0.000 0.000 ↓ 0.0 0

Index Scan using form_metadata_formname on form_metadata meta_2 (cost=0.43..7,384.51 rows=6,035 width=51) (never executed)

  • Index Cond: (formname = "*VALUES*_1".column1)
  • 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))
143. 0.251 53.463 ↓ 0.0 0 251

Nested Loop (cost=1.99..11.74 rows=12 width=0) (actual time=0.213..0.213 rows=0 loops=251)

144. 0.251 53.212 ↓ 0.0 0 251

Nested Loop (cost=1.71..9.21 rows=1 width=1,396) (actual time=0.212..0.212 rows=0 loops=251)

145. 0.502 52.961 ↓ 0.0 0 251

Nested Loop (cost=1.28..6.55 rows=1 width=37) (actual time=0.211..0.211 rows=0 loops=251)

  • Join Filter: (chw.parent_uuid = cmd_1.parent_uuid)
146. 1.506 7.028 ↑ 1.0 1 251

Nested Loop (cost=0.85..5.29 rows=1 width=110) (actual time=0.027..0.028 rows=1 loops=251)

147. 3.012 3.012 ↑ 1.0 1 251

Index Scan using contactview_metadata_uuid on contactview_metadata chw (cost=0.43..2.65 rows=1 width=73) (actual time=0.012..0.012 rows=1 loops=251)

  • Index Cond: (uuid = chp.uuid)
148. 2.510 2.510 ↑ 1.0 1 251

Index Only Scan using contactview_metadata_uuid on contactview_metadata chwarea (cost=0.43..2.65 rows=1 width=37) (actual time=0.010..0.010 rows=1 loops=251)

  • Index Cond: (uuid = chw.parent_uuid)
  • Heap Fetches: 251
149. 45.431 45.431 ↓ 0.0 0 251

Index Scan using contactview_metadata_parent_uuid on contactview_metadata cmd_1 (cost=0.43..1.24 rows=1 width=36) (actual time=0.181..0.181 rows=0 loops=251)

  • Index Cond: (parent_uuid = chwarea.uuid)
  • Filter: ((type = 'clinic'::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))
  • Rows Removed by Filter: 98
150. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_person_expr_idx8 on pat_person (cost=0.42..2.66 rows=1 width=1,396) (never executed)

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

Index Scan using pat_health_center_expr_idx8 on pat_health_center (cost=0.28..2.51 rows=1 width=957) (never executed)

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

Nested Loop (cost=1.99..47,494.02 rows=275,731 width=32) (never executed)

153. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.71..39,521.10 rows=22,472 width=1,396) (never executed)

154. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.28..21,594.56 rows=28,087 width=37) (never executed)

155. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.85..20,561.88 rows=1,190 width=73) (never executed)

156. 0.000 0.000 ↓ 0.0 0

Index Scan using contactview_metadata_type on contactview_metadata cmd_2 (cost=0.43..17,530.93 rows=1,190 width=36) (never executed)

  • 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))
157. 0.000 0.000 ↓ 0.0 0

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

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

Index Scan using contactview_metadata_parent_uuid on contactview_metadata chw_1 (cost=0.43..0.72 rows=15 width=73) (never executed)

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

Index Scan using pat_person_expr_idx8 on pat_person pat_person_1 (cost=0.42..0.63 rows=1 width=1,396) (never executed)

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

Index Scan using pat_health_center_expr_idx8 on pat_health_center pat_health_center_1 (cost=0.28..0.31 rows=1 width=957) (never executed)

  • Index Cond: ((doc ->> '_id'::text) = (pat_person_1.doc #>> '{parent,_id}'::text[]))
  • Filter: (((doc ->> 'type'::text) = 'health_center'::text) AND ((doc ->> 'type'::text) = ANY ('{contact,clinic,district_hospital,health_center,person}'::text[])))
161. 0.340 365.707 ↓ 0.0 0 251

Nested Loop (cost=1.53..2,745.85 rows=1 width=0) (actual time=1.457..1.457 rows=0 loops=251)

162. 0.450 353.910 ↑ 113.0 1 251

Nested Loop (cost=1.11..2,264.37 rows=113 width=72) (actual time=1.374..1.410 rows=1 loops=251)

163. 351.651 351.651 ↑ 113.0 1 251

Index Scan using form_metadata_chw on form_metadata meta_3 (cost=0.56..2,075.37 rows=113 width=36) (actual time=1.372..1.401 rows=1 loops=251)

  • Index Cond: (chw = chp.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: 1050
164. 1.809 1.809 ↑ 1.0 1 201

Index Only Scan using form_metadata_uuid on form_metadata fm (cost=0.56..1.67 rows=1 width=36) (actual time=0.009..0.009 rows=1 loops=201)

  • Index Cond: (uuid = meta_3.uuid)
  • Heap Fetches: 0
165. 0.956 11.457 ↓ 0.0 0 201

Append (cost=0.42..4.14 rows=12 width=38) (actual time=0.057..0.057 rows=0 loops=201)

166. 1.407 1.407 ↓ 0.0 0 201

Index Scan using pat_clinic_expr_idx8 on pat_clinic form_36 (cost=0.42..0.46 rows=1 width=1,030) (actual time=0.007..0.007 rows=0 loops=201)

  • Index Cond: ((doc ->> '_id'::text) = fm.uuid)
  • Filter: (((doc ->> 'form'::text) = 'postnatal_care'::text) AND ((doc #>> '{fields,follow_up_count}'::text[]) = '1'::text))
167. 1.608 1.608 ↓ 0.0 0 201

Index Scan using pat_form_postnatal_care_expr_idx8 on pat_form_postnatal_care form_37 (cost=0.42..0.45 rows=1 width=26) (actual time=0.008..0.008 rows=0 loops=201)

  • Index Cond: ((doc ->> '_id'::text) = fm.uuid)
  • Filter: (((doc ->> 'form'::text) = 'postnatal_care'::text) AND ((doc #>> '{fields,follow_up_count}'::text[]) = '1'::text))
  • Rows Removed by Filter: 0
168. 0.197 0.197 ↓ 0.0 0 197

Index Scan using pat_district_hospital_expr_idx8 on pat_district_hospital form_38 (cost=0.14..0.16 rows=1 width=432) (actual time=0.001..0.001 rows=0 loops=197)

  • Index Cond: ((doc ->> '_id'::text) = fm.uuid)
  • Filter: (((doc ->> 'form'::text) = 'postnatal_care'::text) AND ((doc #>> '{fields,follow_up_count}'::text[]) = '1'::text))
169. 1.182 1.182 ↓ 0.0 0 197

Index Scan using pat_feedback_expr_idx8 on pat_feedback form_39 (cost=0.41..0.44 rows=1 width=655) (actual time=0.006..0.006 rows=0 loops=197)

  • Index Cond: ((doc ->> '_id'::text) = fm.uuid)
  • Filter: (((doc ->> 'form'::text) = 'postnatal_care'::text) AND ((doc #>> '{fields,follow_up_count}'::text[]) = '1'::text))
170. 0.394 0.394 ↓ 0.0 0 197

Index Scan using pat_form_expr_idx8 on pat_form form_40 (cost=0.14..0.17 rows=1 width=638) (actual time=0.002..0.002 rows=0 loops=197)

  • Index Cond: ((doc ->> '_id'::text) = fm.uuid)
  • Filter: (((doc ->> 'form'::text) = 'postnatal_care'::text) AND ((doc #>> '{fields,follow_up_count}'::text[]) = '1'::text))
171. 0.788 0.788 ↓ 0.0 0 197

Index Scan using pat_health_center_expr_idx8 on pat_health_center form_41 (cost=0.28..0.31 rows=1 width=957) (actual time=0.004..0.004 rows=0 loops=197)

  • Index Cond: ((doc ->> '_id'::text) = fm.uuid)
  • Filter: (((doc ->> 'form'::text) = 'postnatal_care'::text) AND ((doc #>> '{fields,follow_up_count}'::text[]) = '1'::text))
172. 1.773 1.773 ↓ 0.0 0 197

Index Scan using pat_info_expr_idx8 on pat_info form_42 (cost=0.56..0.66 rows=1 width=297) (actual time=0.009..0.009 rows=0 loops=197)

  • Index Cond: ((doc ->> '_id'::text) = fm.uuid)
  • Filter: (((doc ->> 'form'::text) = 'postnatal_care'::text) AND ((doc #>> '{fields,follow_up_count}'::text[]) = '1'::text))
173. 0.197 0.197 ↓ 0.0 0 197

Index Scan using pat_meta_expr_idx8 on pat_meta form_43 (cost=0.12..0.15 rows=1 width=849) (actual time=0.001..0.001 rows=0 loops=197)

  • Index Cond: ((doc ->> '_id'::text) = fm.uuid)
  • Filter: (((doc ->> 'form'::text) = 'postnatal_care'::text) AND ((doc #>> '{fields,follow_up_count}'::text[]) = '1'::text))
174. 1.379 1.379 ↓ 0.0 0 197

Index Scan using pat_person_expr_idx8 on pat_person form_44 (cost=0.42..0.50 rows=1 width=1,396) (actual time=0.007..0.007 rows=0 loops=197)

  • Index Cond: ((doc ->> '_id'::text) = fm.uuid)
  • Filter: (((doc ->> 'form'::text) = 'postnatal_care'::text) AND ((doc #>> '{fields,follow_up_count}'::text[]) = '1'::text))
175. 0.591 0.591 ↓ 0.0 0 197

Index Scan using pat_traffic_stats_expr_idx8 on pat_traffic_stats form_45 (cost=0.28..0.30 rows=1 width=828) (actual time=0.003..0.003 rows=0 loops=197)

  • Index Cond: ((doc ->> '_id'::text) = fm.uuid)
  • Filter: (((doc ->> 'form'::text) = 'postnatal_care'::text) AND ((doc #>> '{fields,follow_up_count}'::text[]) = '1'::text))
176. 0.197 0.197 ↓ 0.0 0 197

Index Scan using pat_usage_stats_expr_idx8 on pat_usage_stats form_46 (cost=0.14..0.17 rows=1 width=1,075) (actual time=0.001..0.001 rows=0 loops=197)

  • Index Cond: ((doc ->> '_id'::text) = fm.uuid)
  • Filter: (((doc ->> 'form'::text) = 'postnatal_care'::text) AND ((doc #>> '{fields,follow_up_count}'::text[]) = '1'::text))
177. 0.788 0.788 ↓ 0.0 0 197

Index Scan using couchdb_default_expr_idx8 on couchdb_default form_47 (cost=0.28..0.31 rows=1 width=698) (actual time=0.004..0.004 rows=0 loops=197)

  • Index Cond: ((doc ->> '_id'::text) = fm.uuid)
  • Filter: (((doc ->> 'form'::text) = 'postnatal_care'::text) AND ((doc #>> '{fields,follow_up_count}'::text[]) = '1'::text))
178. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.53..3,633.95 rows=43 width=36) (never executed)

179. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.98..3,195.25 rows=690 width=74) (never executed)

180. 0.000 0.000 ↓ 0.0 0

Append (cost=0.42..2,048.30 rows=690 width=38) (never executed)

181. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_clinic_expr_idx9 on pat_clinic form_48 (cost=0.42..2.64 rows=1 width=1,030) (never executed)

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

Index Scan using pat_form_postnatal_care_expr_expr1_idx1 on pat_form_postnatal_care form_49 (cost=0.42..2,019.86 rows=679 width=26) (never executed)

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

Seq Scan on pat_district_hospital form_50 (cost=0.00..1.28 rows=1 width=432) (never executed)

  • Filter: (((doc ->> 'form'::text) = 'postnatal_care'::text) AND ((doc #>> '{fields,follow_up_count}'::text[]) = '1'::text))
184. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_feedback_expr_idx9 on pat_feedback form_51 (cost=0.29..2.51 rows=1 width=655) (never executed)

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

Index Scan using pat_form_expr_idx9 on pat_form form_52 (cost=0.14..2.36 rows=1 width=638) (never executed)

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

Index Scan using pat_health_center_expr_idx9 on pat_health_center form_53 (cost=0.28..2.50 rows=1 width=957) (never executed)

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

Index Scan using pat_info_expr_idx9 on pat_info form_54 (cost=0.43..2.66 rows=1 width=297) (never executed)

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

Seq Scan on pat_meta form_55 (cost=0.00..1.02 rows=1 width=849) (never executed)

  • Filter: (((doc ->> 'form'::text) = 'postnatal_care'::text) AND ((doc #>> '{fields,follow_up_count}'::text[]) = '1'::text))
189. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_person_expr_idx9 on pat_person form_56 (cost=0.42..2.65 rows=1 width=1,396) (never executed)

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

Index Scan using pat_traffic_stats_expr_idx9 on pat_traffic_stats form_57 (cost=0.28..2.50 rows=1 width=828) (never executed)

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

Index Scan using pat_usage_stats_expr_idx9 on pat_usage_stats form_58 (cost=0.14..2.36 rows=1 width=1,075) (never executed)

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

Index Scan using couchdb_default_expr_idx9 on couchdb_default form_59 (cost=0.28..2.51 rows=1 width=698) (never executed)

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

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

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

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

  • Index Cond: (uuid = fm_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))
195. 73,902.882 82,322.664 ↑ 1.3 1,651 1,842

GroupAggregate (cost=34,745.16..35,364.35 rows=2,085 width=48) (actual time=0.060..44.692 rows=1,651 loops=1,842)

  • Group Key: useview_households_visited.chw, 0
196. 8,376.238 8,419.782 ↑ 1.0 58,985 1,842

Sort (cost=34,745.16..34,894.74 rows=59,834 width=76) (actual time=0.047..4.571 rows=58,985 loops=1,842)

  • Sort Key: useview_households_visited.chw
  • Sort Method: quicksort Memory: 9831kB
197. 37.163 43.544 ↑ 1.0 58,985 1

Bitmap Heap Scan on useview_households_visited (cost=1,459.78..29,997.72 rows=59,834 width=76) (actual time=9.464..43.544 rows=58,985 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))
  • Heap Blocks: exact=17795
198. 6.381 6.381 ↑ 1.0 58,985 1

Bitmap Index Scan on households_date_visited_chw_hh (cost=0.00..1,444.82 rows=59,834 width=0) (actual time=6.381..6.381 rows=58,985 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))
199. 1,130.003 1,134.672 ↑ 1.6 1,245 1,842

HashAggregate (cost=8,448.66..8,469.07 rows=2,041 width=152) (actual time=0.016..0.616 rows=1,245 loops=1,842)

  • Group Key: population.chp_area_uuid, 0
200. 4.669 4.669 ↑ 1.2 5,131 1

Index Scan using useview_population_demographics_reported_idx on useview_population_demographics population (cost=0.45..6,283.81 rows=5,972 width=87) (actual time=0.055..4.669 rows=5,131 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))
201. 1.513 59.029 ↑ 1.9 1,089 1

Materialize (cost=8,486.49..8,839.86 rows=2,111 width=64) (actual time=23.314..59.029 rows=1,089 loops=1)

202. 30.724 57.516 ↑ 1.9 1,089 1

GroupAggregate (cost=8,486.49..8,813.47 rows=2,111 width=64) (actual time=23.311..57.516 rows=1,089 loops=1)

  • Group Key: survey.chw, 0
203. 16.788 26.792 ↑ 1.0 13,016 1

Sort (cost=8,486.49..8,520.48 rows=13,594 width=88) (actual time=23.271..26.792 rows=13,016 loops=1)

  • Sort Key: survey.chw
  • Sort Method: quicksort Memory: 2215kB
204. 10.004 10.004 ↑ 1.0 13,016 1

Index Scan using useview_family_survey_uuid_reported_chw on useview_family_survey survey (cost=0.45..7,553.22 rows=13,594 width=88) (actual time=0.028..10.004 rows=13,016 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))
205. 1,115.822 1,180.722 ↑ 71.4 1,123 1,842

HashAggregate (cost=11,362.68..12,164.07 rows=80,139 width=48) (actual time=0.070..0.641 rows=1,123 loops=1,842)

  • Group Key: contactview_metadata_1.parent_uuid, 0
206. 64.900 64.900 ↑ 1.0 239,512 1

Index Scan using contactview_metadata_type on contactview_metadata contactview_metadata_1 (cost=0.43..9,512.20 rows=246,730 width=40) (actual time=0.028..64.900 rows=239,512 loops=1)

  • Index Cond: (type = 'clinic'::text)
207. 167.319 2,208.558 ↑ 1.7 1,356 1,842

Materialize (cost=169,219.84..169,300.16 rows=2,295 width=48) (actual time=1.108..1.199 rows=1,356 loops=1,842)

208. 0.343 2,041.239 ↓ 1.2 2,708 1

Subquery Scan on sync (cost=169,219.84..169,288.69 rows=2,295 width=48) (actual time=2,039.287..2,041.239 rows=2,708 loops=1)

209. 1,302.262 2,040.896 ↓ 1.2 2,708 1

HashAggregate (cost=169,219.84..169,265.74 rows=2,295 width=48) (actual time=2,039.285..2,040.896 rows=2,708 loops=1)

  • Group Key: useview_chp_sync.chw_uuid, 0
210. 738.634 738.634 ↑ 1.0 5,332,505 1

Seq Scan on useview_chp_sync (cost=0.00..129,226.05 rows=5,332,505 width=48) (actual time=0.013..738.634 rows=5,332,505 loops=1)

211. 137.113 559.968 ↑ 2.0 1,110 1,842

Materialize (cost=60,538.51..60,593.04 rows=2,181 width=48) (actual time=0.230..0.304 rows=1,110 loops=1,842)

212. 0.265 422.855 ↓ 1.0 2,222 1

Subquery Scan on population_all_time (cost=60,538.51..60,582.13 rows=2,181 width=48) (actual time=421.993..422.855 rows=2,222 loops=1)

213. 257.484 422.590 ↓ 1.0 2,222 1

HashAggregate (cost=60,538.51..60,560.32 rows=2,181 width=48) (actual time=421.991..422.590 rows=2,222 loops=1)

  • Group Key: population_1.chp_area_uuid, 0
214. 165.106 165.106 ↑ 1.0 977,458 1

Seq Scan on useview_population_demographics population_1 (cost=0.00..53,207.58 rows=977,458 width=77) (actual time=0.011..165.106 rows=977,458 loops=1)

215. 410.766 410.766 ↓ 6.6 1,312 1,842

CTE Scan on fp (cost=0.00..4.00 rows=200 width=52) (actual time=0.072..0.223 rows=1,312 loops=1,842)

216. 1.918 363.268 ↓ 8.2 1,630 1

Materialize (cost=18,167.25..18,187.25 rows=200 width=80) (actual time=268.862..363.268 rows=1,630 loops=1)

217. 80.277 361.350 ↓ 8.2 1,630 1

GroupAggregate (cost=18,167.25..18,184.75 rows=200 width=80) (actual time=268.858..361.350 rows=1,630 loops=1)

  • Group Key: assess_u1fu_pnc_aggregated.chw, 0
218. 62.928 281.073 ↓ 184.3 36,861 1

Sort (cost=18,167.25..18,167.75 rows=200 width=96) (actual time=268.835..281.073 rows=36,861 loops=1)

  • Sort Key: assess_u1fu_pnc_aggregated.chw
  • Sort Method: quicksort Memory: 7450kB
219. 5.813 218.145 ↓ 184.3 36,861 1

Subquery Scan on assess_u1fu_pnc_aggregated (cost=17,968.82..18,159.60 rows=200 width=96) (actual time=197.650..218.145 rows=36,861 loops=1)

220. 8.976 212.332 ↓ 184.3 36,861 1

Unique (cost=17,968.82..18,157.60 rows=200 width=100) (actual time=197.638..212.332 rows=36,861 loops=1)

221. 100.034 203.356 ↓ 1.1 40,031 1

Sort (cost=17,968.82..18,063.21 rows=37,756 width=100) (actual time=197.637..203.356 rows=40,031 loops=1)

  • Sort Key: "*SELECT* 1".patient_id
  • Sort Method: quicksort Memory: 7910kB
222. 5.526 103.322 ↓ 1.1 40,031 1

Result (cost=0.45..15,098.53 rows=37,756 width=100) (actual time=0.046..103.322 rows=40,031 loops=1)

223. 4.278 97.796 ↓ 1.1 40,031 1

Append (cost=0.45..14,720.97 rows=37,756 width=92) (actual time=0.045..97.796 rows=40,031 loops=1)

224. 4.295 42.483 ↑ 1.0 33,495 1

Subquery Scan on *SELECT* 1 (cost=0.45..10,954.85 rows=35,084 width=86) (actual time=0.044..42.483 rows=33,495 loops=1)

225. 38.188 38.188 ↑ 1.0 33,495 1

Index Scan using useview_assessment_reported on useview_assessment (cost=0.45..10,604.01 rows=35,084 width=94) (actual time=0.042..38.188 rows=33,495 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))
226. 0.108 2.892 ↓ 1.0 740 1

Subquery Scan on *SELECT* 2 (cost=0.32..472.37 rows=735 width=153) (actual time=0.074..2.892 rows=740 loops=1)

227. 2.784 2.784 ↓ 1.0 740 1

Index Scan using useview_u1_follow_up_reported_chw_uuid on useview_u1_follow_up (cost=0.32..465.02 rows=735 width=161) (actual time=0.068..2.784 rows=740 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))
228. 0.936 48.143 ↓ 3.0 5,796 1

Subquery Scan on *SELECT* 3 (cost=0.45..3,104.97 rows=1,937 width=177) (actual time=0.157..48.143 rows=5,796 loops=1)

229. 47.207 47.207 ↓ 3.0 5,796 1

Index Scan using useview_postnatal_reported on useview_postnatal_care useview_postnatal_care_1 (cost=0.45..3,085.60 rows=1,937 width=185) (actual time=0.155..47.207 rows=5,796 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))
  • Filter: is_date((date_of_birth)::character varying)
  • Rows Removed by Filter: 1
230. 0.064 14.338 ↓ 1.2 59 1

Materialize (cost=251.08..254.44 rows=51 width=56) (actual time=10.819..14.338 rows=59 loops=1)

231. 3.455 14.274 ↓ 1.2 59 1

GroupAggregate (cost=251.08..253.80 rows=51 width=56) (actual time=10.817..14.274 rows=59 loops=1)

  • Group Key: dr.chw, 0
232. 0.192 10.819 ↓ 1.8 92 1

Sort (cost=251.08..251.21 rows=52 width=1,622) (actual time=10.769..10.819 rows=92 loops=1)

  • Sort Key: dr.chw
  • Sort Method: quicksort Memory: 210kB
233. 4.930 10.627 ↓ 1.8 92 1

Hash Right Join (cost=65.55..249.60 rows=52 width=1,622) (actual time=4.525..10.627 rows=92 loops=1)

  • Hash Cond: ((form.doc ->> '_id'::text) = dr.uuid)
234. 0.070 4.917 ↑ 1.0 638 1

Append (cost=0.42..182.68 rows=649 width=1,582) (actual time=0.030..4.917 rows=638 loops=1)

235. 0.007 0.007 ↓ 0.0 0 1

Index Scan using pat_clinic_expr_idx9 on pat_clinic form (cost=0.42..2.64 rows=1 width=1,030) (actual time=0.006..0.007 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
236. 4.779 4.779 ↑ 1.0 638 1

Seq Scan on pat_form_death_report form_1 (cost=0.00..154.57 rows=638 width=1,595) (actual time=0.023..4.779 rows=638 loops=1)

  • Filter: ((doc ->> 'form'::text) = 'death_report'::text)
237. 0.021 0.021 ↓ 0.0 0 1

Seq Scan on pat_district_hospital form_2 (cost=0.00..1.21 rows=1 width=432) (actual time=0.021..0.021 rows=0 loops=1)

  • Filter: ((doc ->> 'form'::text) = 'death_report'::text)
  • Rows Removed by Filter: 14
238. 0.006 0.006 ↓ 0.0 0 1

Index Scan using pat_feedback_expr_idx9 on pat_feedback form_3 (cost=0.29..2.51 rows=1 width=655) (actual time=0.005..0.006 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
239. 0.004 0.004 ↓ 0.0 0 1

Index Scan using pat_form_expr_idx9 on pat_form form_4 (cost=0.14..2.36 rows=1 width=638) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
240. 0.004 0.004 ↓ 0.0 0 1

Index Scan using pat_health_center_expr_idx9 on pat_health_center form_5 (cost=0.28..2.50 rows=1 width=957) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
241. 0.005 0.005 ↓ 0.0 0 1

Index Scan using pat_info_expr_idx9 on pat_info form_6 (cost=0.43..2.65 rows=1 width=297) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
242. 0.007 0.007 ↓ 0.0 0 1

Seq Scan on pat_meta form_7 (cost=0.00..1.01 rows=1 width=849) (actual time=0.007..0.007 rows=0 loops=1)

  • Filter: ((doc ->> 'form'::text) = 'death_report'::text)
  • Rows Removed by Filter: 1
243. 0.005 0.005 ↓ 0.0 0 1

Index Scan using pat_person_expr_idx9 on pat_person form_8 (cost=0.42..2.64 rows=1 width=1,396) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
244. 0.003 0.003 ↓ 0.0 0 1

Index Scan using pat_traffic_stats_expr_idx9 on pat_traffic_stats form_9 (cost=0.28..2.49 rows=1 width=828) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
245. 0.002 0.002 ↓ 0.0 0 1

Index Scan using pat_usage_stats_expr_idx9 on pat_usage_stats form_10 (cost=0.14..2.36 rows=1 width=1,075) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
246. 0.004 0.004 ↓ 0.0 0 1

Index Scan using couchdb_default_expr_idx9 on couchdb_default form_11 (cost=0.28..2.50 rows=1 width=698) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
247. 0.027 0.780 ↓ 1.8 92 1

Hash (cost=64.48..64.48 rows=52 width=72) (actual time=0.780..0.780 rows=92 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
248. 0.753 0.753 ↓ 1.8 92 1

Index Scan using form_metadata_formname on form_metadata dr (cost=0.43..64.48 rows=52 width=72) (actual time=0.299..0.753 rows=92 loops=1)

  • Index Cond: (formname = 'death_report'::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: 546
249. 0.004 103.906 ↑ 2.0 1 1

Materialize (cost=8,180.59..8,180.66 rows=2 width=48) (actual time=103.905..103.906 rows=1 loops=1)

250. 0.009 103.902 ↑ 2.0 1 1

GroupAggregate (cost=8,180.59..8,180.63 rows=2 width=48) (actual time=103.902..103.902 rows=1 loops=1)

  • Group Key: "*SELECT* 1_1".chw_uuid, 0
251. 0.007 103.893 ↑ 1.0 2 1

Sort (cost=8,180.59..8,180.60 rows=2 width=40) (actual time=103.893..103.893 rows=2 loops=1)

  • Sort Key: "*SELECT* 1_1".chw_uuid
  • Sort Method: quicksort Memory: 25kB
252. 0.004 103.886 ↑ 1.0 2 1

Result (cost=7,944.61..8,180.58 rows=2 width=40) (actual time=81.012..103.886 rows=2 loops=1)

253. 0.002 103.882 ↑ 1.0 2 1

Append (cost=7,944.61..8,180.56 rows=2 width=36) (actual time=81.009..103.882 rows=2 loops=1)

254. 0.042 81.018 ↑ 1.0 1 1

Subquery Scan on *SELECT* 1_1 (cost=7,944.61..7,944.84 rows=1 width=36) (actual time=81.008..81.018 rows=1 loops=1)

  • Filter: (("*SELECT* 1_1".reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 mon'::interval)))::timestamp without time zone) AND ("*SELECT* 1_1".reported < ((date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 17
255. 0.015 80.976 ↓ 3.6 18 1

Unique (cost=7,944.61..7,944.63 rows=5 width=80) (actual time=80.958..80.976 rows=18 loops=1)

256. 0.054 80.961 ↓ 11.6 58 1

Sort (cost=7,944.61..7,944.62 rows=5 width=80) (actual time=80.956..80.961 rows=58 loops=1)

  • Sort Key: pv_2.patient_id
  • Sort Method: quicksort Memory: 33kB
257. 27.395 80.907 ↓ 11.6 58 1

Hash Join (cost=232.34..7,944.55 rows=5 width=80) (actual time=19.912..80.907 rows=58 loops=1)

  • Hash Cond: (pv_2.patient_id = (form_12.doc #>> '{fields,inputs,contact,_id}'::text[]))
258. 34.704 34.704 ↓ 1.0 177,846 1

Seq Scan on useview_pregnancy_visit pv_2 (cost=0.00..6,823.00 rows=177,831 width=36) (actual time=0.008..34.704 rows=177,846 loops=1)

  • Filter: (patient_id <> ''::text)
  • Rows Removed by Filter: 90
259. 4.723 18.808 ↓ 638.0 638 1

Hash (cost=232.33..232.33 rows=1 width=1,626) (actual time=18.808..18.808 rows=638 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1052kB
260. 8.810 14.085 ↓ 638.0 638 1

Hash Join (cost=48.28..232.33 rows=1 width=1,626) (actual time=0.394..14.085 rows=638 loops=1)

  • Hash Cond: ((form_12.doc ->> '_id'::text) = dr_1.uuid)
261. 0.074 4.929 ↑ 1.0 638 1

Append (cost=0.42..182.68 rows=649 width=1,582) (actual time=0.020..4.929 rows=638 loops=1)

262. 0.006 0.006 ↓ 0.0 0 1

Index Scan using pat_clinic_expr_idx9 on pat_clinic form_12 (cost=0.42..2.64 rows=1 width=1,030) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
263. 4.801 4.801 ↑ 1.0 638 1

Seq Scan on pat_form_death_report form_13 (cost=0.00..154.57 rows=638 width=1,595) (actual time=0.012..4.801 rows=638 loops=1)

  • Filter: ((doc ->> 'form'::text) = 'death_report'::text)
264. 0.011 0.011 ↓ 0.0 0 1

Seq Scan on pat_district_hospital form_14 (cost=0.00..1.21 rows=1 width=432) (actual time=0.011..0.011 rows=0 loops=1)

  • Filter: ((doc ->> 'form'::text) = 'death_report'::text)
  • Rows Removed by Filter: 14
265. 0.009 0.009 ↓ 0.0 0 1

Index Scan using pat_feedback_expr_idx9 on pat_feedback form_15 (cost=0.29..2.51 rows=1 width=655) (actual time=0.009..0.009 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
266. 0.002 0.002 ↓ 0.0 0 1

Index Scan using pat_form_expr_idx9 on pat_form form_16 (cost=0.14..2.36 rows=1 width=638) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
267. 0.003 0.003 ↓ 0.0 0 1

Index Scan using pat_health_center_expr_idx9 on pat_health_center form_17 (cost=0.28..2.50 rows=1 width=957) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
268. 0.004 0.004 ↓ 0.0 0 1

Index Scan using pat_info_expr_idx9 on pat_info form_18 (cost=0.43..2.65 rows=1 width=297) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
269. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on pat_meta form_19 (cost=0.00..1.01 rows=1 width=849) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: ((doc ->> 'form'::text) = 'death_report'::text)
  • Rows Removed by Filter: 1
270. 0.004 0.004 ↓ 0.0 0 1

Index Scan using pat_person_expr_idx9 on pat_person form_20 (cost=0.42..2.64 rows=1 width=1,396) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
271. 0.002 0.002 ↓ 0.0 0 1

Index Scan using pat_traffic_stats_expr_idx9 on pat_traffic_stats form_21 (cost=0.28..2.49 rows=1 width=828) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
272. 0.005 0.005 ↓ 0.0 0 1

Index Scan using pat_usage_stats_expr_idx9 on pat_usage_stats form_22 (cost=0.14..2.36 rows=1 width=1,075) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
273. 0.003 0.003 ↓ 0.0 0 1

Index Scan using couchdb_default_expr_idx9 on couchdb_default form_23 (cost=0.28..2.50 rows=1 width=698) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
274. 0.157 0.346 ↑ 1.3 638 1

Hash (cost=37.48..37.48 rows=831 width=80) (actual time=0.346..0.346 rows=638 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 81kB
275. 0.189 0.189 ↑ 1.3 638 1

Index Scan using form_metadata_formname on form_metadata dr_1 (cost=0.43..37.48 rows=831 width=80) (actual time=0.016..0.189 rows=638 loops=1)

  • Index Cond: (formname = 'death_report'::text)
276. 0.024 22.862 ↑ 1.0 1 1

Subquery Scan on *SELECT* 2_1 (cost=235.66..235.71 rows=1 width=36) (actual time=22.855..22.862 rows=1 loops=1)

  • Filter: (("*SELECT* 2_1".reported >= (date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 mon'::interval)))::timestamp without time zone) AND ("*SELECT* 2_1".reported < ((date_trunc('day'::text, (date_trunc('month'::text, now()) - '1 day'::interval)) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 15
277. 0.011 22.838 ↓ 16.0 16 1

Unique (cost=235.66..235.66 rows=1 width=80) (actual time=22.825..22.838 rows=16 loops=1)

278. 0.042 22.827 ↓ 40.0 40 1

Sort (cost=235.66..235.66 rows=1 width=80) (actual time=22.824..22.827 rows=40 loops=1)

  • Sort Key: pc_1.patient_id
  • Sort Method: quicksort Memory: 30kB
279. 4.635 22.785 ↓ 40.0 40 1

Nested Loop (cost=48.71..235.65 rows=1 width=80) (actual time=3.142..22.785 rows=40 loops=1)

280. 8.753 14.322 ↓ 638.0 638 1

Hash Join (cost=48.28..232.33 rows=1 width=1,626) (actual time=0.475..14.322 rows=638 loops=1)

  • Hash Cond: ((form_24.doc ->> '_id'::text) = dr_2.uuid)
281. 0.078 5.164 ↑ 1.0 638 1

Append (cost=0.42..182.68 rows=649 width=1,582) (actual time=0.038..5.164 rows=638 loops=1)

282. 0.013 0.013 ↓ 0.0 0 1

Index Scan using pat_clinic_expr_idx9 on pat_clinic form_24 (cost=0.42..2.64 rows=1 width=1,030) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
283. 4.998 4.998 ↑ 1.0 638 1

Seq Scan on pat_form_death_report form_25 (cost=0.00..154.57 rows=638 width=1,595) (actual time=0.025..4.998 rows=638 loops=1)

  • Filter: ((doc ->> 'form'::text) = 'death_report'::text)
284. 0.033 0.033 ↓ 0.0 0 1

Seq Scan on pat_district_hospital form_26 (cost=0.00..1.21 rows=1 width=432) (actual time=0.032..0.033 rows=0 loops=1)

  • Filter: ((doc ->> 'form'::text) = 'death_report'::text)
  • Rows Removed by Filter: 14
285. 0.004 0.004 ↓ 0.0 0 1

Index Scan using pat_feedback_expr_idx9 on pat_feedback form_27 (cost=0.29..2.51 rows=1 width=655) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
286. 0.002 0.002 ↓ 0.0 0 1

Index Scan using pat_form_expr_idx9 on pat_form form_28 (cost=0.14..2.36 rows=1 width=638) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
287. 0.005 0.005 ↓ 0.0 0 1

Index Scan using pat_health_center_expr_idx9 on pat_health_center form_29 (cost=0.28..2.50 rows=1 width=957) (actual time=0.004..0.005 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
288. 0.005 0.005 ↓ 0.0 0 1

Index Scan using pat_info_expr_idx9 on pat_info form_30 (cost=0.43..2.65 rows=1 width=297) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
289. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on pat_meta form_31 (cost=0.00..1.01 rows=1 width=849) (actual time=0.006..0.006 rows=0 loops=1)

  • Filter: ((doc ->> 'form'::text) = 'death_report'::text)
  • Rows Removed by Filter: 1
290. 0.011 0.011 ↓ 0.0 0 1

Index Scan using pat_person_expr_idx9 on pat_person form_32 (cost=0.42..2.64 rows=1 width=1,396) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
291. 0.003 0.003 ↓ 0.0 0 1

Index Scan using pat_traffic_stats_expr_idx9 on pat_traffic_stats form_33 (cost=0.28..2.49 rows=1 width=828) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
292. 0.003 0.003 ↓ 0.0 0 1

Index Scan using pat_usage_stats_expr_idx9 on pat_usage_stats form_34 (cost=0.14..2.36 rows=1 width=1,075) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
293. 0.003 0.003 ↓ 0.0 0 1

Index Scan using couchdb_default_expr_idx9 on couchdb_default form_35 (cost=0.28..2.50 rows=1 width=698) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = 'death_report'::text)
294. 0.179 0.405 ↑ 1.3 638 1

Hash (cost=37.48..37.48 rows=831 width=80) (actual time=0.405..0.405 rows=638 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 81kB
295. 0.226 0.226 ↑ 1.3 638 1

Index Scan using form_metadata_formname on form_metadata dr_2 (cost=0.43..37.48 rows=831 width=80) (actual time=0.020..0.226 rows=638 loops=1)

  • Index Cond: (formname = 'death_report'::text)
296. 3.828 3.828 ↓ 0.0 0 638

Index Scan using useview_postnatal_patient_id on useview_postnatal_care pc_1 (cost=0.42..3.31 rows=1 width=36) (actual time=0.006..0.006 rows=0 loops=638)

  • Index Cond: (patient_id = (form_24.doc #>> '{fields,inputs,contact,_id}'::text[]))
  • Filter: ((patient_id <> ''::text) AND (date_part('year'::text, age((CURRENT_DATE)::timestamp with time zone, ((date_of_birth)::date)::timestamp with time zone)) >= '12'::double precision))
  • Rows Removed by Filter: 0
297. 1.491 540.855 ↑ 1.1 1,638 1

Materialize (cost=39,801.92..41,315.88 rows=1,777 width=48) (actual time=347.597..540.855 rows=1,638 loops=1)

298. 154.664 539.364 ↑ 1.1 1,638 1

GroupAggregate (cost=39,801.92..41,293.67 rows=1,777 width=48) (actual time=347.594..539.364 rows=1,638 loops=1)

  • Group Key: useview_home_visit.reported_by, 0
299. 258.316 384.700 ↑ 1.0 146,223 1

Sort (cost=39,801.92..40,170.41 rows=147,398 width=77) (actual time=347.527..384.700 rows=146,223 loops=1)

  • Sort Key: useview_home_visit.reported_by
  • Sort Method: quicksort Memory: 26707kB
300. 126.384 126.384 ↑ 1.0 146,223 1

Index Scan using useview_home_visit_reported_idx on useview_home_visit (cost=0.46..27,148.27 rows=147,398 width=77) (actual time=0.064..126.384 rows=146,223 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))
301. 0.125 13.648 ↑ 1.9 106 1

Materialize (cost=2,136.34..2,850.33 rows=200 width=97) (actual time=11.523..13.648 rows=106 loops=1)

302. 1.653 13.523 ↑ 1.9 106 1

GroupAggregate (cost=2,136.34..2,847.83 rows=200 width=97) (actual time=11.519..13.523 rows=106 loops=1)

  • Group Key: "*SELECT* 1_2".reported_by, 0
303. 1.476 11.870 ↑ 11.6 1,435 1

Sort (cost=2,136.34..2,178.07 rows=16,694 width=178) (actual time=11.481..11.870 rows=1,435 loops=1)

  • Sort Key: "*SELECT* 1_2".reported_by
  • Sort Method: quicksort Memory: 430kB
304. 1.166 10.394 ↑ 11.6 1,435 1

Merge Left Join (cost=707.91..965.50 rows=16,694 width=178) (actual time=8.924..10.394 rows=1,435 loops=1)

  • Merge Cond: ("*SELECT* 1_2".form_uuid = useview_fp_follow_up_prospective_ke.source_id)
305. 2.328 4.615 ↑ 1.0 1,435 1

Sort (cost=485.03..488.62 rows=1,436 width=122) (actual time=4.478..4.615 rows=1,435 loops=1)

  • Sort Key: "*SELECT* 1_2".form_uuid
  • Sort Method: quicksort Memory: 430kB
306. 0.150 2.287 ↑ 1.0 1,435 1

Append (cost=0.30..409.73 rows=1,436 width=122) (actual time=0.053..2.287 rows=1,435 loops=1)

307. 0.047 1.109 ↑ 1.0 363 1

Subquery Scan on *SELECT* 1_2 (cost=0.30..103.83 rows=363 width=122) (actual time=0.052..1.109 rows=363 loops=1)

308. 0.046 1.062 ↑ 1.0 363 1

Result (cost=0.30..100.20 rows=363 width=122) (actual time=0.050..1.062 rows=363 loops=1)

309. 0.036 1.016 ↑ 1.0 363 1

Append (cost=0.30..96.57 rows=363 width=121) (actual time=0.048..1.016 rows=363 loops=1)

310. 0.135 0.135 ↑ 1.0 122 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..26.31 rows=123 width=121) (actual time=0.047..0.135 rows=122 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))
311. 0.100 0.100 ↓ 1.0 122 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..20.14 rows=121 width=121) (actual time=0.035..0.100 rows=122 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))
312. 0.745 0.745 ↑ 1.0 119 1

Seq Scan on useview_fp_follow_up_long_term_ke useview_fp_follow_up_long_term_ke_1 (cost=0.00..48.30 rows=119 width=121) (actual time=0.523..0.745 rows=119 loops=1)

  • 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: 594
313. 0.132 1.028 ↑ 1.0 1,072 1

Subquery Scan on *SELECT* 2_2 (cost=0.31..298.72 rows=1,073 width=122) (actual time=0.030..1.028 rows=1,072 loops=1)

314. 0.896 0.896 ↑ 1.0 1,072 1

Index Scan using index_useview_fp_registration_ke_reported_patient_id_uuid on useview_fp_registration_ke (cost=0.31..287.99 rows=1,073 width=122) (actual time=0.029..0.896 rows=1,072 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))
315. 3.815 4.613 ↑ 1.0 2,319 1

Sort (cost=222.88..228.69 rows=2,325 width=89) (actual time=4.431..4.613 rows=2,319 loops=1)

  • Sort Key: useview_fp_follow_up_prospective_ke.source_id
  • Sort Method: quicksort Memory: 424kB
316. 0.232 0.798 ↓ 1.0 2,326 1

Append (cost=0.00..92.88 rows=2,325 width=89) (actual time=0.009..0.798 rows=2,326 loops=1)

317. 0.205 0.205 ↑ 1.0 930 1

Seq Scan on useview_fp_follow_up_prospective_ke (cost=0.00..32.30 rows=930 width=88) (actual time=0.008..0.205 rows=930 loops=1)

318. 0.205 0.205 ↓ 1.0 683 1

Seq Scan on useview_fp_follow_up_short_term_ke (cost=0.00..23.82 rows=682 width=89) (actual time=0.006..0.205 rows=683 loops=1)

319. 0.156 0.156 ↑ 1.0 713 1

Seq Scan on useview_fp_follow_up_long_term_ke (cost=0.00..25.13 rows=713 width=89) (actual time=0.007..0.156 rows=713 loops=1)

Planning time : 53.847 ms
Execution time : 113,758.403 ms