explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eCjZ

Settings
# exclusive inclusive rows x rows loops node
1. 214.445 459,585.198 ↓ 4.8 2,053 1

GroupAggregate (cost=1,351,308.34..1,351,565.61 rows=427 width=467) (actual time=459,367.060..459,585.198 rows=2,053 loops=1)

  • Group Key: chwlist.branch_uuid, chwlist.branch_name, chwlist.supervisor_uuid, chwlist.supervisor_name, 'multiple'::text, 'multiple'::text, 'multiple'::text, 'multiple'::text, (date(chwlist.interval_start)), chwlist.interval_number, ((supervisor_chp_alloc.count)::integer), (((now())::date - (a.latest_replication_date)::date))
  • Functions: 793
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 632.767 ms, Inlining 515.100 ms, Optimization 34873.390 ms, Emission 18687.867 ms, Total 54709.125 ms
2. 361.333 459,370.753 ↓ 19.8 8,438 1

Sort (cost=1,351,308.34..1,351,309.41 rows=427 width=635) (actual time=459,366.393..459,370.753 rows=8,438 loops=1)

  • Sort Key: chwlist.branch_uuid, chwlist.branch_name, chwlist.supervisor_uuid, chwlist.supervisor_name, (date(chwlist.interval_start)), chwlist.interval_number, ((supervisor_chp_alloc.count)::integer), (((now())::date - (a.latest_replication_date)::date))
  • Sort Method: quicksort Memory: 5419kB
3. 92.413 459,009.420 ↓ 19.8 8,438 1

Merge Left Join (cost=1,313,729.90..1,351,289.69 rows=427 width=635) (actual time=142,261.263..459,009.420 rows=8,438 loops=1)

  • Merge Cond: (chwlist.chw_uuid = a_1.chw)
  • Join Filter: (chwlist.interval_number = (0))
4. 62.674 423,403.382 ↓ 19.8 8,438 1

Merge Left Join (cost=1,123,612.06..1,160,710.22 rows=427 width=547) (actual time=109,593.170..423,403.382 rows=8,438 loops=1)

  • Merge Cond: (chwlist.chw_uuid = useview_households_visited.chw)
  • Join Filter: (chwlist.interval_number = (0))
  • Rows Removed by Join Filter: 1686
5. 84.276 417,016.302 ↓ 19.8 8,438 1

Merge Join (cost=999,881.95..1,033,559.38 rows=427 width=543) (actual time=105,528.712..417,016.302 rows=8,438 loops=1)

  • Merge Cond: (chwlist.chw_uuid = a.chw_uuid)
6. 25,441.214 412,302.426 ↓ 602.7 8,438 1

Nested Loop (cost=938,030.70..971,674.56 rows=14 width=570) (actual time=100,920.643..412,302.426 rows=8,438 loops=1)

  • Join Filter: (chwlist.chw_uuid = useview_chp_sync.chw_uuid)
  • Rows Removed by Join Filter: 65774074
7. 118.957 328,016.740 ↓ 12,216.0 12,216 1

Merge Left Join (cost=881,948.17..915,400.83 rows=1 width=535) (actual time=96,887.895..328,016.740 rows=12,216 loops=1)

  • Merge Cond: (chwlist.chw_uuid = visit_1.chw)
  • Join Filter: ((0) = chwlist.interval_number)
8. 10,416.097 327,467.279 ↓ 12,216.0 12,216 1

Nested Loop Left Join (cost=877,718.90..910,822.06 rows=1 width=527) (actual time=96,612.534..327,467.279 rows=12,216 loops=1)

  • Join Filter: ((((pat_clinic.doc #>> '{fields,inputs,contact,_id}'::text[])) = chwlist.chw_area_uuid) AND ((0) = chwlist.interval_number))
  • Rows Removed by Join Filter: 28549882
9. 216.778 267,991.726 ↓ 12,216.0 12,216 1

Nested Loop Left Join (cost=562,419.45..595,486.89 rows=1 width=559) (actual time=95,784.276..267,991.726 rows=12,216 loops=1)

  • Join Filter: (chwlist.interval_number = 0)
10. 1,412.883 106,890.228 ↓ 12,216.0 12,216 1

Nested Loop Left Join (cost=562,419.17..595,014.34 rows=1 width=555) (actual time=95,782.946..106,890.228 rows=12,216 loops=1)

  • Join Filter: ((chwlist.chw_uuid = meta.chw) AND (chwlist.interval_number = (0)))
  • Rows Removed by Join Filter: 3108600
11. 362.346 101,617.089 ↓ 12,216.0 12,216 1

Nested Loop (cost=555,371.69..587,905.92 rows=1 width=547) (actual time=95,417.217..101,617.089 rows=12,216 loops=1)

  • Join Filter: (chwlist.supervisor_uuid = supervisor_chp_alloc.supervisor_uuid)
  • Rows Removed by Join Filter: 836722
12. 127.772 100,277.463 ↓ 172.1 12,216 1

Merge Left Join (cost=555,123.66..587,656.80 rows=71 width=539) (actual time=94,891.360..100,277.463 rows=12,216 loops=1)

  • Merge Cond: (chwlist.chw_uuid = useview_assessment.chw)
  • Join Filter: (chwlist.interval_number = (0))
13. 107.357 93,072.834 ↓ 172.1 12,216 1

Merge Left Join (cost=464,195.32..464,198.43 rows=71 width=323) (actual time=92,920.365..93,072.834 rows=12,216 loops=1)

  • Merge Cond: ((chwlist.chw_uuid = pncvisit.chw_uuid) AND (chwlist.interval_number = pncvisit.interval_number))
14. 177.056 92,886.640 ↓ 172.1 12,216 1

Sort (cost=463,823.79..463,823.97 rows=71 width=275) (actual time=92,843.392..92,886.640 rows=12,216 loops=1)

  • Sort Key: chwlist.chw_uuid, chwlist.interval_number
  • Sort Method: quicksort Memory: 6096kB
15. 12.248 92,709.584 ↓ 172.1 12,216 1

Hash Right Join (cost=456,020.44..463,821.61 rows=71 width=275) (actual time=92,692.124..92,709.584 rows=12,216 loops=1)

  • Hash Cond: ((cmeta.parent_uuid = chwlist.chw_area_uuid) AND ((0) = chwlist.interval_number))
16. 1,212.247 8,879.996 ↑ 45.0 6,300 1

HashAggregate (cost=116,374.50..119,211.03 rows=283,653 width=56) (actual time=8,874.755..8,879.996 rows=6,300 loops=1)

  • Group Key: cmeta.parent_uuid, 0
17. 1,893.533 7,667.749 ↓ 1.0 854,028 1

Hash Left Join (cost=60,839.25..107,984.04 rows=839,046 width=76) (actual time=4,702.954..7,667.749 rows=854,028 loops=1)

  • Hash Cond: (cmeta.uuid = useview_family_survey.family_id)
18. 1,071.782 1,071.782 ↓ 1.0 854,028 1

Index Scan using contactview_metadata_type on contactview_metadata cmeta (cost=0.43..44,942.72 rows=839,046 width=72) (actual time=0.047..1,071.782 rows=854,028 loops=1)

  • Index Cond: (type = 'clinic'::text)
19. 726.391 4,702.434 ↓ 1.2 530,662 1

Hash (cost=55,147.72..55,147.72 rows=455,288 width=36) (actual time=4,702.434..4,702.434 rows=530,662 loops=1)

  • Buckets: 1048576 (originally 524288) Batches: 1 (originally 1) Memory Usage: 43950kB
20. 1,682.769 3,976.043 ↓ 1.2 530,662 1

HashAggregate (cost=46,041.97..50,594.85 rows=455,288 width=36) (actual time=3,495.094..3,976.043 rows=530,662 loops=1)

  • Group Key: useview_family_survey.family_id
21. 2,293.274 2,293.274 ↓ 1.0 703,676 1

Seq Scan on useview_family_survey (cost=0.00..44,332.33 rows=683,854 width=36) (actual time=0.043..2,293.274 rows=703,676 loops=1)

  • Filter: is_equity_survey
  • Rows Removed by Filter: 428135
22. 56.838 83,817.340 ↓ 1,221.6 12,216 1

Hash (cost=339,645.79..339,645.79 rows=10 width=223) (actual time=83,817.340..83,817.340 rows=12,216 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2965kB
23. 50.591 83,760.502 ↓ 1,221.6 12,216 1

Merge Left Join (cost=339,526.23..339,645.79 rows=10 width=223) (actual time=68,117.845..83,760.502 rows=12,216 loops=1)

  • Merge Cond: (chwlist.chw_uuid = assess.chw)
  • Join Filter: (chwlist.interval_number = (0))
24. 8,827.691 82,033.424 ↓ 1,221.6 12,216 1

Nested Loop Left Join (cost=278,740.60..278,742.39 rows=10 width=215) (actual time=66,468.519..82,033.424 rows=12,216 loops=1)

  • Join Filter: ((chwlist.chw_area_uuid = familyreg.chw_area_uuid) AND (chwlist.interval_number = familyreg.interval_number))
  • Rows Removed by Join Filter: 22953032
25. 31.118 64,422.429 ↓ 1,221.6 12,216 1

Merge Left Join (cost=217,016.95..217,018.53 rows=10 width=207) (actual time=64,375.314..64,422.429 rows=12,216 loops=1)

  • Merge Cond: ((chwlist.chw_uuid = eddnopnc.chw_uuid) AND (chwlist.interval_number = eddnopnc.interval_number))
26. 168.779 54,494.449 ↓ 1,221.6 12,216 1

Sort (cost=2,481.54..2,481.57 rows=10 width=199) (actual time=54,479.537..54,494.449 rows=12,216 loops=1)

  • Sort Key: chwlist.chw_uuid, chwlist.interval_number
  • Sort Method: quicksort Memory: 3629kB
27. 5.708 54,325.670 ↓ 1,221.6 12,216 1

Hash Right Join (cost=2,429.25..2,481.37 rows=10 width=199) (actual time=54,318.908..54,325.670 rows=12,216 loops=1)

  • Hash Cond: ((visit.chw = chwlist.chw_uuid) AND ((0) = chwlist.interval_number))
28. 17.822 29.138 ↓ 1.5 2,803 1

HashAggregate (cost=2,122.95..2,141.90 rows=1,895 width=47) (actual time=28.056..29.138 rows=2,803 loops=1)

  • Group Key: visit.chw, 0
29. 11.316 11.316 ↓ 3.9 8,960 1

Index Scan using useview_pregnancy_reported on useview_pregnancy visit (cost=0.44..2,105.76 rows=2,292 width=39) (actual time=0.183..11.316 rows=8,960 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
30. 19.299 54,290.824 ↓ 1,221.6 12,216 1

Hash (cost=306.15..306.15 rows=10 width=187) (actual time=54,290.824..54,290.824 rows=12,216 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2681kB
31. 10.901 54,271.525 ↓ 1,221.6 12,216 1

Subquery Scan on chwlist (cost=272.20..306.15 rows=10 width=187) (actual time=54,256.994..54,271.525 rows=12,216 loops=1)

32. 3.690 54,260.624 ↓ 1,221.6 12,216 1

Hash Join (cost=272.20..306.05 rows=10 width=251) (actual time=54,256.992..54,260.624 rows=12,216 loops=1)

  • Hash Cond: ((row_number() OVER (?)) = (row_number() OVER (?)))
33. 0.021 53,898.606 ↑ 500.0 2 1

WindowAgg (cost=59.84..79.84 rows=1,000 width=24) (actual time=53,898.597..53,898.606 rows=2 loops=1)

34. 0.025 53,898.585 ↑ 500.0 2 1

Sort (cost=59.84..62.34 rows=1,000 width=8) (actual time=53,898.584..53,898.585 rows=2 loops=1)

  • Sort Key: generate_series.generate_series
  • Sort Method: quicksort Memory: 25kB
35. 53,898.560 53,898.560 ↑ 500.0 2 1

Function Scan on generate_series (cost=0.01..10.01 rows=1,000 width=8) (actual time=53,898.559..53,898.560 rows=2 loops=1)

36. 7.856 358.328 ↓ 6,108.0 12,216 1

Hash (cost=212.34..212.34 rows=2 width=187) (actual time=358.328..358.328 rows=12,216 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2687kB
37. 3.604 350.472 ↓ 6,108.0 12,216 1

Nested Loop (cost=1.60..212.34 rows=2 width=187) (actual time=0.192..350.472 rows=12,216 loops=1)

38. 4.616 310.220 ↓ 6,108.0 6,108 1

Nested Loop Anti Join (cost=1.55..212.21 rows=1 width=175) (actual time=0.158..310.220 rows=6,108 loops=1)

39. 10.248 252.803 ↓ 7,543.0 7,543 1

Nested Loop (cost=1.27..211.89 rows=1 width=175) (actual time=0.136..252.803 rows=7,543 loops=1)

40. 10.983 22.851 ↓ 7,576.0 7,576 1

Nested Loop (cost=0.71..209.47 rows=1 width=159) (actual time=0.094..22.851 rows=7,576 loops=1)

41. 0.084 0.084 ↓ 24.0 24 1

Index Scan using contactview_metadata_type on contactview_metadata cmd (cost=0.43..2.45 rows=1 width=52) (actual time=0.058..0.084 rows=24 loops=1)

  • Index Cond: (type = 'district_hospital'::text)
42. 11.784 11.784 ↑ 1.0 316 24

Index Scan using contactview_chp_branch_uuid on contactview_chp chp_1 (cost=0.28..203.87 rows=316 width=140) (actual time=0.022..0.491 rows=316 loops=24)

  • Index Cond: (branch_uuid = cmd.uuid)
43. 219.704 219.704 ↑ 1.0 1 7,576

Index Scan using contactview_metadata_uuid on contactview_metadata cmeta_1 (cost=0.56..2.42 rows=1 width=52) (actual time=0.029..0.029 rows=1 loops=7,576)

  • Index Cond: (uuid = chp_1.supervisor_uuid)
44. 52.801 52.801 ↓ 0.0 0 7,543

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

  • Index Cond: (chw = chp_1.uuid)
  • Heap Fetches: 0
45. 36.648 36.648 ↑ 1.0 2 6,108

WindowAgg (cost=0.06..0.09 rows=2 width=16) (actual time=0.003..0.006 rows=2 loops=6,108)

46. 0.000 0.000 ↑ 1.0 2 6,108

Sort (cost=0.06..0.06 rows=2 width=4) (actual time=0.000..0.000 rows=2 loops=6,108)

  • Sort Key: generate_series_1.generate_series
  • Sort Method: quicksort Memory: 25kB
47. 0.018 0.018 ↑ 1.0 2 1

Function Scan on generate_series generate_series_1 (cost=0.03..0.05 rows=2 width=4) (actual time=0.018..0.018 rows=2 loops=1)

48. 12.167 9,896.862 ↓ 9.5 1,898 1

Sort (cost=214,535.41..214,535.91 rows=200 width=47) (actual time=9,895.714..9,896.862 rows=1,898 loops=1)

  • Sort Key: eddnopnc.chw_uuid, eddnopnc.interval_number
  • Sort Method: quicksort Memory: 298kB
49. 0.345 9,884.695 ↓ 9.5 1,898 1

Subquery Scan on eddnopnc (cost=214,523.76..214,527.76 rows=200 width=47) (actual time=9,883.696..9,884.695 rows=1,898 loops=1)

50. 3.016 9,884.350 ↓ 9.5 1,898 1

HashAggregate (cost=214,523.76..214,525.76 rows=200 width=47) (actual time=9,883.692..9,884.350 rows=1,898 loops=1)

  • Group Key: p.chw, 0
51. 5.470 9,881.334 ↑ 504.3 3,084 1

Hash Left Join (cost=200,903.34..202,858.58 rows=1,555,357 width=39) (actual time=9,720.864..9,881.334 rows=3,084 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.new_edd) <> ''::text) THEN (max(pv.new_edd))::date ELSE max(p.edd) END) - ((max(useview_postnatal_care.reported)))::date)) > 60))
  • Rows Removed by Filter: 2851
52. 271.965 8,206.452 ↑ 1.2 5,935 1

HashAggregate (cost=178,164.00..180,028.80 rows=7,104 width=75) (actual time=8,051.276..8,206.452 rows=5,935 loops=1)

  • Group Key: p.chw, p.patient_id
  • Filter: ((max(p.edd) >= date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone)) AND CASE WHEN (date_trunc('day'::text, now()) = date_trunc('day'::text, now())) THEN (max(p.edd) <= (now() - '3 days'::interval)) ELSE (max(p.edd) <= (now() + '1 day'::interval)) END)
  • Rows Removed by Filter: 49650
53. 84.477 7,934.487 ↓ 1.5 63,662 1

Merge Anti Join (cost=176,690.33..177,311.52 rows=42,624 width=81) (actual time=7,812.689..7,934.487 rows=63,662 loops=1)

  • Merge Cond: (p.patient_id = useview_pregnancy_visit_1.patient_id)
54. 993.449 5,644.261 ↓ 1.7 80,559 1

Sort (cost=90,485.04..90,600.80 rows=46,304 width=81) (actual time=5,609.860..5,644.261 rows=80,559 loops=1)

  • Sort Key: p.patient_id
  • Sort Method: quicksort Memory: 14401kB
55. 429.445 4,650.812 ↓ 1.7 80,559 1

Hash Join (cost=72,022.44..86,896.75 rows=46,304 width=81) (actual time=3,618.665..4,650.812 rows=80,559 loops=1)

  • Hash Cond: (p.patient_id = pv.patient_id)
  • Join Filter: (p.reported < pv.reported)
  • Rows Removed by Join Filter: 830
56. 603.250 603.250 ↑ 1.0 348,743 1

Seq Scan on useview_pregnancy p (cost=0.00..13,958.56 rows=348,856 width=83) (actual time=0.022..603.250 rows=348,743 loops=1)

57. 102.217 3,618.117 ↑ 1.7 69,910 1

Hash (cost=70,549.20..70,549.20 rows=117,859 width=50) (actual time=3,618.117..3,618.117 rows=69,910 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 7341kB
58. 60.182 3,515.900 ↑ 1.7 69,910 1

Subquery Scan on pv (cost=68,555.76..70,549.20 rows=117,859 width=50) (actual time=3,308.648..3,515.900 rows=69,910 loops=1)

59. 73.734 3,455.718 ↑ 1.7 69,910 1

Unique (cost=68,555.76..69,370.61 rows=117,859 width=82) (actual time=3,308.629..3,455.718 rows=69,910 loops=1)

60. 1,878.810 3,381.984 ↓ 1.1 178,564 1

Sort (cost=68,555.76..68,963.19 rows=162,971 width=82) (actual time=3,308.622..3,381.984 rows=178,564 loops=1)

  • Sort Key: useview_pregnancy_visit.patient_id, useview_pregnancy_visit.reported DESC
  • Sort Method: quicksort Memory: 31255kB
61. 1,336.858 1,503.174 ↓ 1.1 178,564 1

Bitmap Heap Scan on useview_pregnancy_visit (cost=5,612.60..54,447.15 rows=162,971 width=82) (actual time=186.270..1,503.174 rows=178,564 loops=1)

  • Recheck Cond: ((reported > ('2020-01-01 00:00:00+00'::timestamp with time zone - '9 mons'::interval)) OR (reported > (now() - '9 mons'::interval)))
  • Filter: (((edd_updated = 'TRUE'::text) AND (reported > ('2020-01-01 00:00:00+00'::timestamp with time zone - '9 mons'::interval))) OR (reported > (now() - '9 mons'::interval)))
  • Rows Removed by Filter: 18992
  • Heap Blocks: exact=23595
62. 0.003 166.316 ↓ 0.0 0 1

BitmapOr (cost=5,612.60..5,612.60 rows=345,502 width=0) (actual time=166.316..166.316 rows=0 loops=1)

63. 101.948 101.948 ↓ 1.1 197,556 1

Bitmap Index Scan on useview_pregnancy_visit_reported_chw (cost=0.00..2,922.40 rows=182,530 width=0) (actual time=101.948..101.948 rows=197,556 loops=1)

  • Index Cond: (reported > ('2020-01-01 00:00:00+00'::timestamp with time zone - '9 mons'::interval))
64. 64.365 64.365 ↓ 1.1 178,564 1

Bitmap Index Scan on useview_pregnancy_visit_reported_chw (cost=0.00..2,608.71 rows=162,971 width=0) (actual time=64.365..64.365 rows=178,564 loops=1)

  • Index Cond: (reported > (now() - '9 mons'::interval))
65. 342.663 2,205.749 ↑ 1.5 15,830 1

Sort (cost=86,205.28..86,264.10 rows=23,525 width=44) (actual time=2,202.782..2,205.749 rows=15,830 loops=1)

  • Sort Key: useview_pregnancy_visit_1.patient_id, useview_pregnancy_visit_1.reported DESC
  • Sort Method: quicksort Memory: 1621kB
66. 3.943 1,863.086 ↑ 1.5 15,830 1

Append (cost=56,017.32..84,497.15 rows=23,525 width=44) (actual time=843.749..1,863.086 rows=15,830 loops=1)

67. 19.162 887.573 ↑ 1.4 15,791 1

Unique (cost=56,017.32..56,130.69 rows=21,690 width=44) (actual time=843.748..887.573 rows=15,791 loops=1)

68. 196.959 868.411 ↑ 1.4 16,358 1

Sort (cost=56,017.32..56,074.01 rows=22,674 width=44) (actual time=843.746..868.411 rows=16,358 loops=1)

  • Sort Key: useview_pregnancy_visit_1.patient_id
  • Sort Method: quicksort Memory: 1662kB
69. 496.252 671.452 ↑ 1.4 16,358 1

Bitmap Heap Scan on useview_pregnancy_visit useview_pregnancy_visit_1 (cost=5,542.45..54,377.00 rows=22,674 width=44) (actual time=180.741..671.452 rows=16,358 loops=1)

  • Recheck Cond: ((reported > ('2020-01-01 00:00:00+00'::timestamp with time zone - '9 mons'::interval)) OR (reported > (now() - '9 mons'::interval)))
  • Filter: (continue_follow_up <> 'yes'::text)
  • Rows Removed by Filter: 181198
  • Heap Blocks: exact=23595
70. 0.003 175.200 ↓ 0.0 0 1

BitmapOr (cost=5,542.45..5,542.45 rows=345,502 width=0) (actual time=175.200..175.200 rows=0 loops=1)

71. 108.397 108.397 ↓ 1.1 197,556 1

Bitmap Index Scan on useview_pregnancy_visit_reported_chw (cost=0.00..2,922.40 rows=182,530 width=0) (actual time=108.397..108.397 rows=197,556 loops=1)

  • Index Cond: (reported > ('2020-01-01 00:00:00+00'::timestamp with time zone - '9 mons'::interval))
72. 66.800 66.800 ↓ 1.1 178,564 1

Bitmap Index Scan on useview_pregnancy_visit_reported_chw (cost=0.00..2,608.71 rows=162,971 width=0) (actual time=66.800..66.800 rows=178,564 loops=1)

  • Index Cond: (reported > (now() - '9 mons'::interval))
73. 0.044 971.570 ↑ 47.1 39 1

Unique (cost=28,004.32..28,013.58 rows=1,835 width=44) (actual time=971.519..971.570 rows=39 loops=1)

74. 0.163 971.526 ↑ 33.7 55 1

Sort (cost=28,004.32..28,008.95 rows=1,852 width=44) (actual time=971.517..971.526 rows=55 loops=1)

  • Sort Key: useview_delivery_check.patient_id
  • Sort Method: quicksort Memory: 29kB
75. 971.363 971.363 ↑ 33.7 55 1

Seq Scan on useview_delivery_check (cost=0.00..27,903.80 rows=1,852 width=44) (actual time=779.200..971.363 rows=55 loops=1)

  • Filter: ((pregnancy_outcome = 'miscarriage'::text) AND ((reported > ('2020-01-01 00:00:00+00'::timestamp with time zone - '9 mons'::interval)) OR (reported > (now() - '9 mons'::interval))))
  • Rows Removed by Filter: 500116
76. 295.082 1,669.412 ↓ 1.3 163,418 1

Hash (cost=21,113.53..21,113.53 rows=130,064 width=44) (actual time=1,669.411..1,669.412 rows=163,418 loops=1)

  • Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 14815kB
77. 415.619 1,374.330 ↓ 1.3 163,418 1

GroupAggregate (cost=0.42..19,812.89 rows=130,064 width=44) (actual time=6.806..1,374.330 rows=163,418 loops=1)

  • Group Key: useview_postnatal_care.patient_id
78. 958.711 958.711 ↑ 1.0 185,248 1

Index Scan using useview_postnatal_follow_up_count_patient_id on useview_postnatal_care (cost=0.42..17,583.62 rows=185,726 width=44) (actual time=0.050..958.711 rows=185,248 loops=1)

  • Index Cond: (follow_up_count = '1'::text)
79. 6,669.488 8,783.304 ↓ 1,879.0 1,879 12,216

Materialize (cost=61,723.65..61,723.69 rows=1 width=48) (actual time=0.171..0.719 rows=1,879 loops=12,216)

80. 8.580 2,113.816 ↓ 2,317.0 2,317 1

Subquery Scan on familyreg (cost=61,723.65..61,723.68 rows=1 width=48) (actual time=2,081.682..2,113.816 rows=2,317 loops=1)

81. 20.559 2,105.236 ↓ 2,317.0 2,317 1

GroupAggregate (cost=61,723.65..61,723.67 rows=1 width=48) (actual time=2,081.679..2,105.236 rows=2,317 loops=1)

  • Group Key: contactview_metadata.parent_uuid, 0
82. 96.351 2,084.677 ↓ 14,718.0 14,718 1

Sort (cost=61,723.65..61,723.66 rows=1 width=40) (actual time=2,081.650..2,084.677 rows=14,718 loops=1)

  • Sort Key: contactview_metadata.parent_uuid
  • Sort Method: quicksort Memory: 1534kB
83. 1,988.326 1,988.326 ↓ 14,718.0 14,718 1

Index Scan using contactview_metadata_type on contactview_metadata (cost=0.43..61,723.64 rows=1 width=40) (actual time=1,892.762..1,988.326 rows=14,718 loops=1)

  • Index Cond: (type = 'clinic'::text)
  • Filter: ((reported >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 839310
84. 3.878 1,676.487 ↑ 1.4 2,358 1

Materialize (cost=60,785.63..60,893.05 rows=3,305 width=47) (actual time=1,649.316..1,676.487 rows=2,358 loops=1)

85. 14.923 1,672.609 ↑ 1.4 2,358 1

GroupAggregate (cost=60,785.63..60,851.73 rows=3,305 width=47) (actual time=1,649.313..1,672.609 rows=2,358 loops=1)

  • Group Key: assess.chw, 0
86. 96.740 1,657.686 ↓ 5.0 16,633 1

Sort (cost=60,785.63..60,793.90 rows=3,305 width=39) (actual time=1,649.285..1,657.686 rows=16,633 loops=1)

  • Sort Key: assess.chw
  • Sort Method: quicksort Memory: 2068kB
87. 18.563 1,560.946 ↓ 5.0 16,633 1

Nested Loop (cost=7,524.44..60,592.45 rows=3,305 width=39) (actual time=434.752..1,560.946 rows=16,633 loops=1)

88. 139.608 572.783 ↓ 1.1 19,392 1

Bitmap Heap Scan on useview_assessment assess (cost=7,524.01..24,854.56 rows=17,787 width=79) (actual time=434.690..572.783 rows=19,392 loops=1)

  • Recheck Cond: ((reported >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone) AND (patient_age_in_years < 5) AND (referral_follow_up = 'true'::text))
  • Heap Blocks: exact=7215
89. 2.382 433.175 ↓ 0.0 0 1

BitmapAnd (cost=7,524.01..7,524.01 rows=17,787 width=0) (actual time=433.175..433.175 rows=0 loops=1)

90. 197.256 197.256 ↓ 1.3 131,421 1

Bitmap Index Scan on useview_assessment_reported_age_uuid (cost=0.00..2,563.11 rows=104,997 width=0) (actual time=197.256..197.256 rows=131,421 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone) AND (patient_age_in_years < 5))
91. 233.537 233.537 ↓ 1.0 485,300 1

Bitmap Index Scan on useview_assessment_referral_follow_up (cost=0.00..4,951.76 rows=483,111 width=0) (actual time=233.536..233.537 rows=485,300 loops=1)

  • Index Cond: (referral_follow_up = 'true'::text)
92. 969.600 969.600 ↑ 1.0 1 19,392

Index Scan using useview_assessment_follow_up_form_source_id on useview_assessment_follow_up follow_up (cost=0.43..2.00 rows=1 width=44) (actual time=0.048..0.050 rows=1 loops=19,392)

  • 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
93. 9.058 78.837 ↓ 6.9 2,368 1

Sort (cost=371.53..372.39 rows=342 width=87) (actual time=76.929..78.837 rows=2,368 loops=1)

  • Sort Key: pncvisit.chw_uuid, pncvisit.interval_number
  • Sort Method: quicksort Memory: 430kB
94. 0.457 69.779 ↓ 6.9 2,368 1

Subquery Scan on pncvisit (cost=350.30..357.14 rows=342 width=87) (actual time=56.140..69.779 rows=2,368 loops=1)

95. 45.062 69.322 ↓ 6.9 2,368 1

HashAggregate (cost=350.30..353.72 rows=342 width=87) (actual time=56.136..69.322 rows=2,368 loops=1)

  • Group Key: useview_postnatal_care_1.chw, 0
96. 24.260 24.260 ↓ 14.4 5,090 1

Index Scan using useview_postnatal_care_count_reported_uuid on useview_postnatal_care useview_postnatal_care_1 (cost=0.44..322.06 rows=353 width=61) (actual time=0.076..24.260 rows=5,090 loops=1)

  • Index Cond: ((follow_up_count = '1'::text) AND (reported >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
97. 18.263 7,076.857 ↑ 1.5 3,546 1

Materialize (cost=90,928.34..123,420.13 rows=5,488 width=255) (actual time=1,970.982..7,076.857 rows=3,546 loops=1)

98. 4,990.065 7,058.594 ↑ 1.5 3,546 1

GroupAggregate (cost=90,928.34..123,351.53 rows=5,488 width=279) (actual time=1,970.977..7,058.594 rows=3,546 loops=1)

  • Group Key: useview_assessment.chw, 0
99. 1,265.584 2,068.529 ↓ 1.2 140,389 1

Sort (cost=90,928.34..91,212.27 rows=113,573 width=127) (actual time=1,970.312..2,068.529 rows=140,389 loops=1)

  • Sort Key: useview_assessment.chw
  • Sort Method: quicksort Memory: 41443kB
100. 802.945 802.945 ↓ 1.2 140,389 1

Index Scan using useview_assessment_reported on useview_assessment (cost=0.45..81,392.04 rows=113,573 width=127) (actual time=0.095..802.945 rows=140,389 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
101. 449.690 977.280 ↓ 69.0 69 12,216

Materialize (cost=248.03..248.06 rows=1 width=43) (actual time=0.044..0.080 rows=69 loops=12,216)

102. 0.034 527.590 ↓ 135.0 135 1

Subquery Scan on supervisor_chp_alloc (cost=248.03..248.06 rows=1 width=43) (actual time=525.499..527.590 rows=135 loops=1)

103. 1.319 527.556 ↓ 135.0 135 1

GroupAggregate (cost=248.03..248.05 rows=1 width=43) (actual time=525.497..527.556 rows=135 loops=1)

  • Group Key: contactview_hierarchy.supervisor_uuid
104. 21.254 526.237 ↓ 6,108.0 6,108 1

Sort (cost=248.03..248.03 rows=1 width=71) (actual time=525.460..526.237 rows=6,108 loops=1)

  • Sort Key: contactview_hierarchy.supervisor_uuid
  • Sort Method: quicksort Memory: 1051kB
105. 5.285 504.983 ↓ 6,108.0 6,108 1

Subquery Scan on contactview_hierarchy (cost=247.98..248.02 rows=1 width=71) (actual time=496.372..504.983 rows=6,108 loops=1)

  • Filter: (NOT (hashed SubPlan 7))
  • Rows Removed by Filter: 1435
106. 3.910 313.975 ↓ 7,543.0 7,543 1

Group (cost=211.90..211.93 rows=1 width=202) (actual time=308.723..313.975 rows=7,543 loops=1)

  • Group Key: cmd_1.uuid, cmd_1.name, chp_2.supervisor_uuid, chp_2.area_uuid, cmeta_2.name, chp_2.uuid, chp_2.name, chp_2.phone
107. 116.605 310.065 ↓ 7,543.0 7,543 1

Sort (cost=211.90..211.91 rows=1 width=202) (actual time=308.700..310.065 rows=7,543 loops=1)

  • Sort Key: cmd_1.uuid, cmd_1.name, chp_2.supervisor_uuid, chp_2.area_uuid, cmeta_2.name, chp_2.uuid, chp_2.name, chp_2.phone
  • Sort Method: quicksort Memory: 2196kB
108. 3.876 193.460 ↓ 7,543.0 7,543 1

Nested Loop (cost=1.27..211.89 rows=1 width=202) (actual time=0.105..193.460 rows=7,543 loops=1)

109. 21.850 30.488 ↓ 7,576.0 7,576 1

Nested Loop (cost=0.71..209.47 rows=1 width=186) (actual time=0.078..30.488 rows=7,576 loops=1)

110. 0.070 0.070 ↓ 24.0 24 1

Index Scan using contactview_metadata_type on contactview_metadata cmd_1 (cost=0.43..2.45 rows=1 width=52) (actual time=0.044..0.070 rows=24 loops=1)

  • Index Cond: (type = 'district_hospital'::text)
111. 8.568 8.568 ↑ 1.0 316 24

Index Scan using contactview_chp_branch_uuid on contactview_chp chp_2 (cost=0.28..203.87 rows=316 width=167) (actual time=0.019..0.357 rows=316 loops=24)

  • Index Cond: (branch_uuid = cmd_1.uuid)
112. 159.096 159.096 ↑ 1.0 1 7,576

Index Scan using contactview_metadata_uuid on contactview_metadata cmeta_2 (cost=0.56..2.42 rows=1 width=52) (actual time=0.021..0.021 rows=1 loops=7,576)

  • Index Cond: (uuid = chp_2.supervisor_uuid)
113.          

SubPlan (for Subquery Scan)

114. 185.723 185.723 ↑ 1.0 1,606 1

Seq Scan on chp_muting chp_muting_1 (cost=0.00..32.06 rows=1,606 width=36) (actual time=185.325..185.723 rows=1,606 loops=1)

115. 3,495.000 3,860.256 ↑ 6.8 255 12,216

HashAggregate (cost=7,047.48..7,064.89 rows=1,741 width=47) (actual time=0.033..0.316 rows=255 loops=12,216)

  • Group Key: meta.chw, 0
116. 21.269 365.256 ↑ 4.4 467 1

Bitmap Heap Scan on form_metadata meta (cost=4,902.60..7,031.94 rows=2,072 width=39) (actual time=344.071..365.256 rows=467 loops=1)

  • Recheck Cond: ((formname = 'community_event'::text) AND (reported >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Heap Blocks: exact=310
117. 1.203 343.987 ↓ 0.0 0 1

BitmapAnd (cost=4,902.60..4,902.60 rows=2,072 width=0) (actual time=343.987..343.987 rows=0 loops=1)

118. 39.485 39.485 ↑ 1.1 58,137 1

Bitmap Index Scan on form_metadata_formname (cost=0.00..719.57 rows=61,735 width=0) (actual time=39.485..39.485 rows=58,137 loops=1)

  • Index Cond: (formname = 'community_event'::text)
119. 303.299 303.299 ↓ 1.8 579,157 1

Bitmap Index Scan on form_metadata_reported (cost=0.00..4,181.74 rows=322,729 width=0) (actual time=303.299..303.299 rows=579,157 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
120. 719.296 160,884.720 ↑ 1.0 1 12,216

Index Scan using contactview_chp_uuid on contactview_chp chp (cost=0.28..472.54 rows=1 width=40) (actual time=13.170..13.170 rows=1 loops=12,216)

  • Index Cond: (uuid = chwlist.chw_uuid)
121.          

SubPlan (for Index Scan)

122. 114.356 101,429.448 ↑ 4.0 1 12,216

Nested Loop Semi Join (cost=0.56..1,735.47 rows=4 width=0) (actual time=8.303..8.303 rows=1 loops=12,216)

  • Join Filter: (meta_1.formname = "*VALUES*_1".column1)
  • Rows Removed by Join Filter: 7
123. 101,282.856 101,282.856 ↑ 18.7 3 12,216

Index Scan using form_metadata_chw on form_metadata meta_1 (cost=0.56..1,732.91 rows=56 width=14) (actual time=8.271..8.291 rows=3 loops=12,216)

  • Index Cond: (chw = chp.uuid)
  • Filter: ((reported >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 1397
124. 32.231 32.236 ↑ 1.0 3 32,236

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

125. 0.005 0.005 ↑ 1.0 3 1

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

126. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=6,778.53..42,099.75 rows=22,004 width=35) (never executed)

127. 0.000 0.000 ↓ 0.0 0

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

  • Group Key: "*VALUES*_2".column1
128. 0.000 0.000 ↓ 0.0 0

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

129. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on form_metadata meta_2 (cost=6,778.49..13,959.87 rows=7,335 width=49) (never executed)

  • Recheck Cond: ((formname = "*VALUES*_2".column1) AND (reported >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
130. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=6,778.49..6,778.49 rows=7,335 width=0) (never executed)

131. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on form_metadata_formname (cost=0.00..2,513.98 rows=218,545 width=0) (never executed)

  • Index Cond: (formname = "*VALUES*_2".column1)
132. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on form_metadata_reported (cost=0.00..4,181.74 rows=322,729 width=0) (never executed)

  • Index Cond: ((reported >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
133. 3,848.040 3,848.040 ↓ 0.0 0 5,090

Index Scan using contactview_metadata_parent_uuid on contactview_metadata clinic (cost=0.56..14.00 rows=1 width=0) (actual time=0.756..0.756 rows=0 loops=5,090)

  • Index Cond: (parent_uuid = chp.area_uuid)
  • Filter: ((reported >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 80
134. 0.000 0.000 ↓ 0.0 0

Seq Scan on contactview_metadata clinic_1 (cost=0.00..166,759.30 rows=1 width=36) (never executed)

  • Filter: ((reported >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
135. 54,887.936 54,887.936 ↓ 0.0 0 5,056

Index Scan using useview_postnatal_reported_chw_patient_id on useview_postnatal_care pnc (cost=0.44..21.95 rows=1 width=0) (actual time=10.856..10.856 rows=0 loops=5,056)

  • Index Cond: ((reported >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone) AND (chw = chp.uuid))
  • Filter: (follow_up_count = '1'::text)
  • Rows Removed by Filter: 0
136. 0.000 0.000 ↓ 0.0 0

Index Scan using useview_postnatal_care_count_reported_uuid on useview_postnatal_care pnc_1 (cost=0.44..322.06 rows=353 width=35) (never executed)

  • Index Cond: ((follow_up_count = '1'::text) AND (reported >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
137. 31,199.664 49,059.456 ↓ 11.7 2,338 12,216

Group (cost=315,299.45..315,330.17 rows=200 width=40) (actual time=0.079..4.016 rows=2,338 loops=12,216)

  • Group Key: ((pat_clinic.doc #>> '{fields,inputs,contact,_id}'::text[])), 0
138. 17,068.013 17,859.792 ↑ 1.4 2,859 12,216

Sort (cost=315,299.45..315,309.52 rows=4,029 width=36) (actual time=0.070..1.462 rows=2,859 loops=12,216)

  • Sort Key: ((pat_clinic.doc #>> '{fields,inputs,contact,_id}'::text[]))
  • Sort Method: quicksort Memory: 396kB
139. 329.270 791.779 ↑ 1.1 3,835 1

Nested Loop (cost=0.46..315,058.19 rows=4,029 width=36) (actual time=0.328..791.779 rows=3,835 loops=1)

140. 0.004 0.013 ↑ 1.0 2 1

Unique (cost=0.04..0.04 rows=2 width=32) (actual time=0.008..0.013 rows=2 loops=1)

141. 0.007 0.009 ↑ 1.0 2 1

Sort (cost=0.04..0.04 rows=2 width=32) (actual time=0.007..0.009 rows=2 loops=1)

  • Sort Key: "*VALUES*".column1
  • Sort Method: quicksort Memory: 25kB
142. 0.002 0.002 ↑ 1.0 2 1

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

143. 1.042 462.496 ↑ 164.8 1,918 2

Append (cost=0.42..154,363.58 rows=316,046 width=856) (actual time=1.263..231.248 rows=1,918 loops=2)

144. 0.026 0.026 ↓ 0.0 0 2

Index Scan using pat_clinic_expr_idx9 on pat_clinic (cost=0.42..2.97 rows=1 width=565) (actual time=0.013..0.013 rows=0 loops=2)

  • Index Cond: ((doc ->> 'form'::text) = "*VALUES*".column1)
  • Filter: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
145. 0.000 0.000 ↓ 0.0 0

Seq Scan on pat_data_record_ancr (cost=0.00..2.08 rows=1 width=32) (never executed)

  • Filter: (("*VALUES*".column1 = (doc ->> 'form'::text)) AND (f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
146. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_ancv_f_cast_isots_idx on pat_data_record_ancv (cost=0.14..2.17 rows=1 width=32) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
147. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_anc_follow_up_f_cast_isots_idx on pat_data_record_anc_follow_up (cost=0.43..3,020.26 rows=3,207 width=1,440) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
148. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_assessment_f_cast_isots_idx on pat_data_record_assessment (cost=0.45..28,619.06 rows=55,634 width=31) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
149. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_assessment_cbds_f_cast_isots_idx on pat_data_record_assessment_cbds (cost=0.30..105.45 rows=455 width=18) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
150. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_assessment_follow_up_f_cast_isots_idx on pat_data_record_assessment_follow_up (cost=0.44..44,313.09 rows=48,807 width=1,153) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
151. 458.468 458.468 ↓ 2.0 3,644 1

Index Scan using pat_data_record_chp_visit_f_cast_isots_idx on pat_data_record_chp_visit (cost=0.31..651.83 rows=1,818 width=20) (actual time=0.226..458.468 rows=3,644 loops=1)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
152. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_community_event_f_cast_isots_idx on pat_data_record_community_event (cost=0.30..85.28 rows=82 width=905) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
153. 0.588 2.774 ↓ 191.0 191 1

Bitmap Heap Scan on pat_data_record_community_events (cost=4.11..5.65 rows=1 width=1,307) (actual time=2.244..2.774 rows=191 loops=1)

  • Recheck Cond: (((doc ->> 'form'::text) = "*VALUES*".column1) AND (f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Heap Blocks: exact=49
154. 0.147 2.186 ↓ 0.0 0 1

BitmapAnd (cost=4.11..4.11 rows=1 width=0) (actual time=2.186..2.186 rows=0 loops=1)

155. 2.010 2.010 ↓ 196.9 14,175 1

Bitmap Index Scan on pat_data_record_community_events_expr_idx9 (cost=0.00..1.82 rows=72 width=0) (actual time=2.009..2.010 rows=14,175 loops=1)

  • Index Cond: ((doc ->> 'form'::text) = "*VALUES*".column1)
156. 0.029 0.029 ↓ 2.7 191 1

Bitmap Index Scan on pat_data_record_community_events_f_cast_isots_idx (cost=0.00..2.02 rows=72 width=0) (actual time=0.029..0.029 rows=191 loops=1)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
157. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_death_report_f_cast_isots_idx on pat_data_record_death_report (cost=0.16..19.22 rows=58 width=1,594) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
158. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_death_report_approval_request_f_cast_isots_idx on pat_data_record_death_report_approval_request (cost=0.16..12.73 rows=56 width=1,041) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
159. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_death_report_confirmation_f_cast_isots_idx on pat_data_record_death_report_confirmation (cost=0.16..10.31 rows=30 width=1,925) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
160. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_delivery_check_f_cast_isots_idx on pat_data_record_delivery_check (cost=0.44..7,411.20 rows=7,793 width=1,831) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
161. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_ecd_assessment_f_cast_isots_idx on pat_data_record_ecd_assessment (cost=0.29..12.62 rows=14 width=18) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
162. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_ecd_follow_up_f_cast_isots_idx on pat_data_record_ecd_follow_up (cost=0.29..15.63 rows=14 width=1,734) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
163. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_family_survey_f_cast_isots_idx on pat_data_record_family_survey (cost=0.44..8,174.26 rows=8,746 width=332) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
164. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_fp_follow_up_long_term_f_cast_isots_idx on pat_data_record_fp_follow_up_long_term (cost=0.30..449.47 rows=1,074 width=1,723) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
165. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_fp_follow_up_prospective_f_cast_isots_idx on pat_data_record_fp_follow_up_prospective (cost=0.30..1,630.63 rows=7,136 width=373) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
166. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_fp_follow_up_refill_f_cast_isots_idx on pat_data_record_fp_follow_up_refill (cost=0.30..473.96 rows=4,675 width=173) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
167. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_fp_follow_up_short_term_f_cast_isots_idx on pat_data_record_fp_follow_up_short_term (cost=0.30..2,637.21 rows=3,422 width=1,493) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
168. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_fp_visit_f_cast_isots_idx on pat_data_record_fp_visit (cost=0.31..1,262.20 rows=11,604 width=107) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
169. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_home_visit_f_cast_isots_idx on pat_data_record_home_visit (cost=0.44..28,161.93 rows=113,055 width=1,281) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
170. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_household_mute_f_cast_isots_idx on pat_data_record_household_mute (cost=0.29..567.22 rows=1,997 width=1,768) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
171. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_household_mute_task_f_cast_isots_idx on pat_data_record_household_mute_task (cost=0.29..204.07 rows=726 width=1,873) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
172. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on pat_data_record_hub_spoke (cost=3.43..4.97 rows=1 width=1,190) (never executed)

  • Recheck Cond: (((doc ->> 'form'::text) = "*VALUES*".column1) AND (f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
173. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=3.43..3.43 rows=1 width=0) (never executed)

174. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on pat_data_record_hub_spoke_expr_idx9 (cost=0.00..1.54 rows=34 width=0) (never executed)

  • Index Cond: ((doc ->> 'form'::text) = "*VALUES*".column1)
175. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on pat_data_record_hub_spoke_f_cast_isots_idx (cost=0.00..1.64 rows=34 width=0) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
176. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_immunization_follow_up_f_cast_isots_idx on pat_data_record_immunization_follow_up (cost=0.31..749.02 rows=747 width=1,287) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
177. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_immunization_ug_f_cast_isots_idx on pat_data_record_immunization_ug (cost=0.31..5,461.62 rows=17,849 width=1,787) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
178. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_immunization_ug_follow_up_f_cast_isots_idx on pat_data_record_immunization_ug_follow_up (cost=0.30..1,301.09 rows=5,126 width=1,493) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
179. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on pat_data_record_it_support (cost=3.42..4.96 rows=1 width=1,005) (never executed)

  • Recheck Cond: (((doc ->> 'form'::text) = "*VALUES*".column1) AND (f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
180. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=3.42..3.42 rows=1 width=0) (never executed)

181. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on pat_data_record_it_support_expr_idx9 (cost=0.00..1.53 rows=33 width=0) (never executed)

  • Index Cond: ((doc ->> 'form'::text) = "*VALUES*".column1)
182. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on pat_data_record_it_support_f_cast_isots_idx (cost=0.00..1.63 rows=33 width=0) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
183. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on pat_data_record_mini_hub (cost=2.90..4.44 rows=1 width=1,218) (never executed)

  • Recheck Cond: (((doc ->> 'form'::text) = "*VALUES*".column1) AND (f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
184. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=2.90..2.90 rows=1 width=0) (never executed)

185. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on pat_data_record_mini_hub_expr_idx9 (cost=0.00..1.31 rows=5 width=0) (never executed)

  • Index Cond: ((doc ->> 'form'::text) = "*VALUES*".column1)
186. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on pat_data_record_mini_hub_f_cast_isots_idx (cost=0.00..1.34 rows=5 width=0) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
187. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_muac_follow_up_f_cast_isots_idx on pat_data_record_muac_follow_up (cost=0.30..182.60 rows=178 width=1,385) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
188. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_mute_person_f_cast_isots_idx on pat_data_record_mute_person (cost=0.29..219.62 rows=808 width=1,717) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
189. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_muting_approve_request_f_cast_isots_idx on pat_data_record_muting_approve_request (cost=0.30..389.76 rows=2,834 width=796) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
190. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_muting_approved_f_cast_isots_idx on pat_data_record_muting_approved (cost=0.29..105.84 rows=640 width=961) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
191. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_nutrition_assessment_f_cast_isots_idx on pat_data_record_nutrition_assessment (cost=0.44..10,582.20 rows=11,229 width=1,767) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
192. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_ovc_survey_f_cast_isots_idx on pat_data_record_ovc_survey (cost=0.29..3.90 rows=3 width=695) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
193. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_postnatal_care_f_cast_isots_idx on pat_data_record_postnatal_care (cost=0.44..907.08 rows=911 width=195) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
194. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_postnatal_care_cbds_f_cast_isots_idx on pat_data_record_postnatal_care_cbds (cost=0.29..21.48 rows=31 width=46) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
195. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_pregnancy_f_cast_isots_idx on pat_data_record_pregnancy (cost=0.44..519.57 rows=532 width=70) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
196. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_pregnancy_referral_follow_up_f_cast_isots_idx on pat_data_record_pregnancy_referral_follow_up (cost=0.31..82.25 rows=79 width=980) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
197. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_pregnancy_visit_f_cast_isots_idx on pat_data_record_pregnancy_visit (cost=0.44..1,173.41 rows=1,240 width=35) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
198. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_prophylaxis_follow_up_f_cast_isots_idx on pat_data_record_prophylaxis_follow_up (cost=0.31..3,169.28 rows=3,407 width=1,446) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
199. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_symptom_report_f_cast_isots_idx on pat_data_record_symptom_report (cost=0.16..3.21 rows=1 width=18) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
200. 0.000 0.000 ↓ 0.0 0

Index Scan using pat_data_record_u1_follow_up_f_cast_isots_idx on pat_data_record_u1_follow_up (cost=0.30..2.33 rows=1 width=1,113) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Filter: ("*VALUES*".column1 = (doc ->> 'form'::text))
201. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on pat_data_record_default (cost=3.84..17.24 rows=10 width=924) (never executed)

  • Recheck Cond: (((doc ->> 'form'::text) = "*VALUES*".column1) AND (f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
202. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=3.84..3.84 rows=10 width=0) (never executed)

203. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on pat_data_record_default_expr_idx9 (cost=0.00..1.31 rows=22 width=0) (never executed)

  • Index Cond: ((doc ->> 'form'::text) = "*VALUES*".column1)
204. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on pat_data_record_default_f_cast_isots_idx (cost=0.00..2.25 rows=109 width=0) (never executed)

  • Index Cond: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
205. 0.018 0.018 ↓ 0.0 0 2

Index Scan using pat_district_hospital_expr_idx9 on pat_district_hospital (cost=0.14..2.68 rows=1 width=32) (actual time=0.009..0.009 rows=0 loops=2)

  • Index Cond: ((doc ->> 'form'::text) = "*VALUES*".column1)
  • Filter: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
206. 0.012 0.012 ↓ 0.0 0 2

Index Scan using pat_feedback_expr_idx9 on pat_feedback (cost=0.29..2.83 rows=1 width=675) (actual time=0.006..0.006 rows=0 loops=2)

  • Index Cond: ((doc ->> 'form'::text) = "*VALUES*".column1)
  • Filter: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
207. 0.006 0.006 ↓ 0.0 0 2

Index Scan using pat_form_expr_idx9 on pat_form (cost=0.14..2.68 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=2)

  • Index Cond: ((doc ->> 'form'::text) = "*VALUES*".column1)
  • Filter: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
208. 0.012 0.012 ↓ 0.0 0 2

Index Scan using pat_health_center_expr_idx9 on pat_health_center (cost=0.28..2.83 rows=1 width=688) (actual time=0.005..0.006 rows=0 loops=2)

  • Index Cond: ((doc ->> 'form'::text) = "*VALUES*".column1)
  • Filter: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
209. 0.030 0.030 ↓ 0.0 0 2

Index Scan using pat_info_expr_idx9 on pat_info (cost=0.43..2.98 rows=1 width=292) (actual time=0.015..0.015 rows=0 loops=2)

  • Index Cond: ((doc ->> 'form'::text) = "*VALUES*".column1)
  • Filter: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
210. 0.040 0.040 ↓ 0.0 0 2

Seq Scan on pat_meta (cost=0.00..1.54 rows=1 width=32) (actual time=0.020..0.020 rows=0 loops=2)

  • Filter: (("*VALUES*".column1 = (doc ->> 'form'::text)) AND (f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 1
211. 0.020 0.020 ↓ 0.0 0 2

Index Scan using pat_person_expr_idx9 on pat_person (cost=0.43..2.97 rows=1 width=653) (actual time=0.010..0.010 rows=0 loops=2)

  • Index Cond: ((doc ->> 'form'::text) = "*VALUES*".column1)
  • Filter: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
212. 0.020 0.020 ↓ 0.0 0 2

Index Scan using pat_traffic_stats_expr_idx9 on pat_traffic_stats (cost=0.28..2.82 rows=1 width=599) (actual time=0.010..0.010 rows=0 loops=2)

  • Index Cond: ((doc ->> 'form'::text) = "*VALUES*".column1)
  • Filter: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
213. 0.008 0.008 ↓ 0.0 0 2

Index Scan using pat_usage_stats_expr_idx9 on pat_usage_stats (cost=0.14..2.68 rows=1 width=1,078) (actual time=0.004..0.004 rows=0 loops=2)

  • Index Cond: ((doc ->> 'form'::text) = "*VALUES*".column1)
  • Filter: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
214. 0.020 0.020 ↓ 0.0 0 2

Index Scan using couchdb_default_expr_idx9 on couchdb_default (cost=0.42..2.97 rows=1 width=808) (actual time=0.010..0.010 rows=0 loops=2)

  • Index Cond: ((doc ->> 'form'::text) = "*VALUES*".column1)
  • Filter: ((f_cast_isots((doc ->> 'reported_date'::text)) >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (f_cast_isots((doc ->> 'reported_date'::text)) < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
215. 2.431 430.504 ↑ 1.4 1,401 1

Materialize (cost=4,229.27..4,573.58 rows=2,023 width=47) (actual time=275.351..430.504 rows=1,401 loops=1)

216. 145.430 428.073 ↑ 1.4 1,401 1

GroupAggregate (cost=4,229.27..4,548.29 rows=2,023 width=47) (actual time=275.347..428.073 rows=1,401 loops=1)

  • Group Key: visit_1.chw, 0
217. 139.486 282.643 ↓ 1.2 20,483 1

Sort (cost=4,229.27..4,271.95 rows=17,074 width=81) (actual time=275.277..282.643 rows=20,483 loops=1)

  • Sort Key: visit_1.chw
  • Sort Method: quicksort Memory: 3715kB
218. 143.157 143.157 ↓ 1.2 20,483 1

Index Scan using useview_fp_visit_reported_xmlforms_uuid on useview_fp_visit visit_1 (cost=0.43..3,029.01 rows=17,074 width=81) (actual time=0.078..143.157 rows=20,483 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
219. 57,433.491 58,844.472 ↑ 1.1 5,385 12,216

HashAggregate (cost=56,082.54..56,141.37 rows=5,883 width=43) (actual time=0.336..4.817 rows=5,385 loops=12,216)

  • Group Key: useview_chp_sync.chw_uuid
220. 1,410.981 1,410.981 ↑ 1.0 2,113,083 1

Seq Scan on useview_chp_sync (cost=0.00..50,799.83 rows=2,113,083 width=35) (actual time=0.020..1,410.981 rows=2,113,083 loops=1)

221. 99.229 4,629.600 ↓ 1.4 8,202 1

Sort (cost=61,851.25..61,865.96 rows=5,883 width=43) (actual time=4,608.041..4,629.600 rows=8,202 loops=1)

  • Sort Key: a.chw_uuid
  • Sort Method: quicksort Memory: 1025kB
222. 1.480 4,530.371 ↓ 1.4 8,204 1

Subquery Scan on a (cost=61,365.25..61,482.91 rows=5,883 width=43) (actual time=4,526.309..4,530.371 rows=8,204 loops=1)

223. 2,835.060 4,528.891 ↓ 1.4 8,204 1

HashAggregate (cost=61,365.25..61,424.08 rows=5,883 width=43) (actual time=4,526.306..4,528.891 rows=8,204 loops=1)

  • Group Key: useview_chp_sync_1.chw_uuid
224. 1,693.831 1,693.831 ↑ 1.0 2,113,083 1

Seq Scan on useview_chp_sync useview_chp_sync_1 (cost=0.00..50,799.83 rows=2,113,083 width=43) (actual time=0.010..1,693.831 rows=2,113,083 loops=1)

225. 14.756 6,324.406 ↓ 1.3 7,304 1

Materialize (cost=123,730.11..126,954.76 rows=5,594 width=43) (actual time=4,064.444..6,324.406 rows=7,304 loops=1)

226. 1,739.085 6,309.650 ↑ 1.5 3,687 1

GroupAggregate (cost=123,730.11..126,884.83 rows=5,594 width=47) (actual time=4,064.440..6,309.650 rows=3,687 loops=1)

  • Group Key: useview_households_visited.chw, 0
227. 2,321.855 4,570.565 ↓ 1.2 384,061 1

Sort (cost=123,730.11..124,504.81 rows=309,878 width=75) (actual time=4,064.266..4,570.565 rows=384,061 loops=1)

  • Sort Key: useview_households_visited.chw
  • Sort Method: external merge Disk: 33968kB
228. 2,248.710 2,248.710 ↓ 1.2 384,061 1

Index Only Scan using households_date_visited_chw_hh on useview_households_visited (cost=0.57..95,467.16 rows=309,878 width=75) (actual time=0.136..2,248.710 rows=384,061 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Heap Fetches: 384061
229. 7.351 35,513.625 ↓ 2,443.0 2,443 1

Materialize (cost=190,117.84..190,571.24 rows=1 width=47) (actual time=32,668.078..35,513.625 rows=2,443 loops=1)

230. 198.885 35,506.274 ↓ 2,443.0 2,443 1

GroupAggregate (cost=190,117.84..190,571.23 rows=1 width=47) (actual time=32,668.075..35,506.274 rows=2,443 loops=1)

  • Group Key: a_1.chw, 0
231. 1,418.105 35,307.389 ↓ 18,962.0 18,962 1

Merge Join (cost=190,117.84..190,571.21 rows=1 width=75) (actual time=32,667.597..35,307.389 rows=18,962 loops=1)

  • Merge Cond: ((a_1.chw = b.chw) AND (a_1.patient_id = b.patient_id))
  • Join Filter: ((date(a_1.reported) - date(b.reported)) < 2)
  • Rows Removed by Join Filter: 50385
232. 220.346 1,263.871 ↑ 2.7 19,392 1

Sort (cost=83,397.01..83,528.26 rows=52,499 width=79) (actual time=1,239.886..1,263.871 rows=19,392 loops=1)

  • Sort Key: a_1.chw, a_1.patient_id
  • Sort Method: quicksort Memory: 3496kB
233. 1,043.525 1,043.525 ↑ 2.7 19,392 1

Index Scan using useview_assessment_reported_age_uuid on useview_assessment a_1 (cost=0.45..79,281.09 rows=52,499 width=79) (actual time=0.084..1,043.525 rows=19,392 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, '2020-01-01 00:00:00+00'::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone) AND (patient_age_in_years < 5))
  • Filter: (referral_follow_up)::boolean
  • Rows Removed by Filter: 112029
234. 27,611.472 32,625.413 ↓ 193.9 1,541,264 1

Sort (cost=106,720.83..106,740.70 rows=7,948 width=79) (actual time=31,427.653..32,625.413 rows=1,541,264 loops=1)

  • Sort Key: b.chw, b.patient_id
  • Sort Method: external sort Disk: 150248kB
235. 5,013.941 5,013.941 ↓ 193.3 1,536,236 1

Seq Scan on useview_assessment_follow_up b (cost=0.00..106,205.95 rows=7,948 width=79) (actual time=0.038..5,013.941 rows=1,536,236 loops=1)

  • Filter: ((follow_up_count)::integer = 1)
  • Rows Removed by Filter: 58427
Planning time : 437.595 ms
Execution time : 460,449.430 ms