explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ose

Settings
# exclusive inclusive rows x rows loops node
1. 46,883.166 46,883.166 ↓ 23.0 23 1

GroupAggregate (cost=10,333,953.03..10,333,954.98 rows=1 width=456) (actual time=46,849.407..46,883.166 rows=23 loops=1)

  • Group Key: chwlist.branch_uuid, chwlist.branch_name, 'multiple'::text, 'multiple'::text, 'multiple'::text, 'multiple'::text, 'multiple'::text, (date(chwlist.interval_start)), chwlist.interval_number
2.          

CTE chwlist

3. 3.202 147.642 ↓ 5,597.0 5,597 1

Nested Loop (cost=8.51..353.34 rows=1 width=213) (actual time=1.514..147.642 rows=5,597 loops=1)

4. 0.983 138.843 ↓ 5,597.0 5,597 1

Nested Loop Anti Join (cost=8.51..353.29 rows=1 width=201) (actual time=1.503..138.843 rows=5,597 loops=1)

5. 8.492 107.592 ↓ 7,567.0 7,567 1

Nested Loop (cost=8.23..352.97 rows=1 width=201) (actual time=1.488..107.592 rows=7,567 loops=1)

6. 4.925 7.744 ↓ 7,613.0 7,613 1

Hash Join (cost=7.68..350.58 rows=1 width=185) (actual time=0.077..7.744 rows=7,613 loops=1)

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

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

8. 0.009 0.050 ↑ 3.5 25 1

Hash (cost=6.58..6.58 rows=88 width=52) (actual time=0.050..0.050 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
9. 0.041 0.041 ↑ 3.5 25 1

Index Scan using contactview_metadata_type on contactview_metadata cmd (cost=0.43..6.58 rows=88 width=52) (actual time=0.029..0.041 rows=25 loops=1)

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

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

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

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

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

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

13.          

CTE familyreg

14. 4.472 3,254.551 ↓ 2,358.0 2,358 1

GroupAggregate (cost=61,219.93..61,219.95 rows=1 width=48) (actual time=3,247.649..3,254.551 rows=2,358 loops=1)

  • Group Key: contactview_metadata.parent_uuid, 0
15. 35.213 3,250.079 ↓ 22,971.0 22,971 1

Sort (cost=61,219.93..61,219.94 rows=1 width=40) (actual time=3,247.640..3,250.079 rows=22,971 loops=1)

  • Sort Key: contactview_metadata.parent_uuid
  • Sort Method: quicksort Memory: 2563kB
16. 3,214.866 3,214.866 ↓ 22,971.0 22,971 1

Index Scan using contactview_metadata_type on contactview_metadata (cost=0.43..61,219.92 rows=1 width=40) (actual time=1.122..3,214.866 rows=22,971 loops=1)

  • Index Cond: (type = 'clinic'::text)
  • Filter: ((reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::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: 788949
17.          

CTE allpreg

18. 3.844 13.127 ↓ 6.7 2,598 1

HashAggregate (cost=305.33..309.18 rows=385 width=47) (actual time=12.564..13.127 rows=2,598 loops=1)

  • Group Key: visit.chw, 0
19. 9.283 9.283 ↓ 19.2 7,669 1

Index Scan using useview_pregnancy_reported on useview_pregnancy visit (cost=0.45..302.33 rows=399 width=39) (actual time=0.754..9.283 rows=7,669 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::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))
20.          

CTE pncvisit

21. 27.409 50.674 ↓ 10.2 2,255 1

HashAggregate (cost=243.09..245.30 rows=221 width=103) (actual time=49.824..50.674 rows=2,255 loops=1)

  • Group Key: useview_postnatal_care.chw, 0
22. 23.265 23.265 ↓ 20.7 4,685 1

Index Scan using useview_postnatal_care_count_reported_uuid on useview_postnatal_care (cost=0.45..209.19 rows=226 width=61) (actual time=1.506..23.265 rows=4,685 loops=1)

  • Index Cond: ((follow_up_count = '1'::text) AND (reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::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))
23.          

CTE eddnopnc

24. 2.983 13,393.179 ↓ 8.7 1,730 1

HashAggregate (cost=575,090.29..575,092.29 rows=200 width=47) (actual time=13,392.748..13,393.179 rows=1,730 loops=1)

  • Group Key: p.chw, 0
25. 65.082 13,390.196 ↑ 1,602.0 2,717 1

Merge Left Join (cost=199,729.14..542,445.90 rows=4,352,585 width=39) (actual time=9,795.760..13,390.196 rows=2,717 loops=1)

  • Merge Cond: (p.patient_id = useview_postnatal_care_1.patient_id)
  • Filter: (((max(useview_postnatal_care_1.reported)) IS NULL) OR ((@ ((CASE WHEN (max(pv.new_edd) <> ''::text) THEN (max(pv.new_edd))::date ELSE max(p.edd) END) - ((max(useview_postnatal_care_1.reported)))::date)) > 60))
  • Rows Removed by Filter: 2727
26. 11.378 9,713.338 ↑ 3.9 5,444 1

Sort (cost=199,728.72..199,781.65 rows=21,172 width=75) (actual time=9,711.457..9,713.338 rows=5,444 loops=1)

  • Sort Key: p.patient_id
  • Sort Method: quicksort Memory: 958kB
27. 101.514 9,701.960 ↑ 3.9 5,444 1

GroupAggregate (cost=197,148.92..197,995.80 rows=21,172 width=75) (actual time=9,588.448..9,701.960 rows=5,444 loops=1)

  • Group Key: p.chw, p.patient_id
  • Filter: ((max(p.edd) >= date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone)) 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: 48569
28. 184.133 9,600.446 ↓ 2.9 61,859 1

Sort (cost=197,148.92..197,201.85 rows=21,172 width=79) (actual time=9,588.385..9,600.446 rows=61,859 loops=1)

  • Sort Key: p.chw, p.patient_id
  • Sort Method: quicksort Memory: 10235kB
29. 33.231 9,416.313 ↓ 2.9 61,859 1

Merge Anti Join (cost=193,674.39..195,627.73 rows=21,172 width=79) (actual time=9,362.876..9,416.313 rows=61,859 loops=1)

  • Merge Cond: (p.patient_id = useview_pregnancy_visit_1.patient_id)
30. 210.659 8,756.661 ↓ 1.8 77,889 1

Sort (cost=89,985.13..90,090.99 rows=42,343 width=79) (actual time=8,740.098..8,756.661 rows=77,889 loops=1)

  • Sort Key: p.patient_id
  • Sort Method: quicksort Memory: 14026kB
31. 132.722 8,546.002 ↓ 1.8 77,889 1

Hash Join (cost=70,715.64..86,731.10 rows=42,343 width=79) (actual time=8,154.615..8,546.002 rows=77,889 loops=1)

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

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

33. 17.703 8,153.614 ↑ 1.6 67,468 1

Hash (cost=69,361.92..69,361.92 rows=108,298 width=48) (actual time=8,153.613..8,153.614 rows=67,468 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 6910kB
34. 11.308 8,135.911 ↑ 1.6 67,468 1

Subquery Scan on pv (cost=67,543.88..69,361.92 rows=108,298 width=48) (actual time=8,065.352..8,135.911 rows=67,468 loops=1)

35. 24.677 8,124.603 ↑ 1.6 67,468 1

Unique (cost=67,543.88..68,278.94 rows=108,298 width=80) (actual time=8,065.351..8,124.603 rows=67,468 loops=1)

36. 576.414 8,099.926 ↓ 1.1 168,943 1

Sort (cost=67,543.88..67,911.41 rows=147,012 width=80) (actual time=8,065.350..8,099.926 rows=168,943 loops=1)

  • Sort Key: useview_pregnancy_visit.patient_id, useview_pregnancy_visit.reported DESC
  • Sort Method: quicksort Memory: 29902kB
37. 7,399.821 7,523.512 ↓ 1.1 168,943 1

Bitmap Heap Scan on useview_pregnancy_visit (cost=4,976.71..54,926.15 rows=147,012 width=80) (actual time=129.151..7,523.512 rows=168,943 loops=1)

  • Recheck Cond: ((reported > ((((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone - '9 mons'::interval)) OR (reported > (((now())::timestamp without time zone)::timestamp with time zone - '9 mons'::interval)))
  • Filter: (((edd_updated = 'TRUE'::text) AND (reported > ((((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone - '9 mons'::interval))) OR (reported > (((now())::timestamp without time zone)::timestamp with time zone - '9 mons'::interval)))
  • Rows Removed by Filter: 13538
  • Heap Blocks: exact=23540
38. 0.002 123.691 ↓ 0.0 0 1

BitmapOr (cost=4,976.71..4,976.71 rows=308,311 width=0) (actual time=123.691..123.691 rows=0 loops=1)

39. 107.380 107.380 ↓ 1.1 182,483 1

Bitmap Index Scan on useview_pregnancy_visit_reported_chw (cost=0.00..2,565.18 rows=161,299 width=0) (actual time=107.380..107.380 rows=182,483 loops=1)

  • Index Cond: (reported > ((((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone - '9 mons'::interval))
40. 16.309 16.309 ↓ 1.1 168,945 1

Bitmap Index Scan on useview_pregnancy_visit_reported_chw (cost=0.00..2,338.03 rows=147,012 width=0) (actual time=16.309..16.309 rows=168,945 loops=1)

  • Index Cond: (reported > (((now())::timestamp without time zone)::timestamp with time zone - '9 mons'::interval))
41. 1.961 626.421 ↑ 6.3 14,631 1

Materialize (cost=103,689.26..105,064.15 rows=91,659 width=36) (actual time=622.771..626.421 rows=14,631 loops=1)

42. 5.568 624.460 ↑ 6.3 14,631 1

Sort (cost=103,689.26..103,918.41 rows=91,659 width=44) (actual time=622.768..624.460 rows=14,631 loops=1)

  • Sort Key: useview_pregnancy_visit_1.patient_id, useview_pregnancy_visit_1.reported DESC
  • Sort Method: quicksort Memory: 1528kB
43. 1.451 618.892 ↑ 6.3 14,631 1

Append (cost=64,436.94..96,134.73 rows=91,659 width=44) (actual time=169.424..618.892 rows=14,631 loops=1)

44. 2.983 174.070 ↑ 6.1 14,631 1

Unique (cost=64,436.94..65,004.32 rows=89,708 width=44) (actual time=169.424..174.070 rows=14,631 loops=1)

45. 29.099 171.087 ↑ 7.5 15,210 1

Sort (cost=64,436.94..64,720.63 rows=113,477 width=44) (actual time=169.423..171.087 rows=15,210 loops=1)

  • Sort Key: useview_pregnancy_visit_1.patient_id
  • Sort Method: quicksort Memory: 1573kB
46. 105.105 141.988 ↑ 7.5 15,210 1

Bitmap Heap Scan on useview_pregnancy_visit useview_pregnancy_visit_1 (cost=4,959.94..54,909.38 rows=113,477 width=44) (actual time=41.631..141.988 rows=15,210 loops=1)

  • Recheck Cond: ((reported > ((((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone - '9 mons'::interval)) OR (reported > (((now())::timestamp without time zone)::timestamp with time zone - '9 mons'::interval)))
  • Filter: (continue_follow_up <> 'yes'::text)
  • Rows Removed by Filter: 167271
  • Heap Blocks: exact=23540
47. 0.001 36.883 ↓ 0.0 0 1

BitmapOr (cost=4,959.94..4,959.94 rows=308,311 width=0) (actual time=36.883..36.883 rows=0 loops=1)

48. 20.033 20.033 ↓ 1.1 182,483 1

Bitmap Index Scan on useview_pregnancy_visit_reported_chw (cost=0.00..2,565.18 rows=161,299 width=0) (actual time=20.033..20.033 rows=182,483 loops=1)

  • Index Cond: (reported > ((((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone - '9 mons'::interval))
49. 16.849 16.849 ↓ 1.1 168,945 1

Bitmap Index Scan on useview_pregnancy_visit_reported_chw (cost=0.00..2,338.03 rows=147,012 width=0) (actual time=16.849..16.849 rows=168,945 loops=1)

  • Index Cond: (reported > (((now())::timestamp without time zone)::timestamp with time zone - '9 mons'::interval))
50. 0.000 443.371 ↓ 0.0 0 1

Unique (cost=30,203.96..30,213.82 rows=1,951 width=44) (actual time=443.371..443.371 rows=0 loops=1)

51. 0.009 443.371 ↓ 0.0 0 1

Sort (cost=30,203.96..30,208.89 rows=1,972 width=44) (actual time=443.371..443.371 rows=0 loops=1)

  • Sort Key: useview_delivery_check.patient_id
  • Sort Method: quicksort Memory: 25kB
52. 443.362 443.362 ↓ 0.0 0 1

Seq Scan on useview_delivery_check (cost=0.00..30,096.04 rows=1,972 width=44) (actual time=443.362..443.362 rows=0 loops=1)

  • Filter: ((pregnancy_outcome = 'miscarriage'::text) AND ((reported > ((((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone - '9 mons'::interval)) OR (reported > (((now())::timestamp without time zone)::timestamp with time zone - '9 mons'::interval))))
  • Rows Removed by Filter: 435274
53. 37.126 3,611.776 ↓ 1.2 152,647 1

Materialize (cost=0.42..19,704.89 rows=122,128 width=44) (actual time=79.332..3,611.776 rows=152,647 loops=1)

54. 117.847 3,574.650 ↓ 1.2 152,647 1

GroupAggregate (cost=0.42..18,178.29 rows=122,128 width=44) (actual time=79.329..3,574.650 rows=152,647 loops=1)

  • Group Key: useview_postnatal_care_1.patient_id
55. 3,456.803 3,456.803 ↑ 1.0 173,587 1

Index Scan using useview_postnatal_follow_up_count_patient_id on useview_postnatal_care useview_postnatal_care_1 (cost=0.42..16,084.50 rows=174,502 width=44) (actual time=1.470..3,456.803 rows=173,587 loops=1)

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

CTE assess

57. 1,502.849 2,416.256 ↑ 1.5 3,481 1

GroupAggregate (cost=9,922.95..16,234.24 rows=5,075 width=279) (actual time=878.686..2,416.256 rows=3,481 loops=1)

  • Group Key: useview_assessment.chw, 0
58. 360.820 913.407 ↓ 5.8 120,638 1

Sort (cost=9,922.95..9,974.69 rows=20,696 width=127) (actual time=878.047..913.407 rows=120,638 loops=1)

  • Sort Key: useview_assessment.chw
  • Sort Method: quicksort Memory: 35117kB
59. 552.587 552.587 ↓ 5.8 120,638 1

Index Scan using useview_assessment_reported on useview_assessment (cost=0.46..8,439.35 rows=20,696 width=127) (actual time=0.792..552.587 rows=120,638 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::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))
60.          

CTE on_time_follow_ups

61. 4.538 8,599.569 ↓ 3.9 2,248 1

GroupAggregate (cost=13,690.92..13,702.52 rows=580 width=47) (actual time=8,592.540..8,599.569 rows=2,248 loops=1)

  • Group Key: assess_1.chw, 0
62. 34.952 8,595.031 ↓ 26.5 15,356 1

Sort (cost=13,690.92..13,692.37 rows=580 width=39) (actual time=8,592.533..8,595.031 rows=15,356 loops=1)

  • Sort Key: assess_1.chw
  • Sort Method: quicksort Memory: 1584kB
63. 25.556 8,560.079 ↓ 26.5 15,356 1

Nested Loop (cost=4,939.05..13,664.30 rows=580 width=39) (actual time=1,254.910..8,560.079 rows=15,356 loops=1)

64. 461.766 1,713.149 ↓ 5.5 17,857 1

Bitmap Heap Scan on useview_assessment assess_1 (cost=4,938.50..8,298.50 rows=3,240 width=80) (actual time=1,252.989..1,713.149 rows=17,857 loops=1)

  • Recheck Cond: ((reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::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) AND (referral_follow_up = 'true'::text))
  • Heap Blocks: exact=6622
65. 1.453 1,251.383 ↓ 0.0 0 1

BitmapAnd (cost=4,938.50..4,938.50 rows=3,240 width=0) (actual time=1,251.383..1,251.383 rows=0 loops=1)

66. 1,004.875 1,004.875 ↓ 5.9 112,296 1

Bitmap Index Scan on useview_assessment_reported_age_uuid (cost=0.00..464.29 rows=19,105 width=0) (actual time=1,004.875..1,004.875 rows=112,296 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::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))
67. 245.055 245.055 ↓ 1.0 439,624 1

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

  • Index Cond: (referral_follow_up = 'true'::text)
68. 6,821.374 6,821.374 ↑ 1.0 1 17,857

Index Only Scan using useview_assessment_follow_up_source_date_uuid on useview_assessment_follow_up follow_up (cost=0.55..1.65 rows=1 width=44) (actual time=0.377..0.382 rows=1 loops=17,857)

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

CTE familyregtotal

70. 342.069 3,279.071 ↑ 44.3 6,148 1

HashAggregate (cost=106,240.78..108,967.27 rows=272,649 width=56) (actual time=3,275.200..3,279.071 rows=6,148 loops=1)

  • Group Key: cmeta_1.parent_uuid, 0
71. 472.320 2,937.002 ↑ 1.0 811,920 1

Hash Right Join (cost=71,258.05..98,033.11 rows=820,767 width=76) (actual time=2,288.732..2,937.002 rows=811,920 loops=1)

  • Hash Cond: (useview_family_survey.family_id = cmeta_1.uuid)
72. 411.244 1,893.162 ↓ 1.2 488,219 1

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

  • Group Key: useview_family_survey.family_id
73. 1,481.918 1,481.918 ↓ 1.0 627,619 1

Index Only Scan using useview_family_survey_equity_family_id on useview_family_survey (cost=0.43..17,371.77 rows=605,847 width=36) (actual time=1.642..1,481.918 rows=627,619 loops=1)

  • Index Cond: (is_equity_survey = true)
  • Filter: is_equity_survey
  • Heap Fetches: 73348
74. 276.996 571.520 ↑ 1.0 811,920 1

Hash (cost=32,493.08..32,493.08 rows=820,767 width=72) (actual time=571.520..571.520 rows=811,920 loops=1)

  • Buckets: 524288 Batches: 2 Memory Usage: 45670kB
75. 294.524 294.524 ↑ 1.0 811,920 1

Index Scan using contactview_metadata_type on contactview_metadata cmeta_1 (cost=0.43..32,493.08 rows=820,767 width=72) (actual time=0.034..294.524 rows=811,920 loops=1)

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

CTE communityevent

77. 0.536 434.066 ↑ 1.8 335 1

HashAggregate (cost=2,510.13..2,516.11 rows=598 width=47) (actual time=433.996..434.066 rows=335 loops=1)

  • Group Key: meta.chw, 0
78. 88.347 433.530 ↑ 1.1 564 1

Bitmap Heap Scan on form_metadata meta (cost=1,843.32..2,505.39 rows=633 width=39) (actual time=345.316..433.530 rows=564 loops=1)

  • Recheck Cond: ((formname = 'community_event'::text) AND (reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::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=551
79. 5.058 345.183 ↓ 0.0 0 1

BitmapAnd (cost=1,843.32..1,843.32 rows=633 width=0) (actual time=345.183..345.183 rows=0 loops=1)

80. 9.271 9.271 ↑ 1.0 57,011 1

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

  • Index Cond: (formname = 'community_event'::text)
81. 330.854 330.854 ↓ 5.5 491,147 1

Bitmap Index Scan on form_metadata_reported (cost=0.00..1,147.15 rows=89,169 width=0) (actual time=330.854..330.854 rows=491,147 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::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))
82.          

CTE active_in_range

83. 65.819 4,480.061 ↑ 1.0 7,616 1

Seq Scan on contactview_chp chp_1 (cost=0.00..9,467,626.94 rows=7,774 width=44) (actual time=1,715.918..4,480.061 rows=7,616 loops=1)

84.          

SubPlan (for Seq Scan)

85. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=1,171.42..1,188.95 rows=1 width=0) (never executed)

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

Bitmap Heap Scan on form_metadata meta_1 (cost=1,171.42..1,188.18 rows=16 width=14) (never executed)

  • Recheck Cond: ((chw = chp_1.uuid) AND (reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::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))
87. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=1,171.42..1,171.42 rows=16 width=0) (never executed)

88. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on form_metadata_chw (cost=0.00..24.01 rows=1,527 width=0) (never executed)

  • Index Cond: (chw = chp_1.uuid)
89. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on form_metadata_reported (cost=0.00..1,147.15 rows=89,169 width=0) (never executed)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::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))
90. 0.000 0.000 ↓ 0.0 0

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

91. 0.000 0.000 ↓ 0.0 0

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

92. 120.646 1,661.212 ↓ 17.3 128,189 1

Hash Semi Join (cost=0.54..4,246.16 rows=7,431 width=35) (actual time=0.366..1,661.212 rows=128,189 loops=1)

  • Hash Cond: (meta_2.formname = "*VALUES*_1".column1)
93. 1,540.561 1,540.561 ↓ 5.5 491,147 1

Index Scan using form_metadata_reported on form_metadata meta_2 (cost=0.46..3,929.35 rows=89,169 width=49) (actual time=0.019..1,540.561 rows=491,147 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::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))
94. 0.002 0.005 ↑ 1.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
95. 0.003 0.003 ↑ 1.0 3 1

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

96. 2,753.030 2,753.030 ↓ 0.0 0 4,109

Index Scan using contactview_metadata_parent_uuid on contactview_metadata clinic (cost=0.56..14.18 rows=1 width=0) (actual time=0.670..0.670 rows=0 loops=4,109)

  • Index Cond: (parent_uuid = chp_1.area_uuid)
  • Filter: ((reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::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: 64
97. 0.000 0.000 ↓ 0.0 0

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

  • Filter: ((reported >= (date_trunc('day'::text, (((now())::timestamp without time zone - '30 days'::interval))::timestamp with time zone))::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))
98. 0.000 0.000 ↓ 0.0 0

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