explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Pw71

Settings
# exclusive inclusive rows x rows loops node
1. 5.466 290,274.895 ↓ 163.0 163 1

GroupAggregate (cost=9,805,880.90..9,805,881.00 rows=1 width=264) (actual time=290,268.844..290,274.895 rows=163 loops=1)

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

CTE chwlist

3. 1.306 87.706 ↓ 5,597.0 5,597 1

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

4. 1.315 80.803 ↓ 5,597.0 5,597 1

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

5. 5.181 56.787 ↓ 7,567.0 7,567 1

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

6. 4.232 5.928 ↓ 7,613.0 7,613 1

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

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

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

8. 0.010 0.034 ↑ 3.5 25 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
9. 0.024 0.024 ↑ 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.016..0.024 rows=25 loops=1)

  • Index Cond: (type = 'district_hospital'::text)
10. 45.678 45.678 ↑ 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.006..0.006 rows=1 loops=7,613)

  • Index Cond: (uuid = chp.supervisor_uuid)
11. 22.701 22.701 ↓ 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.003..0.003 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.03 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=5,597)

13.          

CTE chw_immunization_pool

14. 10.270 119.155 ↓ 5.2 1,718 1

GroupAggregate (cost=1,813.04..1,819.77 rows=331 width=47) (actual time=106.399..119.155 rows=1,718 loops=1)

  • Group Key: form.chw, 0
15. 31.783 108.885 ↓ 87.3 29,847 1

Sort (cost=1,813.04..1,813.89 rows=342 width=39) (actual time=106.364..108.885 rows=29,847 loops=1)

  • Sort Key: form.chw
  • Sort Method: quicksort Memory: 3100kB
16. 18.790 77.102 ↓ 87.3 29,847 1

Bitmap Heap Scan on form_metadata form (cost=1,442.96..1,798.64 rows=342 width=39) (actual time=60.123..77.102 rows=29,847 loops=1)

  • Recheck Cond: ((formname = ANY ('{immunization_ug,immunization_ug_follow_up}'::text[])) AND (reported >= ((((now())::timestamp without time zone - '1 mon'::interval) + '1 day'::interval))::timestamp without time zone) AND (reported < ((now() + '1 day'::interval))::timestamp without time zone))
  • Heap Blocks: exact=10338
17. 3.524 58.312 ↓ 0.0 0 1

BitmapAnd (cost=1,442.96..1,442.96 rows=342 width=0) (actual time=58.311..58.312 rows=0 loops=1)

18. 7.361 7.361 ↓ 1.8 65,814 1

Bitmap Index Scan on form_metadata_formname (cost=0.00..424.99 rows=36,016 width=0) (actual time=7.361..7.361 rows=65,814 loops=1)

  • Index Cond: (formname = ANY ('{immunization_ug,immunization_ug_follow_up}'::text[]))
19. 47.427 47.427 ↓ 6.1 478,933 1

Bitmap Index Scan on form_metadata_reported (cost=0.00..1,017.56 rows=79,110 width=0) (actual time=47.427..47.427 rows=478,933 loops=1)

  • Index Cond: ((reported >= ((((now())::timestamp without time zone - '1 mon'::interval) + '1 day'::interval))::timestamp without time zone) AND (reported < ((now() + '1 day'::interval))::timestamp without time zone))
20.          

CTE chw_pool

21. 20.961 188.679 ↑ 2.7 2,029 1

GroupAggregate (cost=35,984.51..36,399.10 rows=5,443 width=47) (actual time=160.494..188.679 rows=2,029 loops=1)

  • Group Key: form_1.chw, 0
22. 81.647 167.718 ↓ 1.8 65,814 1

Sort (cost=35,984.51..36,074.55 rows=36,016 width=39) (actual time=160.443..167.718 rows=65,814 loops=1)

  • Sort Key: form_1.chw
  • Sort Method: quicksort Memory: 8214kB
23. 86.071 86.071 ↓ 1.8 65,814 1

Index Scan using form_metadata_formname on form_metadata form_1 (cost=0.43..33,258.76 rows=36,016 width=39) (actual time=0.077..86.071 rows=65,814 loops=1)

  • Index Cond: (formname = ANY ('{immunization_ug,immunization_ug_follow_up}'::text[]))
24.          

CTE u2_child_numerator

25. 5.197 152.926 ↓ 1,092.0 1,092 1

GroupAggregate (cost=5,968.68..5,968.71 rows=1 width=47) (actual time=147.076..152.926 rows=1,092 loops=1)

  • Group Key: form_2.chw, 0
26. 7.329 147.729 ↓ 6,289.0 6,289 1

Sort (cost=5,968.68..5,968.69 rows=1 width=75) (actual time=147.050..147.729 rows=6,289 loops=1)

  • Sort Key: form_2.chw
  • Sort Method: quicksort Memory: 1077kB
27. 2.069 140.400 ↓ 6,289.0 6,289 1

Nested Loop (cost=4,564.52..5,968.67 rows=1 width=75) (actual time=57.616..140.400 rows=6,289 loops=1)

28. 10.768 81.730 ↓ 6,289.0 6,289 1

Hash Join (cost=4,563.96..5,966.12 rows=1 width=73) (actual time=57.590..81.730 rows=6,289 loops=1)

  • Hash Cond: ((useview_immunization_ug.id = i.id) AND ((max(useview_immunization_ug.reported)) = i.reported))
29. 39.668 46.916 ↓ 1.0 43,194 1

HashAggregate (cost=2,815.19..3,246.62 rows=43,143 width=44) (actual time=33.491..46.916 rows=43,194 loops=1)

  • Group Key: useview_immunization_ug.id
30. 7.248 7.248 ↑ 1.0 50,279 1

Seq Scan on useview_immunization_ug (cost=0.00..2,563.79 rows=50,279 width=44) (actual time=0.008..7.248 rows=50,279 loops=1)

31. 1.893 24.046 ↓ 1.9 6,524 1

Hash (cost=1,696.35..1,696.35 rows=3,495 width=81) (actual time=24.046..24.046 rows=6,524 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 810kB
32. 22.153 22.153 ↓ 1.9 6,524 1

Index Scan using useview_immunization_ug_reported on useview_immunization_ug i (cost=0.31..1,696.35 rows=3,495 width=81) (actual time=0.072..22.153 rows=6,524 loops=1)

  • Index Cond: ((reported >= ((((now())::timestamp without time zone - '1 mon'::interval) + '1 day'::interval))::timestamp without time zone) AND (reported < ((now() + '1 day'::interval))::timestamp without time zone))
  • Filter: ((NOT (defaulter)::boolean) AND ((patient_age_in_months)::integer <= 23))
  • Rows Removed by Filter: 15770
33. 56.601 56.601 ↑ 1.0 1 6,289

Index Scan using form_metadata_uuid on form_metadata form_2 (cost=0.56..2.55 rows=1 width=72) (actual time=0.009..0.009 rows=1 loops=6,289)

  • Index Cond: (uuid = i.xmlform_uuid)
34.          

CTE u2_children_registered

35. 10,688.799 98,208.227 ↑ 2.1 2,628 1

GroupAggregate (cost=2,370,143.40..2,474,987.38 rows=5,450 width=47) (actual time=22,429.941..98,208.227 rows=2,628 loops=1)

  • Group Key: form_3.chw, 0
36. 76,440.489 87,519.428 ↓ 2.3 23,971,415 1

Sort (cost=2,370,143.40..2,396,340.77 rows=10,478,948 width=75) (actual time=22,407.349..87,519.428 rows=23,971,415 loops=1)

  • Sort Key: form_3.chw
  • Sort Method: external merge Disk: 2100144kB
37. 6,691.028 11,078.939 ↓ 2.3 23,971,415 1

Hash Join (cost=586,553.36..882,178.16 rows=10,478,948 width=75) (actual time=4,364.840..11,078.939 rows=23,971,415 loops=1)

  • Hash Cond: (demo.chp_area_uuid = form_3.chw_area)
38. 27.564 545.450 ↓ 2.5 14,212 1

Bitmap Heap Scan on useview_population_demographics demo (cost=132,635.03..138,531.95 rows=5,798 width=71) (actual time=520.320..545.450 rows=14,212 loops=1)

  • Recheck Cond: ((age_in_months <= 23) AND (reported >= ((((now())::timestamp without time zone - '1 mon'::interval) + '1 day'::interval))::timestamp without time zone) AND (reported < ((now() + '1 day'::interval))::timestamp without time zone))
  • Heap Blocks: exact=12700
39. 9.826 517.886 ↓ 0.0 0 1

BitmapAnd (cost=132,635.03..132,635.03 rows=5,798 width=0) (actual time=517.886..517.886 rows=0 loops=1)

40. 108.859 108.859 ↓ 1.0 197,359 1

Bitmap Index Scan on useview_population_demographics_patient_age (cost=0.00..27,975.23 rows=194,337 width=0) (actual time=108.859..108.859 rows=197,359 loops=1)

  • Index Cond: (age_in_months <= 23)
41. 399.201 399.201 ↓ 1.0 73,900 1

Bitmap Index Scan on useview_population_demographics_chp_area_reported (cost=0.00..104,656.65 rows=73,529 width=0) (actual time=399.201..399.201 rows=73,900 loops=1)

  • Index Cond: ((reported >= ((((now())::timestamp without time zone - '1 mon'::interval) + '1 day'::interval))::timestamp without time zone) AND (reported < ((now() + '1 day'::interval))::timestamp without time zone))
42. 2,330.344 3,842.461 ↑ 1.0 8,320,173 1

Hash (cost=252,361.26..252,361.26 rows=8,322,326 width=70) (actual time=3,842.461..3,842.461 rows=8,320,173 loops=1)

  • Buckets: 524288 Batches: 32 Memory Usage: 32591kB
43. 1,512.117 1,512.117 ↑ 1.0 8,320,192 1

Seq Scan on form_metadata form_3 (cost=0.00..252,361.26 rows=8,322,326 width=70) (actual time=0.017..1,512.117 rows=8,320,192 loops=1)

44.          

CTE u5_child_numerator

45. 10.344 240.035 ↓ 1,246.0 1,246 1

GroupAggregate (cost=5,968.68..5,968.71 rows=1 width=47) (actual time=228.437..240.035 rows=1,246 loops=1)

  • Group Key: form_4.chw, 0
46. 16.197 229.691 ↓ 13,485.0 13,485 1

Sort (cost=5,968.68..5,968.69 rows=1 width=75) (actual time=228.397..229.691 rows=13,485 loops=1)

  • Sort Key: form_4.chw
  • Sort Method: quicksort Memory: 2281kB
47. 11.221 213.494 ↓ 13,485.0 13,485 1

Nested Loop (cost=4,564.52..5,968.67 rows=1 width=75) (actual time=64.289..213.494 rows=13,485 loops=1)

48. 15.797 94.393 ↓ 13,485.0 13,485 1

Hash Join (cost=4,563.96..5,966.12 rows=1 width=73) (actual time=64.259..94.393 rows=13,485 loops=1)

  • Hash Cond: ((useview_immunization_ug_1.id = i_1.id) AND ((max(useview_immunization_ug_1.reported)) = i_1.reported))
49. 43.456 51.002 ↓ 1.0 43,194 1

HashAggregate (cost=2,815.19..3,246.62 rows=43,143 width=44) (actual time=36.645..51.002 rows=43,194 loops=1)

  • Group Key: useview_immunization_ug_1.id
50. 7.546 7.546 ↑ 1.0 50,279 1

Seq Scan on useview_immunization_ug useview_immunization_ug_1 (cost=0.00..2,563.79 rows=50,279 width=44) (actual time=0.008..7.546 rows=50,279 loops=1)

51. 4.308 27.594 ↓ 4.0 13,945 1

Hash (cost=1,696.35..1,696.35 rows=3,495 width=81) (actual time=27.594..27.594 rows=13,945 loops=1)

  • Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1722kB
52. 23.286 23.286 ↓ 4.0 13,945 1

Index Scan using useview_immunization_ug_reported on useview_immunization_ug i_1 (cost=0.31..1,696.35 rows=3,495 width=81) (actual time=0.061..23.286 rows=13,945 loops=1)

  • Index Cond: ((reported >= ((((now())::timestamp without time zone - '1 mon'::interval) + '1 day'::interval))::timestamp without time zone) AND (reported < ((now() + '1 day'::interval))::timestamp without time zone))
  • Filter: ((NOT (defaulter)::boolean) AND ((patient_age_in_months)::integer <= 59))
  • Rows Removed by Filter: 8349
53. 107.880 107.880 ↑ 1.0 1 13,485

Index Scan using form_metadata_uuid on form_metadata form_4 (cost=0.56..2.55 rows=1 width=72) (actual time=0.008..0.008 rows=1 loops=13,485)

  • Index Cond: (uuid = i_1.xmlform_uuid)
54.          

CTE u5_children_registered

55. 20,948.495 183,906.469 ↑ 1.9 2,825 1

GroupAggregate (cost=6,902,075.40..7,267,555.65 rows=5,450 width=47) (actual time=29,303.650..183,906.469 rows=2,825 loops=1)

  • Group Key: form_5.chw, 0
56. 149,659.102 162,957.974 ↓ 1.2 42,745,791 1

Sort (cost=6,902,075.40..6,993,431.84 rows=36,542,575 width=75) (actual time=29,299.906..162,957.974 rows=42,745,791 loops=1)

  • Sort Key: form_5.chw
  • Sort Method: external merge Disk: 3744464kB
57. 9,202.994 13,298.872 ↓ 1.2 42,745,791 1

Hash Join (cost=453,918.91..1,383,926.15 rows=36,542,575 width=75) (actual time=3,664.292..13,298.872 rows=42,745,791 loops=1)

  • Hash Cond: (demo_1.chp_area_uuid = form_5.chw_area)
58. 439.470 439.470 ↓ 1.5 29,722 1

Index Scan using useview_population_demographics_chp_area_reported on useview_population_demographics demo_1 (cost=0.58..162,233.56 rows=20,219 width=71) (actual time=0.236..439.470 rows=29,722 loops=1)

  • Index Cond: ((reported >= ((((now())::timestamp without time zone - '1 mon'::interval) + '1 day'::interval))::timestamp without time zone) AND (reported < ((now() + '1 day'::interval))::timestamp without time zone))
  • Filter: (age_in_months <= 59)
  • Rows Removed by Filter: 44178
59. 2,278.839 3,656.408 ↑ 1.0 8,320,173 1

Hash (cost=252,361.26..252,361.26 rows=8,322,326 width=70) (actual time=3,656.408..3,656.408 rows=8,320,173 loops=1)

  • Buckets: 524288 Batches: 32 Memory Usage: 32591kB
60. 1,377.569 1,377.569 ↑ 1.0 8,320,192 1

Seq Scan on form_metadata form_5 (cost=0.00..252,361.26 rows=8,322,326 width=70) (actual time=0.010..1,377.569 rows=8,320,192 loops=1)

61.          

CTE u2_defaulters_treated

62. 2.943 133.106 ↓ 1,021.0 1,021 1

GroupAggregate (cost=6,345.83..6,345.85 rows=1 width=47) (actual time=129.826..133.106 rows=1,021 loops=1)

  • Group Key: form_6.chw, 0
63. 4.846 130.163 ↓ 3,595.0 3,595 1

Sort (cost=6,345.83..6,345.84 rows=1 width=75) (actual time=129.811..130.163 rows=3,595 loops=1)

  • Sort Key: form_6.chw
  • Sort Method: quicksort Memory: 602kB
64. 1.477 125.317 ↓ 3,595.0 3,595 1

Nested Loop (cost=5,157.38..6,345.82 rows=1 width=75) (actual time=69.606..125.317 rows=3,595 loops=1)

65. 9.112 91.485 ↓ 3,595.0 3,595 1

Hash Join (cost=5,156.82..6,343.26 rows=1 width=73) (actual time=69.578..91.485 rows=3,595 loops=1)

  • Hash Cond: ((useview_immunization_ug_2.id = i_2.id) AND ((max(useview_immunization_ug_2.reported)) = i_2.reported))
66. 41.366 48.779 ↓ 1.0 43,194 1

HashAggregate (cost=2,815.19..3,246.62 rows=43,143 width=44) (actual time=35.960..48.779 rows=43,194 loops=1)

  • Group Key: useview_immunization_ug_2.id
67. 7.413 7.413 ↑ 1.0 50,279 1

Seq Scan on useview_immunization_ug useview_immunization_ug_2 (cost=0.00..2,563.79 rows=50,279 width=44) (actual time=0.008..7.413 rows=50,279 loops=1)

68. 1.502 33.594 ↓ 6.2 3,768 1

Hash (cost=2,332.50..2,332.50 rows=609 width=117) (actual time=33.594..33.594 rows=3,768 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 599kB
69. 2.060 32.092 ↓ 6.2 3,768 1

Hash Semi Join (cost=555.15..2,332.50 rows=609 width=117) (actual time=6.891..32.092 rows=3,768 loops=1)

  • Hash Cond: (i_2.id = useview_immunization_ug_follow_up.patient_id)
70. 23.214 23.214 ↓ 1.3 4,484 1

Index Scan using useview_immunization_ug_reported on useview_immunization_ug i_2 (cost=0.31..1,748.77 rows=3,495 width=81) (actual time=0.058..23.214 rows=4,484 loops=1)

  • Index Cond: ((reported >= ((((now())::timestamp without time zone - '1 mon'::interval) + '1 day'::interval))::timestamp without time zone) AND (reported < ((now() + '1 day'::interval))::timestamp without time zone))
  • Filter: ((patient_age_in_months <> ''::text) AND (defaulter)::boolean AND ((patient_age_in_months)::integer <= 23))
  • Rows Removed by Filter: 17810
71. 1.804 6.818 ↓ 1.0 7,553 1

Hash (cost=463.62..463.62 rows=7,297 width=36) (actual time=6.818..6.818 rows=7,553 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 573kB
72. 5.014 5.014 ↓ 1.0 7,553 1

Index Scan using useview_immunization_ug_follow_up_reported on useview_immunization_ug_follow_up (cost=0.31..463.62 rows=7,297 width=36) (actual time=0.028..5.014 rows=7,553 loops=1)

  • Index Cond: ((reported >= ((((now())::timestamp without time zone - '1 mon'::interval) + '1 day'::interval))::timestamp without time zone) AND (reported < ((now() + '1 day'::interval))::timestamp without time zone))
73. 32.355 32.355 ↑ 1.0 1 3,595

Index Scan using form_metadata_uuid on form_metadata form_6 (cost=0.56..2.55 rows=1 width=72) (actual time=0.009..0.009 rows=1 loops=3,595)

  • Index Cond: (uuid = i_2.xmlform_uuid)
74.          

CTE u2_defaulters

75. 4.025 134.477 ↓ 1,176.0 1,176 1

GroupAggregate (cost=6,021.11..6,021.13 rows=1 width=47) (actual time=129.997..134.477 rows=1,176 loops=1)

  • Group Key: form_7.chw, 0
76. 6.125 130.452 ↓ 4,299.0 4,299 1

Sort (cost=6,021.11..6,021.11 rows=1 width=75) (actual time=129.981..130.452 rows=4,299 loops=1)

  • Sort Key: form_7.chw
  • Sort Method: quicksort Memory: 797kB
77. 1.160 124.327 ↓ 4,299.0 4,299 1

Nested Loop (cost=4,616.94..6,021.10 rows=1 width=75) (actual time=62.510..124.327 rows=4,299 loops=1)

78. 9.230 84.476 ↓ 4,299.0 4,299 1

Hash Join (cost=4,616.38..6,018.54 rows=1 width=73) (actual time=62.486..84.476 rows=4,299 loops=1)

  • Hash Cond: ((useview_immunization_ug_3.id = i_3.id) AND ((max(useview_immunization_ug_3.reported)) = i_3.reported))
79. 41.931 49.442 ↓ 1.0 43,194 1

HashAggregate (cost=2,815.19..3,246.62 rows=43,143 width=44) (actual time=36.649..49.442 rows=43,194 loops=1)

  • Group Key: useview_immunization_ug_3.id
80. 7.511 7.511 ↑ 1.0 50,279 1

Seq Scan on useview_immunization_ug useview_immunization_ug_3 (cost=0.00..2,563.79 rows=50,279 width=44) (actual time=0.007..7.511 rows=50,279 loops=1)

81. 1.764 25.804 ↓ 1.3 4,484 1

Hash (cost=1,748.77..1,748.77 rows=3,495 width=81) (actual time=25.804..25.804 rows=4,484 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 577kB
82. 24.040 24.040 ↓ 1.3 4,484 1

Index Scan using useview_immunization_ug_reported on useview_immunization_ug i_3 (cost=0.31..1,748.77 rows=3,495 width=81) (actual time=0.061..24.040 rows=4,484 loops=1)

  • Index Cond: ((reported >= ((((now())::timestamp without time zone - '1 mon'::interval) + '1 day'::interval))::timestamp without time zone) AND (reported < ((now() + '1 day'::interval))::timestamp without time zone))
  • Filter: ((patient_age_in_months <> ''::text) AND (defaulter)::boolean AND ((patient_age_in_months)::integer <= 23))
  • Rows Removed by Filter: 17810
83. 38.691 38.691 ↑ 1.0 1 4,299

Index Scan using form_metadata_uuid on form_metadata form_7 (cost=0.56..2.55 rows=1 width=72) (actual time=0.009..0.009 rows=1 loops=4,299)

  • Index Cond: (uuid = i_3.xmlform_uuid)
84. 12.768 290,269.429 ↓ 5,488.0 5,488 1

Sort (cost=461.27..461.27 rows=1 width=296) (actual time=290,268.794..290,269.429 rows=5,488 loops=1)

  • Sort Key: chwlist.branch_uuid, chwlist.branch_name, chwlist.supervisor_uuid, chwlist.supervisor_name, (date(chwlist.interval_start)), chwlist.interval_number
  • Sort Method: quicksort Memory: 1650kB
85. 774.855 290,256.661 ↓ 5,488.0 5,488 1

Nested Loop Left Join (cost=299.65..461.26 rows=1 width=296) (actual time=128,600.376..290,256.661 rows=5,488 loops=1)

  • Join Filter: ((chwlist.chw_uuid = u2_defaulters.chw_uuid) AND (chwlist.interval_number = u2_defaulters.interval_number))
  • Rows Removed by Join Filter: 6452716
86. 667.286 288,795.806 ↓ 5,488.0 5,488 1

Nested Loop Left Join (cost=299.65..461.22 rows=1 width=228) (actual time=128,465.176..288,795.806 rows=5,488 loops=1)

  • Join Filter: ((chwlist.chw_uuid = u2_defaulters_treated.chw_uuid) AND (chwlist.interval_number = u2_defaulters_treated.interval_number))
  • Rows Removed by Join Filter: 5602231
87. 818.518 287,513.864 ↓ 5,488.0 5,488 1

Nested Loop Left Join (cost=299.65..461.19 rows=1 width=220) (actual time=128,331.537..287,513.864 rows=5,488 loops=1)

  • Join Filter: ((chwlist.chw_uuid = u5_child_numerator.chw_uuid) AND (chwlist.interval_number = u5_child_numerator.interval_number))
  • Rows Removed by Join Filter: 6836809
88. 716.045 285,872.146 ↓ 5,488.0 5,488 1

Nested Loop Left Join (cost=299.65..461.15 rows=1 width=212) (actual time=128,090.749..285,872.146 rows=5,488 loops=1)

  • Join Filter: ((chwlist.chw_uuid = u2_child_numerator.chw_uuid) AND (chwlist.interval_number = u2_child_numerator.interval_number))
  • Rows Removed by Join Filter: 5991809
89. 1,126.306 284,486.565 ↓ 5,488.0 5,488 1

Nested Loop Left Join (cost=299.65..461.12 rows=1 width=204) (actual time=127,937.167..284,486.565 rows=5,488 loops=1)

  • Join Filter: ((chwlist.chw_uuid = chw_immunization_pool.chw_uuid) AND (chwlist.interval_number = chw_immunization_pool.interval_number))
  • Rows Removed by Join Filter: 9426673
90. 8.954 282,432.787 ↓ 5,488.0 5,488 1

Hash Right Join (cost=299.65..449.53 rows=1 width=196) (actual time=127,817.031..282,432.787 rows=5,488 loops=1)

  • Hash Cond: ((u5_children_registered.chw_uuid = chwlist.chw_uuid) AND (u5_children_registered.interval_number = chwlist.interval_number))
91. 183,910.469 183,910.469 ↑ 1.9 2,825 1

CTE Scan on u5_children_registered (cost=0.00..109.00 rows=5,450 width=44) (actual time=29,303.652..183,910.469 rows=2,825 loops=1)

92. 3.747 98,513.364 ↓ 5,488.0 5,488 1

Hash (cost=299.63..299.63 rows=1 width=188) (actual time=98,513.364..98,513.364 rows=5,488 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1104kB
93. 7.084 98,509.617 ↓ 5,488.0 5,488 1

Hash Right Join (cost=149.75..299.63 rows=1 width=188) (actual time=22,720.048..98,509.617 rows=5,488 loops=1)

  • Hash Cond: ((u2_children_registered.chw_uuid = chwlist.chw_uuid) AND (u2_children_registered.interval_number = chwlist.interval_number))
94. 98,212.446 98,212.446 ↑ 2.1 2,628 1

CTE Scan on u2_children_registered (cost=0.00..109.00 rows=5,450 width=44) (actual time=22,429.944..98,212.446 rows=2,628 loops=1)

95. 1.894 290.087 ↓ 5,488.0 5,488 1

Hash (cost=149.73..149.73 rows=1 width=180) (actual time=290.087..290.087 rows=5,488 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1046kB
96. 2.175 288.193 ↓ 5,488.0 5,488 1

Hash Right Join (cost=0.04..149.73 rows=1 width=180) (actual time=257.020..288.193 rows=5,488 loops=1)

  • Hash Cond: ((chw_pool.chw_uuid = chwlist.chw_uuid) AND (chw_pool.interval_number = chwlist.interval_number))
97. 189.509 189.509 ↑ 2.7 2,029 1

CTE Scan on chw_pool (cost=0.00..108.86 rows=5,443 width=44) (actual time=160.497..189.509 rows=2,029 loops=1)

98. 2.580 96.509 ↓ 5,488.0 5,488 1

Hash (cost=0.03..0.03 rows=1 width=172) (actual time=96.509..96.509 rows=5,488 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1018kB
99. 93.929 93.929 ↓ 5,488.0 5,488 1

CTE Scan on chwlist (cost=0.00..0.03 rows=1 width=172) (actual time=0.114..93.929 rows=5,488 loops=1)

  • Filter: ((branch_name <> 'HQ'::text) AND (branch_name <> 'HQ OVC'::text))
  • Rows Removed by Filter: 109
100. 927.472 927.472 ↓ 5.2 1,718 5,488

CTE Scan on chw_immunization_pool (cost=0.00..6.62 rows=331 width=44) (actual time=0.020..0.169 rows=1,718 loops=5,488)

101. 669.536 669.536 ↓ 1,092.0 1,092 5,488

CTE Scan on u2_child_numerator (cost=0.00..0.02 rows=1 width=44) (actual time=0.027..0.122 rows=1,092 loops=5,488)

102. 823.200 823.200 ↓ 1,246.0 1,246 5,488

CTE Scan on u5_child_numerator (cost=0.00..0.02 rows=1 width=44) (actual time=0.042..0.150 rows=1,246 loops=5,488)

103. 614.656 614.656 ↓ 1,021.0 1,021 5,488

CTE Scan on u2_defaulters_treated (cost=0.00..0.02 rows=1 width=44) (actual time=0.024..0.112 rows=1,021 loops=5,488)

104. 686.000 686.000 ↓ 1,176.0 1,176 5,488

CTE Scan on u2_defaulters (cost=0.00..0.02 rows=1 width=44) (actual time=0.024..0.125 rows=1,176 loops=5,488)

Planning time : 33.354 ms
Execution time : 290,987.291 ms