explain.depesz.com

PostgreSQL's explain analyze made readable

Result: j2qE : CHW Dashboard data

Settings
# exclusive inclusive rows x rows loops node
1. 164.940 65,435.569 ↓ 1,800.0 1,800 1

GroupAggregate (cost=146,467,853.97..146,467,855.92 rows=1 width=700) (actual time=65,268.027..65,435.569 rows=1,800 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. 21.635 65,270.629 ↓ 600.0 1,800 1

Sort (cost=146,467,853.97..146,467,853.97 rows=3 width=2,036) (actual time=65,267.705..65,270.629 rows=1,800 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: 1791kB
3. 195.155 65,248.994 ↓ 600.0 1,800 1

Merge Left Join (cost=146,365,256.81..146,467,853.94 rows=3 width=2,036) (actual time=27,812.118..65,248.994 rows=1,800 loops=1)

  • Merge Cond: (chwlist.chw_uuid = "*SELECT* 1_2".reported_by)
  • Join Filter: (chwlist.interval_number = (0))
4. 8.012 65,053.839 ↓ 600.0 1,800 1

Merge Left Join (cost=146,356,851.57..146,456,311.61 rows=3 width=1,984) (actual time=27,642.188..65,053.839 rows=1,800 loops=1)

  • Merge Cond: (chwlist.chw_uuid = useview_home_visit.reported_by)
  • Join Filter: (chwlist.interval_number = (0))
5. 3.211 58,542.726 ↓ 600.0 1,800 1

Merge Left Join (cost=146,299,658.29..146,394,763.41 rows=3 width=1,976) (actual time=22,297.560..58,542.726 rows=1,800 loops=1)

  • Merge Cond: (chwlist.chw_uuid = "*SELECT* 1_1".chw_uuid)
  • Join Filter: (chwlist.interval_number = (0))
6. 4.067 58,327.918 ↓ 600.0 1,800 1

Merge Left Join (cost=146,290,867.32..146,385,972.32 rows=3 width=1,968) (actual time=22,085.958..58,327.918 rows=1,800 loops=1)

  • Merge Cond: (chwlist.chw_uuid = dr.chw)
  • Join Filter: (chwlist.interval_number = (0))
7. 8.896 58,321.709 ↓ 600.0 1,800 1

Merge Left Join (cost=146,290,625.96..146,385,728.69 rows=3 width=1,952) (actual time=22,084.377..58,321.709 rows=1,800 loops=1)

  • Merge Cond: (chwlist.chw_uuid = assess_u1fu_pnc_aggregated.chw)
  • Join Filter: (chwlist.interval_number = (0))
8. 11.259 55,630.561 ↓ 600.0 1,800 1

Merge Left Join (cost=146,238,670.46..146,333,752.65 rows=3 width=1,912) (actual time=19,865.470..55,630.561 rows=1,800 loops=1)

  • Merge Cond: (chwlist.chw_uuid = "*SELECT* 1".reported_by)
  • Join Filter: (chwlist.interval_number = (0))
9. 2,359.556 54,783.502 ↓ 600.0 1,800 1

Nested Loop Left Join (cost=146,228,329.95..146,323,085.23 rows=3 width=1,896) (actual time=19,076.455..54,783.502 rows=1,800 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: 3996006
10. 1,965.106 48,332.546 ↓ 600.0 1,800 1

Nested Loop Left Join (cost=145,836,586.00..145,931,175.82 rows=3 width=1,920) (actual time=16,942.748..48,332.546 rows=1,800 loops=1)

  • Join Filter: ((chwlist.chw_uuid = sync.chw_uuid) AND (chwlist.interval_number = sync.interval_number))
  • Rows Removed by Join Filter: 3322925
11. 2,220.361 43,408.240 ↓ 600.0 1,800 1

Nested Loop Left Join (cost=145,811,842.04..145,906,275.81 rows=3 width=1,912) (actual time=15,607.503..43,408.240 rows=1,800 loops=1)

  • Join Filter: ((chwlist.chw_area_uuid = contactview_metadata_1.parent_uuid) AND (chwlist.interval_number = (0)))
  • Rows Removed by Join Filter: 4026606
12. 11.142 37,884.879 ↓ 1,800.0 1,800 1

Merge Left Join (cost=145,799,030.90..145,888,915.57 rows=1 width=1,868) (actual time=15,162.104..37,884.879 rows=1,800 loops=1)

  • Merge Cond: (chwlist.chw_uuid = survey.chw)
  • Join Filter: (chwlist.interval_number = (0))
13. 1,771.703 37,003.237 ↓ 1,800.0 1,800 1

Nested Loop Left Join (cost=145,776,819.99..145,865,821.36 rows=1 width=1,844) (actual time=14,486.149..37,003.237 rows=1,800 loops=1)

  • Join Filter: ((chwlist.chw_area_uuid = demographics.chw_area_uuid) AND (chwlist.interval_number = demographics.interval_number))
  • Rows Removed by Join Filter: 3000790
14. 1,835.399 32,961.734 ↓ 1,800.0 1,800 1

Nested Loop Left Join (cost=145,384,129.60..145,473,031.70 rows=1 width=1,732) (actual time=14,292.332..32,961.734 rows=1,800 loops=1)

  • Join Filter: ((chwlist.chw_uuid = household_cte.chw_uuid) AND (chwlist.interval_number = household_cte.interval_number))
  • Rows Removed by Join Filter: 3126730
15. 11.026 28,341.735 ↓ 1,800.0 1,800 1

Nested Loop Left Join (cost=145,360,252.36..145,449,057.71 rows=1 width=1,724) (actual time=13,678.437..28,341.735 rows=1,800 loops=1)

  • Join Filter: (chwlist.interval_number = 0)
16. 957.615 20,666.309 ↓ 1,800.0 1,800 1

Nested Loop Left Join (cost=145,360,252.08..145,447,934.53 rows=1 width=1,720) (actual time=13,675.621..20,666.309 rows=1,800 loops=1)

  • Join Filter: ((chwlist.chw_uuid = meta.chw) AND (chwlist.interval_number = (0)))
  • Rows Removed by Join Filter: 1741454
17. 10.802 18,364.094 ↓ 1,800.0 1,800 1

Merge Left Join (cost=145,341,112.96..145,428,716.14 rows=1 width=1,712) (actual time=13,431.130..18,364.094 rows=1,800 loops=1)

  • Merge Cond: (chwlist.chw_uuid = assess.chw)
  • Join Filter: (chwlist.interval_number = (0))
18. 1,846.168 13,382.056 ↓ 1,800.0 1,800 1

Nested Loop Left Join (cost=145,308,041.26..145,395,558.27 rows=1 width=1,704) (actual time=8,487.324..13,382.056 rows=1,800 loops=1)

  • Join Filter: ((chwlist.chw_uuid = assessments.chw_uuid) AND (chwlist.interval_number = assessments.interval_number))
  • Rows Removed by Join Filter: 3115931
19. 601.661 8,263.488 ↓ 1,800.0 1,800 1

Nested Loop Left Join (cost=145,282,026.46..145,369,447.17 rows=1 width=452) (actual time=6,957.792..8,263.488 rows=1,800 loops=1)

  • Join Filter: ((chwlist.chw_uuid = p.chw) AND (chwlist.interval_number = (0)))
  • Rows Removed by Join Filter: 1092047
20. 9.763 5,874.427 ↓ 1,800.0 1,800 1

Merge Left Join (cost=145,195,288.66..145,282,702.37 rows=1 width=444) (actual time=5,846.099..5,874.427 rows=1,800 loops=1)

  • Merge Cond: ((chwlist.chw_uuid = anc_pnc_u1fu.chw_uuid) AND (chwlist.interval_number = anc_pnc_u1fu.interval_number))
21. 7.338 356.011 ↓ 1,800.0 1,800 1

Sort (cost=19,738.87..19,738.87 rows=1 width=396) (actual time=353.841..356.011 rows=1,800 loops=1)

  • Sort Key: chwlist.chw_uuid, chwlist.interval_number
  • Sort Method: quicksort Memory: 929kB
22. 2.275 348.673 ↓ 1,800.0 1,800 1

Hash Right Join (cost=19,624.01..19,738.86 rows=1 width=396) (actual time=345.771..348.673 rows=1,800 loops=1)

  • Hash Cond: ((contactview_metadata.parent_uuid = chwlist.chw_area_uuid) AND ((0) = chwlist.interval_number))
23. 3.444 117.411 ↑ 3.8 1,111 1

HashAggregate (cost=6,909.10..6,950.86 rows=4,176 width=48) (actual time=116.757..117.411 rows=1,111 loops=1)

  • Group Key: contactview_metadata.parent_uuid, 0
24. 5.654 113.967 ↑ 1.2 3,666 1

Bitmap Heap Scan on contactview_metadata (cost=2,808.34..6,877.42 rows=4,224 width=76) (actual time=108.442..113.967 rows=3,666 loops=1)

  • Recheck Cond: ((reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone) AND (type = 'clinic'::text))
  • Heap Blocks: exact=892
25. 0.274 108.313 ↓ 0.0 0 1

BitmapAnd (cost=2,808.34..2,808.34 rows=4,224 width=0) (actual time=108.313..108.313 rows=0 loops=1)

26. 88.058 88.058 ↓ 1.1 23,458 1

Bitmap Index Scan on contactview_metadata_reported (cost=0.00..283.13 rows=21,668 width=0) (actual time=88.058..88.058 rows=23,458 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
27. 19.981 19.981 ↓ 1.0 238,653 1

Bitmap Index Scan on contactview_metadata_type (cost=0.00..2,522.85 rows=237,256 width=0) (actual time=19.981..19.981 rows=238,653 loops=1)

  • Index Cond: (type = 'clinic'::text)
28. 2.017 228.987 ↓ 1,800.0 1,800 1

Hash (cost=12,714.89..12,714.89 rows=1 width=388) (actual time=228.987..228.987 rows=1,800 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 643kB
29. 2.260 226.970 ↓ 1,800.0 1,800 1

Hash Right Join (cost=12,662.08..12,714.89 rows=1 width=388) (actual time=223.856..226.970 rows=1,800 loops=1)

  • Hash Cond: ((useview_pregnancy.chw = chwlist.chw_uuid) AND ((0) = chwlist.interval_number))
30. 5.642 16.419 ↑ 1.3 1,480 1

HashAggregate (cost=538.78..557.98 rows=1,920 width=56) (actual time=15.544..16.419 rows=1,480 loops=1)

  • Group Key: useview_pregnancy.chw, 0
31. 10.777 10.777 ↓ 1.0 4,644 1

Index Scan using useview_pregnancy_reported on useview_pregnancy (cost=0.32..449.46 rows=4,466 width=78) (actual time=2.374..10.777 rows=4,644 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
32. 1.946 208.291 ↓ 1,800.0 1,800 1

Hash (cost=12,123.29..12,123.29 rows=1 width=372) (actual time=208.291..208.291 rows=1,800 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 620kB
33. 2.492 206.345 ↓ 1,800.0 1,800 1

Hash Right Join (cost=12,064.34..12,123.29 rows=1 width=372) (actual time=202.965..206.345 rows=1,800 loops=1)

  • Hash Cond: ((anc.chw = chwlist.chw_uuid) AND ((0) = chwlist.interval_number))
34. 14.775 70.253 ↑ 1.3 1,620 1

HashAggregate (cost=8,289.74..8,311.17 rows=2,143 width=48) (actual time=69.334..70.253 rows=1,620 loops=1)

  • Group Key: anc.chw, 0
35. 55.478 55.478 ↓ 1.0 16,493 1

Index Scan using useview_pregnancy_visit_pregnancy_reported_uuid on useview_pregnancy_visit anc (cost=0.45..8,169.38 rows=16,048 width=40) (actual time=4.593..55.478 rows=16,493 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
36. 2.315 133.600 ↓ 1,800.0 1,800 1

Hash (cost=3,774.59..3,774.59 rows=1 width=364) (actual time=133.600..133.600 rows=1,800 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 606kB
37. 2.613 131.285 ↓ 1,800.0 1,800 1

Hash Right Join (cost=3,719.44..3,774.59 rows=1 width=364) (actual time=127.310..131.285 rows=1,800 loops=1)

  • Hash Cond: ((pnc.chw = chwlist.chw_uuid) AND ((0) = chwlist.interval_number))
38. 32.591 44.611 ↑ 1.4 1,478 1

HashAggregate (cost=3,599.82..3,619.87 rows=2,005 width=136) (actual time=43.229..44.611 rows=1,478 loops=1)

  • Group Key: pnc.chw, 0
39. 12.020 12.020 ↑ 1.3 4,612 1

Index Scan using useview_postnatal_care_count_reported_uuid on useview_postnatal_care pnc (cost=0.45..2,884.71 rows=6,086 width=102) (actual time=0.108..12.020 rows=4,612 loops=1)

  • Index Cond: ((follow_up_count = '1'::text) AND (reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
40. 2.529 84.061 ↓ 1,800.0 1,800 1

Hash (cost=119.61..119.61 rows=1 width=268) (actual time=84.061..84.061 rows=1,800 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 459kB
41. 1.879 81.532 ↓ 1,800.0 1,800 1

Subquery Scan on chwlist (cost=118.55..119.61 rows=1 width=268) (actual time=49.886..81.532 rows=1,800 loops=1)

42. 4.367 79.653 ↓ 1,800.0 1,800 1

Nested Loop Anti Join (cost=118.55..119.60 rows=1 width=268) (actual time=49.884..79.653 rows=1,800 loops=1)

43.          

CTE chw_hierarchy

44. 6.986 58.765 ↓ 2,452.0 2,452 1

Group (cost=118.24..118.27 rows=1 width=221) (actual time=49.730..58.765 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
45. 15.045 51.779 ↓ 2,452.0 2,452 1

Sort (cost=118.24..118.24 rows=1 width=209) (actual time=49.715..51.779 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
46. 6.104 36.734 ↓ 2,452.0 2,452 1

Nested Loop (cost=3.65..118.23 rows=1 width=209) (actual time=0.117..36.734 rows=2,452 loops=1)

47. 4.382 6.110 ↓ 2,452.0 2,452 1

Hash Join (cost=3.22..115.95 rows=1 width=192) (actual time=0.094..6.110 rows=2,452 loops=1)

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

Seq Scan on contactview_chp chp_1 (cost=0.00..103.52 rows=2,452 width=172) (actual time=0.012..1.665 rows=2,452 loops=1)

49. 0.016 0.063 ↓ 1.8 14 1

Hash (cost=3.12..3.12 rows=8 width=53) (actual time=0.063..0.063 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
50. 0.047 0.047 ↓ 1.8 14 1

Index Scan using contactview_metadata_type on contactview_metadata cmd_2 (cost=0.43..3.12 rows=8 width=53) (actual time=0.017..0.047 rows=14 loops=1)

  • Index Cond: (type = 'district_hospital'::text)
51. 24.520 24.520 ↑ 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.009..0.010 rows=1 loops=2,452)

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

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

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

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

  • Index Cond: (chw = chw_hierarchy.chw_uuid)
  • Heap Fetches: 94
54. 2.725 5,508.653 ↑ 31.1 1,285 1

Materialize (cost=145,175,549.79..145,262,763.48 rows=40,000 width=84) (actual time=5,492.244..5,508.653 rows=1,285 loops=1)

55. 11.132 5,505.928 ↑ 31.1 1,285 1

GroupAggregate (cost=145,175,549.79..145,262,263.48 rows=40,000 width=84) (actual time=5,492.236..5,505.928 rows=1,285 loops=1)

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

CTE u1s

57. 15.647 504.010 ↑ 7.3 7,274 1

HashAggregate (cost=337,881.82..338,412.44 rows=53,062 width=81) (actual time=498.298..504.010 rows=7,274 loops=1)

  • Group Key: useview_population_demographics.family_uuid, useview_population_demographics.uuid, useview_population_demographics.date_of_birth, 0
58. 488.363 488.363 ↑ 7.3 7,274 1

Index Scan using useview_population_demographics_chp_area_reported on useview_population_demographics (cost=0.58..337,351.20 rows=53,062 width=81) (actual time=0.260..488.363 rows=7,274 loops=1)

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

CTE family_persons

60. 32.980 2,191.019 ↑ 7.7 6,308 1

GroupAggregate (cost=562,162.37..563,134.51 rows=48,607 width=69) (actual time=2,151.619..2,191.019 rows=6,308 loops=1)

  • Group Key: pd.family_uuid
61. 42.544 2,158.039 ↑ 4.5 10,910 1

Sort (cost=562,162.37..562,283.88 rows=48,607 width=73) (actual time=2,151.581..2,158.039 rows=10,910 loops=1)

  • Sort Key: pd.family_uuid
  • Sort Method: quicksort Memory: 1919kB
62. 66.225 2,115.495 ↑ 4.5 10,910 1

Hash Join (cost=508,984.12..558,378.58 rows=48,607 width=73) (actual time=2,047.847..2,115.495 rows=10,910 loops=1)

  • Hash Cond: (u1s.family_uuid = pd.family_uuid)
63. 4.562 4.562 ↑ 7.3 7,274 1

CTE Scan on u1s (cost=0.00..1,061.24 rows=53,062 width=32) (actual time=0.001..4.562 rows=7,274 loops=1)

64. 314.736 2,044.708 ↑ 4.7 311,306 1

Hash (cost=472,201.65..472,201.65 rows=1,459,878 width=73) (actual time=2,044.708..2,044.708 rows=311,306 loops=1)

  • Buckets: 524288 Batches: 4 Memory Usage: 12122kB
65. 1,729.972 1,729.972 ↑ 4.7 311,306 1

Seq Scan on useview_population_demographics pd (cost=0.00..472,201.65 rows=1,459,878 width=73) (actual time=482.276..1,729.972 rows=311,306 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: 660801
66.          

CTE pnc_mother_baby

67. 7.752 3,199.090 ↑ 8,220.4 3,227 1

Group (cost=4,993,077.53..5,457,305.04 rows=26,527,286 width=176) (actual time=3,186.869..3,199.090 rows=3,227 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
68. 29.446 3,191.338 ↑ 3,313.8 8,005 1

Sort (cost=4,993,077.53..5,059,395.75 rows=26,527,286 width=172) (actual time=3,186.864..3,191.338 rows=8,005 loops=1)

  • Sort Key: pnc_1.chw, u1s_1.family_uuid, fp.person_ids, u1s_1.uuid, u1s_1.interval_number
  • Sort Method: quicksort Memory: 2319kB
69. 17.755 3,161.892 ↑ 3,313.8 8,005 1

Merge Join (cost=28,681.41..426,856.01 rows=26,527,286 width=172) (actual time=3,134.256..3,161.892 rows=8,005 loops=1)

  • Merge Cond: (u1s_1.family_uuid = fp.family_uuid)
70. 31.178 544.968 ↑ 7.3 7,274 1

Sort (cost=5,225.38..5,358.04 rows=53,062 width=68) (actual time=540.069..544.968 rows=7,274 loops=1)

  • Sort Key: u1s_1.family_uuid
  • Sort Method: quicksort Memory: 1215kB
71. 513.790 513.790 ↑ 7.3 7,274 1

CTE Scan on u1s u1s_1 (cost=0.00..1,061.24 rows=53,062 width=68) (actual time=498.302..513.790 rows=7,274 loops=1)

72. 28.210 2,599.169 ↑ 12.5 8,005 1

Sort (cost=23,456.03..23,705.99 rows=99,986 width=136) (actual time=2,594.174..2,599.169 rows=8,005 loops=1)

  • Sort Key: fp.family_uuid
  • Sort Method: quicksort Memory: 2089kB
73. 149.956 2,570.959 ↑ 14.0 7,141 1

Merge Join (cost=4,756.34..15,152.47 rows=99,986 width=136) (actual time=2,223.307..2,570.959 rows=7,141 loops=1)

  • Merge Cond: (pnc_1.patient_id = fp.person_ids)
74. 190.809 190.809 ↑ 1.0 129,661 1

Index Scan using useview_postnatal_patient_id on useview_postnatal_care pnc_1 (cost=0.42..8,570.28 rows=130,591 width=72) (actual time=0.022..190.809 rows=129,661 loops=1)

75. 29.720 2,230.194 ↑ 4.6 10,560 1

Sort (cost=4,755.92..4,877.44 rows=48,607 width=64) (actual time=2,222.524..2,230.194 rows=10,560 loops=1)

  • Sort Key: fp.person_ids
  • Sort Method: quicksort Memory: 1207kB
76. 2,200.474 2,200.474 ↑ 7.7 6,308 1

CTE Scan on family_persons fp (cost=0.00..972.14 rows=48,607 width=64) (actual time=2,151.624..2,200.474 rows=6,308 loops=1)

77.          

CTE anc_pnc_u1fu

78. 52.160 5,484.800 ↑ 822.0 3,227 1

GroupAggregate (cost=130,226,971.00..138,423,615.70 rows=2,652,729 width=96) (actual time=5,417.190..5,484.800 rows=3,227 loops=1)

  • Group Key: pmb.chw_uuid, pmb.baby_uuid, pmb.interval_number
79. 84.148 5,432.640 ↑ 15,410.1 30,296 1

Sort (cost=130,226,971.00..131,394,130.63 rows=466,863,852 width=179) (actual time=5,417.152..5,432.640 rows=30,296 loops=1)

  • Sort Key: pmb.chw_uuid, pmb.baby_uuid, pmb.interval_number
  • Sort Method: quicksort Memory: 8816kB
80. 32.979 5,348.492 ↑ 15,410.1 30,296 1

Hash Left Join (cost=4,671,058.44..15,586,389.02 rows=466,863,852 width=179) (actual time=4,862.816..5,348.492 rows=30,296 loops=1)

  • Hash Cond: (pmb.baby_uuid = u1fu.patient_id)
81. 143.159 5,286.283 ↑ 6,487.3 30,296 1

Merge Left Join (cost=4,669,892.20..8,705,531.77 rows=196,537,999 width=142) (actual time=4,833.533..5,286.283 rows=30,296 loops=1)

  • Merge Cond: (pmb.mother_uuid = pv_1.patient_id)
82. 99.464 4,130.549 ↑ 6,816.7 8,005 1

Merge Left Join (cost=4,648,529.92..5,599,680.51 rows=54,567,611 width=137) (actual time=3,942.373..4,130.549 rows=8,005 loops=1)

  • Merge Cond: (pmb.mother_uuid = pc_1.patient_id)
83. 12.753 3,217.131 ↑ 8,220.4 3,227 1

Sort (cost=4,630,467.24..4,696,785.45 rows=26,527,286 width=100) (actual time=3,214.760..3,217.131 rows=3,227 loops=1)

  • Sort Key: pmb.mother_uuid
  • Sort Method: quicksort Memory: 954kB
84. 3,204.378 3,204.378 ↑ 8,220.4 3,227 1

CTE Scan on pnc_mother_baby pmb (cost=0.00..530,545.72 rows=26,527,286 width=100) (actual time=3,186.874..3,204.378 rows=3,227 loops=1)

85. 712.939 813.954 ↑ 1.0 130,525 1

Sort (cost=18,062.68..18,389.16 rows=130,591 width=73) (actual time=727.293..813.954 rows=130,525 loops=1)

  • Sort Key: pc_1.patient_id
  • Sort Method: quicksort Memory: 21302kB
86. 101.015 101.015 ↑ 1.0 129,692 1

Seq Scan on useview_postnatal_care pc_1 (cost=0.00..6,965.91 rows=130,591 width=73) (actual time=0.010..101.015 rows=129,692 loops=1)

87. 879.648 1,012.575 ↓ 1.1 191,945 1

Sort (cost=21,362.28..21,796.50 rows=173,690 width=73) (actual time=890.856..1,012.575 rows=191,945 loops=1)

  • Sort Key: pv_1.patient_id
  • Sort Method: quicksort Memory: 30394kB
88. 132.927 132.927 ↑ 1.0 172,478 1

Seq Scan on useview_pregnancy_visit pv_1 (cost=0.00..6,245.90 rows=173,690 width=73) (actual time=0.012..132.927 rows=172,478 loops=1)

89. 12.685 29.230 ↓ 1.0 16,981 1

Hash (cost=954.44..954.44 rows=16,944 width=74) (actual time=29.230..29.230 rows=16,981 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2014kB
90. 16.545 16.545 ↓ 1.0 16,981 1

Seq Scan on useview_u1_follow_up u1fu (cost=0.00..954.44 rows=16,944 width=74) (actual time=0.059..16.545 rows=16,981 loops=1)

91. 5.153 5,494.796 ↑ 822.0 3,227 1

Sort (cost=393,082.11..399,713.93 rows=2,652,729 width=60) (actual time=5,492.206..5,494.796 rows=3,227 loops=1)

  • Sort Key: anc_pnc_u1fu.chw_uuid, anc_pnc_u1fu.interval_number
  • Sort Method: quicksort Memory: 550kB
92. 5,489.643 5,489.643 ↑ 822.0 3,227 1

CTE Scan on anc_pnc_u1fu (cost=0.00..53,054.58 rows=2,652,729 width=60) (actual time=5,417.195..5,489.643 rows=3,227 loops=1)

93. 677.730 1,787.400 ↓ 3.0 607 1,800

HashAggregate (cost=86,737.80..86,739.80 rows=200 width=48) (actual time=0.618..0.993 rows=607 loops=1,800)

  • Group Key: p.chw, 0
94. 40.486 1,109.670 ↑ 749.9 837 1

Merge Right Join (cost=25,425.35..82,030.11 rows=627,692 width=76) (actual time=845.661..1,109.670 rows=837 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: 1804
95. 61.671 221.986 ↓ 1.1 41,705 1

GroupAggregate (cost=0.42..9,515.86 rows=38,284 width=44) (actual time=0.440..221.986 rows=41,705 loops=1)

  • Group Key: useview_postnatal_care.patient_id
96. 160.315 160.315 ↑ 1.0 46,856 1

Index Scan using useview_postnatal_patient_id on useview_postnatal_care (cost=0.42..8,896.76 rows=47,251 width=44) (actual time=0.027..160.315 rows=46,856 loops=1)

  • Filter: (follow_up_count = '1'::text)
  • Rows Removed by Filter: 82804
97. 10.712 847.198 ↑ 3.7 2,641 1

Sort (cost=25,424.93..25,449.28 rows=9,740 width=76) (actual time=845.133..847.198 rows=2,641 loops=1)

  • Sort Key: p.patient_id
  • Sort Method: quicksort Memory: 468kB
98. 53.781 836.486 ↑ 3.7 2,641 1

HashAggregate (cost=24,511.82..24,682.27 rows=9,740 width=76) (actual time=806.447..836.486 rows=2,641 loops=1)

  • Group Key: p.chw, p.patient_id
  • Filter: ((max(p.edd) >= date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval))) AND CASE WHEN (date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) = date_trunc('day'::text, now())) THEN (max(p.edd) <= (((now())::timestamp without time zone)::timestamp with time zone - '3 days'::interval)) ELSE (max(p.edd) <= (((now())::timestamp without time zone)::timestamp with time zone + '1 day'::interval)) END)
  • Rows Removed by Filter: 9215
99. 33.616 782.705 ↓ 1.3 12,864 1

Merge Anti Join (cost=19,193.25..24,317.02 rows=9,740 width=78) (actual time=451.404..782.705 rows=12,864 loops=1)

  • Merge Cond: (p.patient_id = useview_pregnancy_visit_1.patient_id)
100. 108.043 635.310 ↓ 1.6 19,663 1

Merge Join (cost=14,707.66..19,528.25 rows=12,401 width=78) (actual time=362.538..635.310 rows=19,663 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: 57
101. 37.526 434.901 ↑ 1.9 18,123 1

Unique (cost=14,707.24..14,969.92 rows=35,047 width=47) (actual time=362.410..434.901 rows=18,123 loops=1)

102. 240.424 397.375 ↑ 1.1 49,603 1

Sort (cost=14,707.24..14,838.58 rows=52,536 width=47) (actual time=362.407..397.375 rows=49,603 loops=1)

  • Sort Key: useview_pregnancy_visit.patient_id, useview_pregnancy_visit.reported DESC
  • Sort Method: quicksort Memory: 8510kB
103. 156.951 156.951 ↑ 1.1 49,603 1

Seq Scan on useview_pregnancy_visit (cost=0.00..10,588.15 rows=52,536 width=47) (actual time=0.023..156.951 rows=49,603 loops=1)

  • Filter: (((edd_updated IS TRUE) AND (reported > ((date_trunc('day'::text, now()) - '3 mons'::interval) - '9 mons'::interval))) OR (reported > (((now())::timestamp without time zone)::timestamp with time zone - '9 mons'::interval)))
  • Rows Removed by Filter: 122875
104. 92.366 92.366 ↓ 1.0 59,225 1

Index Scan using useview_pregnancy_patient_id on useview_pregnancy p (cost=0.41..3,507.24 rows=59,188 width=84) (actual time=0.059..92.366 rows=59,225 loops=1)

105. 8.117 113.779 ↑ 1.6 7,702 1

Materialize (cost=4,485.60..4,701.25 rows=11,964 width=36) (actual time=88.829..113.779 rows=7,702 loops=1)

106. 10.038 105.662 ↑ 1.6 7,702 1

Unique (cost=4,485.60..4,551.70 rows=11,964 width=44) (actual time=88.823..105.662 rows=7,702 loops=1)

107. 44.215 95.624 ↑ 1.4 9,699 1

Sort (cost=4,485.60..4,518.65 rows=13,220 width=44) (actual time=88.821..95.624 rows=9,699 loops=1)

  • Sort Key: useview_pregnancy_visit_1.patient_id, useview_pregnancy_visit_1.reported DESC
  • Sort Method: quicksort Memory: 1142kB
108. 51.409 51.409 ↑ 1.4 9,699 1

Index Scan using useview_pregnancy_visit_continue_follow_up on useview_pregnancy_visit useview_pregnancy_visit_1 (cost=0.42..3,580.66 rows=13,220 width=44) (actual time=0.075..51.409 rows=9,699 loops=1)

  • Index Cond: (continue_follow_up = false)
  • Filter: ((continue_follow_up IS FALSE) AND ((reported > ((date_trunc('day'::text, now()) - '3 mons'::interval) - '9 mons'::interval)) OR (reported > (((now())::timestamp without time zone)::timestamp with time zone - '9 mons'::interval))))
  • Rows Removed by Filter: 14923
109. 3,272.400 3,272.400 ↑ 1.2 1,732 1,800

CTE Scan on assessments (cost=26,014.80..26,057.60 rows=2,140 width=1,288) (actual time=0.843..1.818 rows=1,732 loops=1,800)

110.          

CTE assessments

111. 434.877 1,526.847 ↑ 1.2 1,732 1

HashAggregate (cost=25,774.05..26,014.80 rows=2,140 width=1,292) (actual time=1,515.801..1,526.847 rows=1,732 loops=1)

  • Group Key: chwview_assessment.chw_uuid, 0
112. 1,091.970 1,091.970 ↓ 1.0 105,440 1

Index Scan using chwview_assessment_reported_chw_uuid on chwview_assessment (cost=0.45..14,975.64 rows=102,842 width=352) (actual time=3.355..1,091.970 rows=105,440 loops=1)

  • Index Cond: ((reported_day >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported_day < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
113. 2.810 4,971.236 ↑ 1.5 1,434 1

Materialize (cost=33,071.71..33,152.40 rows=2,136 width=48) (actual time=4,943.797..4,971.236 rows=1,434 loops=1)

114. 13.253 4,968.426 ↑ 1.5 1,434 1

GroupAggregate (cost=33,071.71..33,125.70 rows=2,136 width=48) (actual time=4,943.782..4,968.426 rows=1,434 loops=1)

  • Group Key: assess.chw, 0
115. 54.544 4,955.173 ↓ 4.6 14,853 1

Sort (cost=33,071.71..33,079.86 rows=3,263 width=77) (actual time=4,943.769..4,955.173 rows=14,853 loops=1)

  • Sort Key: assess.chw
  • Sort Method: quicksort Memory: 2473kB
116. 58.339 4,900.629 ↓ 4.6 14,853 1

Nested Loop (cost=3,981.47..32,881.28 rows=3,263 width=77) (actual time=240.758..4,900.629 rows=14,853 loops=1)

117. 2,163.162 2,402.098 ↓ 1.4 19,064 1

Bitmap Heap Scan on useview_assessment assess (cost=3,980.92..16,669.74 rows=13,849 width=81) (actual time=240.665..2,402.098 rows=19,064 loops=1)

  • Recheck Cond: ((referral_follow_up = 'true'::text) AND (reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone) AND (patient_age_in_years < 5))
  • Heap Blocks: exact=5173
118. 8.598 238.936 ↓ 0.0 0 1

BitmapAnd (cost=3,980.92..3,980.92 rows=13,849 width=0) (actual time=238.936..238.936 rows=0 loops=1)

119. 37.442 37.442 ↓ 1.0 168,865 1

Bitmap Index Scan on useview_assessment_referral_follow_up (cost=0.00..1,733.81 rows=168,452 width=0) (actual time=37.442..37.442 rows=168,865 loops=1)

  • Index Cond: (referral_follow_up = 'true'::text)
120. 192.896 192.896 ↓ 1.1 91,856 1

Bitmap Index Scan on useview_assessment_reported_age_uuid (cost=0.00..2,239.93 rows=80,829 width=0) (actual time=192.896..192.896 rows=91,856 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone) AND (patient_age_in_years < 5))
121. 2,440.192 2,440.192 ↑ 1.0 1 19,064

Index Only Scan using useview_assessment_follow_up_source_date_uuid on useview_assessment_follow_up follow_up (cost=0.55..1.16 rows=1 width=45) (actual time=0.127..0.128 rows=1 loops=19,064)

  • 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: 893
122. 1,105.579 1,344.600 ↑ 2.3 968 1,800

HashAggregate (cost=19,139.11..19,161.76 rows=2,265 width=48) (actual time=0.137..0.747 rows=968 loops=1,800)

  • Group Key: meta.chw, 0
123. 15.352 239.021 ↑ 2.0 4,815 1

Bitmap Heap Scan on form_metadata meta (cost=9,587.99..19,068.38 rows=9,431 width=76) (actual time=224.333..239.021 rows=4,815 loops=1)

  • Recheck Cond: ((formname = 'community_event'::text) AND (reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
  • Heap Blocks: exact=4011
124. 5.897 223.669 ↓ 0.0 0 1

BitmapAnd (cost=9,587.99..9,587.99 rows=9,431 width=0) (actual time=223.669..223.669 rows=0 loops=1)

125. 13.464 13.464 ↑ 1.0 51,595 1

Bitmap Index Scan on form_metadata_formname (cost=0.00..624.32 rows=52,119 width=0) (actual time=13.464..13.464 rows=51,595 loops=1)

  • Index Cond: (formname = 'community_event'::text)
126. 204.308 204.308 ↓ 1.0 711,750 1

Bitmap Index Scan on form_metadata_reported (cost=0.00..8,958.70 rows=706,624 width=0) (actual time=204.308..204.308 rows=711,750 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
127. 39.830 7,664.400 ↑ 1.0 1 1,800

Index Only Scan using contactview_chp_uuid on contactview_chp chp (cost=0.28..1,123.16 rows=1 width=40) (actual time=4.255..4.258 rows=1 loops=1,800)

  • Index Cond: (uuid = chwlist.chw_uuid)
  • Heap Fetches: 262
128.          

SubPlan (forIndex Only Scan)

129. 36.442 3,722.400 ↑ 26.0 1 1,800

Nested Loop Semi Join (cost=0.56..1,795.20 rows=26 width=0) (actual time=2.068..2.068 rows=1 loops=1,800)

  • Join Filter: (meta_1.formname = "*VALUES*".column1)
  • Rows Removed by Join Filter: 19
130. 3,661.200 3,661.200 ↑ 43.9 7 1,800

Index Scan using form_metadata_chw on form_metadata meta_1 (cost=0.56..1,781.34 rows=307 width=16) (actual time=1.640..2.034 rows=7 loops=1,800)

  • Index Cond: (chw = chp.uuid)
  • Filter: ((reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 769
131. 24.754 24.758 ↑ 1.0 3 12,379

Materialize (cost=0.00..0.05 rows=3 width=32) (actual time=0.001..0.002 rows=3 loops=12,379)

132. 0.004 0.004 ↑ 1.0 3 1

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

133. 0.000 0.000 ↓ 0.0 0

Hash Semi Join (cost=0.53..39,095.00 rows=58,885 width=36) (never executed)

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

Index Scan using form_metadata_reported on form_metadata meta_2 (cost=0.46..36,584.94 rows=706,624 width=52) (never executed)

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

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

136. 0.000 0.000 ↓ 0.0 0

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

137. 0.130 46.930 ↓ 0.0 0 130

Nested Loop (cost=2.41..10.99 rows=1 width=0) (actual time=0.361..0.361 rows=0 loops=130)

138. 0.260 46.800 ↓ 0.0 0 130

Nested Loop (cost=1.84..8.39 rows=1 width=648) (actual time=0.360..0.360 rows=0 loops=130)

139. 0.650 46.540 ↓ 0.0 0 130

Nested Loop (cost=1.28..5.79 rows=1 width=36) (actual time=0.358..0.358 rows=0 loops=130)

  • Join Filter: (chw.parent_uuid = cmd.parent_uuid)
140. 0.910 11.050 ↑ 1.0 1 130

Nested Loop (cost=0.85..4.90 rows=1 width=108) (actual time=0.082..0.085 rows=1 loops=130)

141. 7.150 7.150 ↑ 1.0 1 130

Index Scan using contactview_metadata_uuid on contactview_metadata chw (cost=0.43..2.45 rows=1 width=72) (actual time=0.054..0.055 rows=1 loops=130)

  • Index Cond: (uuid = chp.uuid)
142. 2.990 2.990 ↑ 1.0 1 130

Index Only Scan using contactview_metadata_uuid on contactview_metadata chwarea (cost=0.43..2.45 rows=1 width=36) (actual time=0.022..0.023 rows=1 loops=130)

  • Index Cond: (uuid = chw.parent_uuid)
  • Heap Fetches: 5
143. 34.840 34.840 ↓ 0.0 0 130

Index Scan using contactview_metadata_parent_uuid on contactview_metadata cmd (cost=0.43..0.88 rows=1 width=36) (actual time=0.268..0.268 rows=0 loops=130)

  • Index Cond: (parent_uuid = chwarea.uuid)
  • Filter: ((type = 'clinic'::text) AND (reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 88
144. 0.000 0.000 ↓ 0.0 0

Index Scan using couchdb_doc_uuid on couchdb (cost=0.56..2.59 rows=1 width=648) (never executed)

  • 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[])))
145. 0.000 0.000 ↓ 0.0 0

Index Scan using couchdb_doc_uuid on couchdb couchdb_1 (cost=0.56..2.59 rows=1 width=648) (never executed)

  • 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[])))
146. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=122.33..113,226.77 rows=1 width=32) (never executed)

147. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=121.90..113,226.30 rows=1 width=720) (never executed)

148. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=121.47..113,225.42 rows=1 width=684) (never executed)

149. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=121.04..113,224.05 rows=2 width=648) (never executed)

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

Index Scan using couchdb_doc_type on couchdb couchdb_2 (cost=0.43..112,210.85 rows=142,812 width=648) (never executed)

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

Hash (cost=118.96..118.96 rows=132 width=648) (never executed)

152. 0.000 0.000 ↓ 0.0 0

Index Scan using couchdb_doc_type on couchdb couchdb_3 (cost=0.43..118.96 rows=132 width=648) (never executed)

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

Index Scan using contactview_metadata_uuid on contactview_metadata chw_1 (cost=0.43..0.67 rows=1 width=72) (never executed)

  • Index Cond: (uuid = (couchdb_2.doc ->> '_id'::text))
154. 0.000 0.000 ↓ 0.0 0

Index Scan using contactview_metadata_parent_uuid on contactview_metadata cmd_1 (cost=0.43..0.88 rows=1 width=36) (never executed)

  • Index Cond: (parent_uuid = chw_1.parent_uuid)
  • Filter: ((type = 'clinic'::text) AND (reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
155. 0.000 0.000 ↓ 0.0 0

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

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

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

157. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.97..977.25 rows=98 width=684) (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..633.81 rows=211 width=648) (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.62 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('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
161. 115.540 3,855.240 ↓ 256.3 4,613 1

Nested Loop (cost=1.53..1,041.05 rows=18 width=36) (actual time=1.236..3,855.240 rows=4,613 loops=1)

162. 1,611.187 2,896.058 ↓ 478.3 46,869 1

Nested Loop (cost=0.97..977.25 rows=98 width=684) (actual time=1.201..2,896.058 rows=46,869 loops=1)

163. 252.455 252.455 ↓ 222.4 46,928 1

Index Scan using couchdb_doc_form_postnatal_care_follow_up_count on couchdb form_4 (cost=0.42..633.81 rows=211 width=648) (actual time=0.036..252.455 rows=46,928 loops=1)

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

Index Only Scan using form_metadata_uuid on form_metadata fm_1 (cost=0.56..1.62 rows=1 width=36) (actual time=0.021..0.022 rows=1 loops=46,928)

  • Index Cond: (uuid = (form_4.doc ->> '_id'::text))
  • Heap Fetches: 1307
165. 843.642 843.642 ↓ 0.0 0 46,869

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

  • Index Cond: (uuid = fm_1.uuid)
  • Filter: ((reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 1
166. 2,784.600 2,784.600 ↑ 1.2 1,738 1,800

CTE Scan on household_cte (cost=23,877.24..23,920.24 rows=2,150 width=44) (actual time=0.342..1.547 rows=1,738 loops=1,800)

167.          

CTE household_cte

168. 346.043 1,085.229 ↑ 1.2 1,738 1

GroupAggregate (cost=22,031.72..23,877.24 rows=2,150 width=48) (actual time=613.882..1,085.229 rows=1,738 loops=1)

  • Group Key: useview_households_visited.chw, 0
169. 521.125 739.186 ↓ 1.0 188,022 1

Sort (cost=22,031.72..22,487.73 rows=182,402 width=76) (actual time=613.795..739.186 rows=188,022 loops=1)

  • Sort Key: useview_households_visited.chw
  • Sort Method: quicksort Memory: 32585kB
170. 218.061 218.061 ↓ 1.0 188,022 1

Index Only Scan using households_date_visited_chw_hh on useview_households_visited (cost=0.58..6,092.74 rows=182,402 width=76) (actual time=1.212..218.061 rows=188,022 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
  • Heap Fetches: 7522
171. 2,269.800 2,269.800 ↑ 1.3 1,668 1,800

CTE Scan on demographics (cost=392,690.39..392,734.51 rows=2,206 width=148) (actual time=0.001..1.261 rows=1,668 loops=1,800)

172.          

CTE demographics

173. 267.195 602.513 ↑ 1.3 1,668 1

GroupAggregate (cost=0.58..392,690.39 rows=2,206 width=152) (actual time=0.238..602.513 rows=1,668 loops=1)

  • Group Key: population_1.chp_area_uuid, 0
174. 335.318 335.318 ↑ 8.2 19,529 1

Index Scan using useview_population_demographics_chp_area_reported on useview_population_demographics population_1 (cost=0.58..334,963.41 rows=159,186 width=86) (actual time=0.130..335.318 rows=19,529 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
175. 3.180 870.500 ↑ 1.4 1,536 1

Materialize (cost=22,210.91..23,088.70 rows=2,149 width=64) (actual time=675.942..870.500 rows=1,536 loops=1)

176. 163.776 867.320 ↑ 1.4 1,536 1

GroupAggregate (cost=22,210.91..23,061.84 rows=2,149 width=64) (actual time=675.937..867.320 rows=1,536 loops=1)

  • Group Key: survey.chw, 0
177. 129.188 703.544 ↓ 1.0 38,092 1

Sort (cost=22,210.91..22,303.07 rows=36,864 width=88) (actual time=675.868..703.544 rows=38,092 loops=1)

  • Sort Key: survey.chw
  • Sort Method: quicksort Memory: 6893kB
178. 574.356 574.356 ↓ 1.0 38,092 1

Index Scan using useview_family_survey_uuid_reported_chw on useview_family_survey survey (cost=0.45..19,414.79 rows=36,864 width=88) (actual time=1.787..574.356 rows=38,092 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
179. 3,060.746 3,303.000 ↑ 58.1 2,238 1,800

HashAggregate (cost=12,811.14..14,110.88 rows=129,974 width=48) (actual time=0.249..1.835 rows=2,238 loops=1,800)

  • Group Key: contactview_metadata_1.parent_uuid, 0
180. 242.254 242.254 ↓ 1.0 238,629 1

Index Scan using contactview_metadata_type on contactview_metadata contactview_metadata_1 (cost=0.43..11,031.72 rows=237,256 width=40) (actual time=0.042..242.254 rows=238,629 loops=1)

  • Index Cond: (type = 'clinic'::text)
181. 1,623.009 2,959.200 ↓ 1.0 1,847 1,800

Materialize (cost=24,743.96..24,808.22 rows=1,836 width=48) (actual time=0.741..1.644 rows=1,847 loops=1,800)

182. 1.685 1,336.191 ↓ 1.0 1,847 1

Subquery Scan on sync (cost=24,743.96..24,799.04 rows=1,836 width=48) (actual time=1,332.111..1,336.191 rows=1,847 loops=1)

183. 716.407 1,334.506 ↓ 1.0 1,847 1

HashAggregate (cost=24,743.96..24,780.68 rows=1,836 width=48) (actual time=1,332.110..1,334.506 rows=1,847 loops=1)

  • Group Key: useview_chp_sync.chw_uuid, 0
184. 618.099 618.099 ↑ 1.0 763,026 1

Seq Scan on useview_chp_sync (cost=0.00..19,021.26 rows=763,026 width=48) (actual time=0.020..618.099 rows=763,026 loops=1)

185. 1,956.571 4,091.400 ↓ 1.0 2,221 1,800

Materialize (cost=391,743.96..391,799.11 rows=2,206 width=48) (actual time=1.185..2.273 rows=2,221 loops=1,800)

186. 1.996 2,134.829 ↓ 1.0 2,221 1

Subquery Scan on population_all_time (cost=391,743.96..391,788.08 rows=2,206 width=48) (actual time=2,131.142..2,134.829 rows=2,221 loops=1)

187. 752.261 2,132.833 ↓ 1.0 2,221 1

HashAggregate (cost=391,743.96..391,766.02 rows=2,206 width=48) (actual time=2,131.140..2,132.833 rows=2,221 loops=1)

  • Group Key: population.chp_area_uuid, 0
188. 1,380.572 1,380.572 ↑ 8.3 972,107 1

Seq Scan on useview_population_demographics population (cost=0.00..331,400.69 rows=8,045,769 width=76) (actual time=421.010..1,380.572 rows=972,107 loops=1)

189. 2.394 835.800 ↓ 8.1 1,627 1

Materialize (cost=10,340.51..10,666.87 rows=200 width=56) (actual time=789.007..835.800 rows=1,627 loops=1)

190. 30.452 833.406 ↓ 8.1 1,627 1

GroupAggregate (cost=10,340.51..10,664.37 rows=200 width=56) (actual time=789.003..833.406 rows=1,627 loops=1)

  • Group Key: "*SELECT* 1".reported_by, 0
191. 64.937 802.954 ↓ 1.0 18,844 1

Sort (cost=10,340.51..10,386.35 rows=18,335 width=80) (actual time=788.976..802.954 rows=18,844 loops=1)

  • Sort Key: "*SELECT* 1".reported_by
  • Sort Method: quicksort Memory: 3418kB
192. 21.388 738.017 ↓ 1.0 18,844 1

Result (cost=0.00..9,042.18 rows=18,335 width=80) (actual time=6.574..738.017 rows=18,844 loops=1)

193. 18.262 716.629 ↓ 1.0 18,844 1

Append (cost=0.00..8,858.83 rows=18,335 width=76) (actual time=6.572..716.629 rows=18,844 loops=1)

194. 0.270 15.044 ↓ 1.0 255 1

Subquery Scan on *SELECT* 1 (cost=0.00..403.68 rows=248 width=78) (actual time=6.570..15.044 rows=255 loops=1)

195. 14.774 14.774 ↓ 1.0 255 1

Seq Scan on useview_fp_registration (cost=0.00..401.20 rows=248 width=256) (actual time=6.569..14.774 rows=255 loops=1)

  • Filter: ((reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 4562
196. 1.335 43.221 ↓ 1.0 1,279 1

Subquery Scan on *SELECT* 2 (cost=0.44..592.22 rows=1,268 width=78) (actual time=1.275..43.221 rows=1,279 loops=1)

197. 41.886 41.886 ↓ 1.0 1,279 1

Index Scan using index_useview_person_fp_registration_reported_chw_patient_id on useview_person_fp_registration (cost=0.44..579.54 rows=1,268 width=256) (actual time=1.273..41.886 rows=1,279 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
  • Filter: (patient_age_in_years >= 15)
  • Rows Removed by Filter: 236
198. 20.887 640.102 ↓ 1.0 17,310 1

Subquery Scan on *SELECT* 3 (cost=0.45..7,862.93 rows=16,819 width=76) (actual time=0.726..640.102 rows=17,310 loops=1)

199. 619.215 619.215 ↓ 1.0 17,310 1

Index Scan using useview_postnatal_reported on useview_postnatal_care useview_postnatal_care_1 (cost=0.45..7,694.74 rows=16,819 width=254) (actual time=0.724..619.215 rows=17,310 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
200. 3.070 2,682.252 ↓ 8.7 1,736 1

Materialize (cost=51,955.50..51,975.50 rows=200 width=76) (actual time=2,218.899..2,682.252 rows=1,736 loops=1)

201. 389.047 2,679.182 ↓ 8.7 1,736 1

GroupAggregate (cost=51,955.50..51,973.00 rows=200 width=76) (actual time=2,218.892..2,679.182 rows=1,736 loops=1)

  • Group Key: assess_u1fu_pnc_aggregated.chw, 0
202.          

CTE assess_u1fu_pnc

203. 120.873 884.086 ↓ 1.1 125,299 1

Append (cost=0.45..40,102.32 rows=108,970 width=99) (actual time=1.129..884.086 rows=125,299 loops=1)

204. 382.320 382.320 ↓ 1.0 105,440 1

Index Scan using useview_assessment_reported on useview_assessment (cost=0.45..30,359.45 rows=100,908 width=93) (actual time=1.127..382.320 rows=105,440 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
205. 20.081 20.081 ↓ 1.0 2,552 1

Index Scan using useview_u1_follow_up_reported_chw_uuid on useview_u1_follow_up (cost=0.44..762.23 rows=2,456 width=161) (actual time=1.156..20.081 rows=2,552 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
206. 360.812 360.812 ↓ 3.1 17,307 1

Index Scan using useview_postnatal_reported on useview_postnatal_care useview_postnatal_care_2 (cost=0.45..7,890.94 rows=5,606 width=185) (actual time=0.352..360.812 rows=17,307 loops=1)

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

CTE assess_u1fu_pnc_aggregated

208. 113.055 1,915.874 ↓ 429.6 85,925 1

Unique (cost=11,296.69..11,841.54 rows=200 width=204) (actual time=1,709.063..1,915.874 rows=85,925 loops=1)

209. 719.849 1,802.819 ↓ 1.1 125,299 1

Sort (cost=11,296.69..11,569.11 rows=108,970 width=204) (actual time=1,709.061..1,802.819 rows=125,299 loops=1)

  • Sort Key: assess_u1fu_pnc.patient_id, assess_u1fu_pnc.reported DESC
  • Sort Method: quicksort Memory: 23647kB
210. 1,082.970 1,082.970 ↓ 1.1 125,299 1

CTE Scan on assess_u1fu_pnc (cost=0.00..2,179.40 rows=108,970 width=204) (actual time=1.132..1,082.970 rows=125,299 loops=1)

211. 269.479 2,290.135 ↓ 429.6 85,925 1

Sort (cost=11.64..12.14 rows=200 width=200) (actual time=2,218.819..2,290.135 rows=85,925 loops=1)

  • Sort Key: assess_u1fu_pnc_aggregated.chw
  • Sort Method: quicksort Memory: 16814kB
212. 2,020.656 2,020.656 ↓ 429.6 85,925 1

CTE Scan on assess_u1fu_pnc_aggregated (cost=0.00..4.00 rows=200 width=200) (actual time=1,709.067..2,020.656 rows=85,925 loops=1)

213. 0.028 2.142 ↑ 2.1 16 1

Materialize (cost=241.36..243.50 rows=33 width=56) (actual time=1.576..2.142 rows=16 loops=1)

214. 0.551 2.114 ↑ 2.1 16 1

GroupAggregate (cost=241.36..243.09 rows=33 width=56) (actual time=1.572..2.114 rows=16 loops=1)

  • Group Key: dr.chw, 0
215. 0.084 1.563 ↑ 1.7 19 1

Sort (cost=241.36..241.44 rows=33 width=688) (actual time=1.528..1.563 rows=19 loops=1)

  • Sort Key: dr.chw
  • Sort Method: quicksort Memory: 67kB
216. 0.040 1.479 ↑ 1.7 19 1

Nested Loop Left Join (cost=0.99..240.52 rows=33 width=688) (actual time=0.918..1.479 rows=19 loops=1)

217. 0.964 0.964 ↑ 1.7 19 1

Index Scan using form_metadata_formname on form_metadata dr (cost=0.43..154.97 rows=33 width=72) (actual time=0.874..0.964 rows=19 loops=1)

  • Index Cond: (formname = 'death_report'::text)
  • Filter: ((reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 247
218. 0.475 0.475 ↑ 1.0 1 19

Index Scan using couchdb_doc_uuid on couchdb form (cost=0.56..2.58 rows=1 width=648) (actual time=0.024..0.025 rows=1 loops=19)

  • Index Cond: (dr.uuid = (doc ->> '_id'::text))
  • Filter: ((doc ->> 'form'::text) = 'death_report'::text)
219. 0.005 211.597 ↓ 0.0 0 1

Materialize (cost=8,790.97..8,791.04 rows=2 width=48) (actual time=211.597..211.597 rows=0 loops=1)

220. 0.002 211.592 ↓ 0.0 0 1

GroupAggregate (cost=8,790.97..8,791.01 rows=2 width=48) (actual time=211.592..211.592 rows=0 loops=1)

  • Group Key: "*SELECT* 1_1".chw_uuid, 0
221. 0.005 211.590 ↓ 0.0 0 1

Sort (cost=8,790.97..8,790.98 rows=2 width=40) (actual time=211.590..211.590 rows=0 loops=1)

  • Sort Key: "*SELECT* 1_1".chw_uuid
  • Sort Method: quicksort Memory: 25kB
222. 0.001 211.585 ↓ 0.0 0 1

Result (cost=8,169.13..8,790.96 rows=2 width=40) (actual time=211.585..211.585 rows=0 loops=1)

223. 0.002 211.584 ↓ 0.0 0 1

Append (cost=8,169.13..8,790.94 rows=2 width=36) (actual time=211.584..211.584 rows=0 loops=1)

224. 0.027 200.962 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1_1 (cost=8,169.13..8,169.32 rows=1 width=36) (actual time=200.962..200.962 rows=0 loops=1)

  • Filter: (("*SELECT* 1_1".reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND ("*SELECT* 1_1".reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 5
225. 0.011 200.935 ↓ 1.2 5 1

Unique (cost=8,169.13..8,169.15 rows=4 width=80) (actual time=200.922..200.935 rows=5 loops=1)

226. 0.040 200.924 ↓ 2.8 11 1

Sort (cost=8,169.13..8,169.14 rows=4 width=80) (actual time=200.915..200.924 rows=11 loops=1)

  • Sort Key: pv.patient_id
  • Sort Method: quicksort Memory: 26kB
227. 86.251 200.884 ↓ 2.8 11 1

Hash Join (cost=620.90..8,169.09 rows=4 width=80) (actual time=59.835..200.884 rows=11 loops=1)

  • Hash Cond: (pv.patient_id = (form_1.doc #>> '{fields,inputs,contact,_id}'::text[]))
228. 104.974 104.974 ↑ 1.0 172,392 1

Seq Scan on useview_pregnancy_visit pv (cost=0.00..6,680.12 rows=173,605 width=36) (actual time=0.011..104.974 rows=172,392 loops=1)

  • Filter: (patient_id <> ''::text)
  • Rows Removed by Filter: 86
229. 2.140 9.659 ↓ 266.0 266 1

Hash (cost=620.89..620.89 rows=1 width=692) (actual time=9.659..9.659 rows=266 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 411kB
230. 0.809 7.519 ↓ 266.0 266 1

Nested Loop (cost=0.99..620.89 rows=1 width=692) (actual time=0.176..7.519 rows=266 loops=1)

231. 0.326 0.326 ↓ 1.5 266 1

Index Scan using form_metadata_formname on form_metadata dr_1 (cost=0.43..149.06 rows=182 width=80) (actual time=0.012..0.326 rows=266 loops=1)

  • Index Cond: (formname = 'death_report'::text)
232. 6.384 6.384 ↑ 1.0 1 266

Index Scan using couchdb_doc_uuid on couchdb form_1 (cost=0.56..2.58 rows=1 width=648) (actual time=0.024..0.024 rows=1 loops=266)

  • Index Cond: ((doc ->> '_id'::text) = dr_1.uuid)
  • Filter: ((doc ->> 'form'::text) = 'death_report'::text)
233. 0.021 10.620 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=621.57..621.62 rows=1 width=36) (actual time=10.620..10.620 rows=0 loops=1)

  • Filter: (("*SELECT* 2_1".reported >= (date_trunc('day'::text, (date_trunc('day'::text, now()) - '3 mons'::interval)))::timestamp without time zone) AND ("*SELECT* 2_1".reported < ((date_trunc('day'::text, ((now())::timestamp without time zone)::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 6
234. 0.013 10.599 ↓ 6.0 6 1

Unique (cost=621.57..621.58 rows=1 width=80) (actual time=10.581..10.599 rows=6 loops=1)

235. 0.031 10.586 ↓ 16.0 16 1

Sort (cost=621.57..621.58 rows=1 width=80) (actual time=10.580..10.586 rows=16 loops=1)

  • Sort Key: pc.patient_id
  • Sort Method: quicksort Memory: 27kB
236. 2.018 10.555 ↓ 16.0 16 1

Nested Loop (cost=1.41..621.56 rows=1 width=80) (actual time=2.289..10.555 rows=16 loops=1)

237. 0.636 6.409 ↓ 266.0 266 1

Nested Loop (cost=0.99..620.89 rows=1 width=692) (actual time=0.076..6.409 rows=266 loops=1)

238. 0.453 0.453 ↓ 1.5 266 1

Index Scan using form_metadata_formname on form_metadata dr_2 (cost=0.43..149.06 rows=182 width=80) (actual time=0.019..0.453 rows=266 loops=1)

  • Index Cond: (formname = 'death_report'::text)
239. 5.320 5.320 ↑ 1.0 1 266

Index Scan using couchdb_doc_uuid on couchdb form_2 (cost=0.56..2.58 rows=1 width=648) (actual time=0.019..0.020 rows=1 loops=266)

  • Index Cond: ((doc ->> '_id'::text) = dr_2.uuid)
  • Filter: ((doc ->> 'form'::text) = 'death_report'::text)
240. 2.128 2.128 ↓ 0.0 0 266

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

  • 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))
241. 6,503.101 6,503.101 ↑ 1.0 1,740 1

Materialize (cost=57,193.27..61,543.36 rows=1,796 width=48) (actual time=5,344.613..6,503.101 rows=1,740 loops=1)