explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BTgU

Settings
# exclusive inclusive rows x rows loops node
1. 5.652 295,833.474 ↓ 163.0 163 1

GroupAggregate (cost=9,997,548.41..9,997,548.51 rows=1 width=264) (actual time=295,827.260..295,833.474 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.080 86.752 ↓ 5,597.0 5,597 1

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

4. 1.113 80.075 ↓ 5,597.0 5,597 1

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

5. 4.757 56.261 ↓ 7,567.0 7,567 1

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

6. 4.095 5.826 ↓ 7,613.0 7,613 1

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

  • Hash Cond: (chp.branch_uuid = cmd.uuid)
7. 1.688 1.688 ↑ 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.688 rows=7,616 loops=1)

8. 0.007 0.043 ↑ 3.5 25 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
9. 0.036 0.036 ↑ 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.025..0.036 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.825 424.870 ↑ 3.2 1,718 1

GroupAggregate (cost=71,985.34..72,322.65 rows=5,450 width=47) (actual time=411.509..424.870 rows=1,718 loops=1)

  • Group Key: form.chw, 0
15. 35.960 414.045 ↓ 1.1 29,856 1

Sort (cost=71,985.34..72,056.04 rows=28,281 width=39) (actual time=411.475..414.045 rows=29,856 loops=1)

  • Sort Key: form.chw
  • Sort Method: quicksort Memory: 3101kB
16. 28.884 378.085 ↓ 1.1 29,856 1

Nested Loop (cost=2,323.86..69,894.30 rows=28,281 width=39) (actual time=39.577..378.085 rows=29,856 loops=1)

17. 21.305 50.641 ↓ 1.1 29,856 1

HashAggregate (cost=2,323.30..2,606.11 rows=28,281 width=40) (actual time=39.533..50.641 rows=29,856 loops=1)

  • Group Key: i.xmlform_uuid, i.reported
18. 2.946 29.336 ↓ 1.1 29,856 1

Append (cost=0.31..2,181.89 rows=28,281 width=40) (actual time=0.074..29.336 rows=29,856 loops=1)

19. 19.982 19.982 ↓ 1.1 22,303 1

Index Scan using useview_immunization_ug_reported on useview_immunization_ug i (cost=0.31..1,435.40 rows=20,981 width=45) (actual time=0.073..19.982 rows=22,303 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. 6.408 6.408 ↓ 1.0 7,553 1

Index Scan using useview_immunization_ug_follow_up_reported on useview_immunization_ug_follow_up f (cost=0.31..463.68 rows=7,300 width=45) (actual time=0.047..6.408 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))
21. 298.560 298.560 ↑ 1.0 1 29,856

Index Scan using form_metadata_uuid on form_metadata form (cost=0.56..2.36 rows=1 width=72) (actual time=0.010..0.010 rows=1 loops=29,856)

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

CTE chw_pool

23. 20.910 877.905 ↑ 2.7 2,029 1

GroupAggregate (cost=152,346.95..153,059.59 rows=5,450 width=47) (actual time=848.755..877.905 rows=2,029 loops=1)

  • Group Key: form_1.chw, 0
24. 90.038 856.995 ↑ 1.0 65,814 1

Sort (cost=152,346.95..152,511.48 rows=65,814 width=39) (actual time=848.707..856.995 rows=65,814 loops=1)

  • Sort Key: form_1.chw
  • Sort Method: quicksort Memory: 8214kB
25. 35.816 766.957 ↑ 1.0 65,814 1

Nested Loop (cost=4,286.91..147,079.82 rows=65,814 width=39) (actual time=47.079..766.957 rows=65,814 loops=1)

26. 44.193 73.001 ↑ 1.0 65,814 1

HashAggregate (cost=4,286.35..4,944.49 rows=65,814 width=40) (actual time=47.039..73.001 rows=65,814 loops=1)

  • Group Key: i_1.xmlform_uuid, i_1.reported
27. 5.950 28.808 ↑ 1.0 65,814 1

Append (cost=0.00..3,957.28 rows=65,814 width=40) (actual time=0.010..28.808 rows=65,814 loops=1)

28. 17.411 17.411 ↑ 1.0 50,279 1

Seq Scan on useview_immunization_ug i_1 (cost=0.00..2,563.79 rows=50,279 width=45) (actual time=0.010..17.411 rows=50,279 loops=1)

29. 5.447 5.447 ↑ 1.0 15,535 1

Seq Scan on useview_immunization_ug_follow_up f_1 (cost=0.00..735.35 rows=15,535 width=45) (actual time=0.008..5.447 rows=15,535 loops=1)

30. 658.140 658.140 ↑ 1.0 1 65,814

Index Scan using form_metadata_uuid on form_metadata form_1 (cost=0.56..2.14 rows=1 width=72) (actual time=0.010..0.010 rows=1 loops=65,814)

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

CTE u2_child_numerator

32. 4.842 147.208 ↓ 1,092.0 1,092 1

GroupAggregate (cost=5,970.02..5,970.05 rows=1 width=47) (actual time=141.770..147.208 rows=1,092 loops=1)

  • Group Key: form_2.chw, 0
33. 7.591 142.366 ↓ 6,290.0 6,290 1

Sort (cost=5,970.02..5,970.03 rows=1 width=75) (actual time=141.733..142.366 rows=6,290 loops=1)

  • Sort Key: form_2.chw
  • Sort Method: quicksort Memory: 1077kB
34. 3.754 134.775 ↓ 6,290.0 6,290 1

Nested Loop (cost=4,565.86..5,970.01 rows=1 width=75) (actual time=58.121..134.775 rows=6,290 loops=1)

35. 10.075 80.701 ↓ 6,290.0 6,290 1

Hash Join (cost=4,565.30..5,967.46 rows=1 width=73) (actual time=58.096..80.701 rows=6,290 loops=1)

  • Hash Cond: ((useview_immunization_ug.id = i_2.id) AND ((max(useview_immunization_ug.reported)) = i_2.reported))
36. 38.974 46.434 ↓ 1.0 43,194 1

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

  • Group Key: useview_immunization_ug.id
37. 7.460 7.460 ↑ 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.007..7.460 rows=50,279 loops=1)

38. 1.952 24.192 ↓ 1.9 6,526 1

Hash (cost=1,697.66..1,697.66 rows=3,497 width=81) (actual time=24.192..24.192 rows=6,526 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 810kB
39. 22.240 22.240 ↓ 1.9 6,526 1

Index Scan using useview_immunization_ug_reported on useview_immunization_ug i_2 (cost=0.31..1,697.66 rows=3,497 width=81) (actual time=0.073..22.240 rows=6,526 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: 15777
40. 50.320 50.320 ↑ 1.0 1 6,290

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

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

CTE u2_children_registered

42. 10,623.395 99,664.923 ↑ 2.1 2,628 1

GroupAggregate (cost=2,371,055.25..2,475,953.45 rows=5,450 width=47) (actual time=23,652.405..99,664.923 rows=2,628 loops=1)

  • Group Key: form_3.chw, 0
43. 77,487.393 89,041.528 ↓ 2.3 23,981,539 1

Sort (cost=2,371,055.25..2,397,266.17 rows=10,484,370 width=75) (actual time=23,628.787..89,041.528 rows=23,981,539 loops=1)

  • Sort Key: form_3.chw
  • Sort Method: external merge Disk: 2101056kB
44. 7,170.464 11,554.135 ↓ 2.3 23,981,539 1

Hash Join (cost=586,553.37..882,280.66 rows=10,484,370 width=75) (actual time=4,359.227..11,554.135 rows=23,981,539 loops=1)

  • Hash Cond: (demo.chp_area_uuid = form_3.chw_area)
45. 29.255 489.907 ↓ 2.5 14,224 1

Bitmap Heap Scan on useview_population_demographics demo (cost=132,635.03..138,535.07 rows=5,801 width=71) (actual time=463.289..489.907 rows=14,224 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=12709
46. 10.095 460.652 ↓ 0.0 0 1

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

47. 73.827 73.827 ↓ 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=73.827..73.827 rows=197,359 loops=1)

  • Index Cond: (age_in_months <= 23)
48. 376.730 376.730 ↓ 1.0 73,987 1

Bitmap Index Scan on useview_population_demographics_chp_area_reported (cost=0.00..104,656.65 rows=73,564 width=0) (actual time=376.730..376.730 rows=73,987 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))
49. 2,386.044 3,893.764 ↑ 1.0 8,320,173 1

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

  • Buckets: 524288 Batches: 32 Memory Usage: 32591kB
50. 1,507.720 1,507.720 ↑ 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.022..1,507.720 rows=8,320,192 loops=1)

51.          

CTE u5_child_numerator

52. 9.707 233.956 ↓ 1,246.0 1,246 1

GroupAggregate (cost=5,970.02..5,970.05 rows=1 width=47) (actual time=222.989..233.956 rows=1,246 loops=1)

  • Group Key: form_4.chw, 0
53. 15.430 224.249 ↓ 13,489.0 13,489 1

Sort (cost=5,970.02..5,970.03 rows=1 width=75) (actual time=222.947..224.249 rows=13,489 loops=1)

  • Sort Key: form_4.chw
  • Sort Method: quicksort Memory: 2281kB
54. 9.229 208.819 ↓ 13,489.0 13,489 1

Nested Loop (cost=4,565.86..5,970.01 rows=1 width=75) (actual time=61.701..208.819 rows=13,489 loops=1)

55. 15.445 91.678 ↓ 13,489.0 13,489 1

Hash Join (cost=4,565.30..5,967.46 rows=1 width=73) (actual time=61.676..91.678 rows=13,489 loops=1)

  • Hash Cond: ((useview_immunization_ug_1.id = i_3.id) AND ((max(useview_immunization_ug_1.reported)) = i_3.reported))
56. 41.404 48.676 ↓ 1.0 43,194 1

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

  • Group Key: useview_immunization_ug_1.id
57. 7.272 7.272 ↑ 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.272 rows=50,279 loops=1)

58. 3.937 27.557 ↓ 4.0 13,950 1

Hash (cost=1,697.66..1,697.66 rows=3,497 width=81) (actual time=27.557..27.557 rows=13,950 loops=1)

  • Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1722kB
59. 23.620 23.620 ↓ 4.0 13,950 1

Index Scan using useview_immunization_ug_reported on useview_immunization_ug i_3 (cost=0.31..1,697.66 rows=3,497 width=81) (actual time=0.052..23.620 rows=13,950 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: 8353
60. 107.912 107.912 ↑ 1.0 1 13,489

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,489)

  • Index Cond: (uuid = i_3.xmlform_uuid)
61.          

CTE u5_children_registered

62. 21,049.255 189,039.239 ↑ 1.9 2,825 1

GroupAggregate (cost=6,905,288.74..7,270,949.72 rows=5,450 width=47) (actual time=29,909.989..189,039.239 rows=2,825 loops=1)

  • Group Key: form_5.chw, 0
63. 154,406.687 167,989.984 ↓ 1.2 42,775,912 1

Sort (cost=6,905,288.74..6,996,690.36 rows=36,560,648 width=75) (actual time=29,906.632..167,989.984 rows=42,775,912 loops=1)

  • Sort Key: form_5.chw
  • Sort Method: external merge Disk: 3747080kB
64. 9,382.252 13,583.297 ↓ 1.2 42,775,912 1

Hash Join (cost=453,918.91..1,384,280.84 rows=36,560,648 width=75) (actual time=3,764.817..13,583.297 rows=42,775,912 loops=1)

  • Hash Cond: (demo_1.chp_area_uuid = form_5.chw_area)
65. 445.383 445.383 ↓ 1.5 29,751 1

Index Scan using useview_population_demographics_chp_area_reported on useview_population_demographics demo_1 (cost=0.58..162,255.00 rows=20,229 width=71) (actual time=0.236..445.383 rows=29,751 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: 44236
66. 2,370.477 3,755.662 ↑ 1.0 8,320,173 1

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

  • Buckets: 524288 Batches: 32 Memory Usage: 32591kB
67. 1,385.185 1,385.185 ↑ 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.008..1,385.185 rows=8,320,192 loops=1)

68.          

CTE u2_defaulters_treated

69. 3.193 127.613 ↓ 1,021.0 1,021 1

GroupAggregate (cost=6,347.27..6,347.30 rows=1 width=47) (actual time=124.087..127.613 rows=1,021 loops=1)

  • Group Key: form_6.chw, 0
70. 4.420 124.420 ↓ 3,595.0 3,595 1

Sort (cost=6,347.27..6,347.28 rows=1 width=75) (actual time=124.072..124.420 rows=3,595 loops=1)

  • Sort Key: form_6.chw
  • Sort Method: quicksort Memory: 602kB
71. 0.242 120.000 ↓ 3,595.0 3,595 1

Nested Loop (cost=5,158.82..6,347.26 rows=1 width=75) (actual time=66.756..120.000 rows=3,595 loops=1)

72. 8.490 87.403 ↓ 3,595.0 3,595 1

Hash Join (cost=5,158.27..6,344.71 rows=1 width=73) (actual time=66.729..87.403 rows=3,595 loops=1)

  • Hash Cond: ((useview_immunization_ug_2.id = i_4.id) AND ((max(useview_immunization_ug_2.reported)) = i_4.reported))
73. 39.833 47.114 ↓ 1.0 43,194 1

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

  • Group Key: useview_immunization_ug_2.id
74. 7.281 7.281 ↑ 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.010..7.281 rows=50,279 loops=1)

75. 1.403 31.799 ↓ 6.2 3,768 1

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

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 599kB
76. 1.858 30.396 ↓ 6.2 3,768 1

Hash Semi Join (cost=555.25..2,333.95 rows=609 width=117) (actual time=6.997..30.396 rows=3,768 loops=1)

  • Hash Cond: (i_4.id = useview_immunization_ug_follow_up.patient_id)
77. 21.628 21.628 ↓ 1.3 4,486 1

Index Scan using useview_immunization_ug_reported on useview_immunization_ug i_4 (cost=0.31..1,750.11 rows=3,497 width=81) (actual time=0.058..21.628 rows=4,486 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: 17817
78. 1.837 6.910 ↓ 1.0 7,553 1

Hash (cost=463.68..463.68 rows=7,300 width=36) (actual time=6.910..6.910 rows=7,553 loops=1)

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

Index Scan using useview_immunization_ug_follow_up_reported on useview_immunization_ug_follow_up (cost=0.31..463.68 rows=7,300 width=36) (actual time=0.024..5.073 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))
80. 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.008..0.009 rows=1 loops=3,595)

  • Index Cond: (uuid = i_4.xmlform_uuid)
81.          

CTE u2_defaulters

82. 3.466 125.640 ↓ 1,176.0 1,176 1

GroupAggregate (cost=6,022.48..6,022.50 rows=1 width=47) (actual time=121.744..125.640 rows=1,176 loops=1)

  • Group Key: form_7.chw, 0
83. 5.674 122.174 ↓ 4,300.0 4,300 1

Sort (cost=6,022.48..6,022.48 rows=1 width=75) (actual time=121.729..122.174 rows=4,300 loops=1)

  • Sort Key: form_7.chw
  • Sort Method: quicksort Memory: 797kB
84. 3.339 116.500 ↓ 4,300.0 4,300 1

Nested Loop (cost=4,618.31..6,022.47 rows=1 width=75) (actual time=58.300..116.500 rows=4,300 loops=1)

85. 8.558 78.761 ↓ 4,300.0 4,300 1

Hash Join (cost=4,617.75..6,019.91 rows=1 width=73) (actual time=58.274..78.761 rows=4,300 loops=1)

  • Hash Cond: ((useview_immunization_ug_3.id = i_5.id) AND ((max(useview_immunization_ug_3.reported)) = i_5.reported))
86. 39.015 46.114 ↓ 1.0 43,194 1

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

  • Group Key: useview_immunization_ug_3.id
87. 7.099 7.099 ↑ 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.099 rows=50,279 loops=1)

88. 1.511 24.089 ↓ 1.3 4,486 1

Hash (cost=1,750.11..1,750.11 rows=3,497 width=81) (actual time=24.089..24.089 rows=4,486 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 577kB
89. 22.578 22.578 ↓ 1.3 4,486 1

Index Scan using useview_immunization_ug_reported on useview_immunization_ug i_5 (cost=0.31..1,750.11 rows=3,497 width=81) (actual time=0.055..22.578 rows=4,486 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: 17817
90. 34.400 34.400 ↑ 1.0 1 4,300

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

  • Index Cond: (uuid = i_5.xmlform_uuid)
91. 12.371 295,827.822 ↓ 5,488.0 5,488 1

Sort (cost=599.78..599.78 rows=1 width=296) (actual time=295,827.208..295,827.822 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
92. 764.301 295,815.451 ↓ 5,488.0 5,488 1

Nested Loop Left Join (cost=449.74..599.77 rows=1 width=296) (actual time=131,634.578..295,815.451 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
93. 661.449 294,381.614 ↓ 5,488.0 5,488 1

Nested Loop Left Join (cost=449.74..599.73 rows=1 width=228) (actual time=131,508.323..294,381.614 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
94. 804.550 293,121.973 ↓ 5,488.0 5,488 1

Nested Loop Left Join (cost=449.74..599.69 rows=1 width=220) (actual time=131,380.150..293,121.973 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
95. 705.820 291,505.199 ↓ 5,488.0 5,488 1

Nested Loop Left Join (cost=449.74..599.66 rows=1 width=212) (actual time=131,145.520..291,505.199 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
96. 9.628 290,140.819 ↓ 5,488.0 5,488 1

Hash Right Join (cost=449.74..599.62 rows=1 width=204) (actual time=130,997.713..290,140.819 rows=5,488 loops=1)

  • Hash Cond: ((u5_children_registered.chw_uuid = chwlist.chw_uuid) AND (u5_children_registered.interval_number = chwlist.interval_number))
97. 189,043.489 189,043.489 ↑ 1.9 2,825 1

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

98. 3.943 101,087.702 ↓ 5,488.0 5,488 1

Hash (cost=449.72..449.72 rows=1 width=196) (actual time=101,087.702..101,087.702 rows=5,488 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1118kB
99. 7.073 101,083.759 ↓ 5,488.0 5,488 1

Hash Right Join (cost=299.84..449.72 rows=1 width=196) (actual time=25,060.121..101,083.759 rows=5,488 loops=1)

  • Hash Cond: ((u2_children_registered.chw_uuid = chwlist.chw_uuid) AND (u2_children_registered.interval_number = chwlist.interval_number))
100. 99,668.992 99,668.992 ↑ 2.1 2,628 1

CTE Scan on u2_children_registered (cost=0.00..109.00 rows=5,450 width=44) (actual time=23,652.409..99,668.992 rows=2,628 loops=1)

101. 1.992 1,407.694 ↓ 5,488.0 5,488 1

Hash (cost=299.82..299.82 rows=1 width=188) (actual time=1,407.694..1,407.694 rows=5,488 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1089kB
102. 2.254 1,405.702 ↓ 5,488.0 5,488 1

Hash Right Join (cost=149.94..299.82 rows=1 width=188) (actual time=1,373.560..1,405.702 rows=5,488 loops=1)

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

CTE Scan on chw_pool (cost=0.00..109.00 rows=5,450 width=44) (actual time=848.758..878.660 rows=2,029 loops=1)

104. 1.898 524.788 ↓ 5,488.0 5,488 1

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

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1042kB
105. 2.003 522.890 ↓ 5,488.0 5,488 1

Hash Right Join (cost=0.04..149.92 rows=1 width=180) (actual time=506.882..522.890 rows=5,488 loops=1)

  • Hash Cond: ((chw_immunization_pool.chw_uuid = chwlist.chw_uuid) AND (chw_immunization_pool.interval_number = chwlist.interval_number))
106. 425.535 425.535 ↑ 3.2 1,718 1

CTE Scan on chw_immunization_pool (cost=0.00..109.00 rows=5,450 width=44) (actual time=411.512..425.535 rows=1,718 loops=1)

107. 2.600 95.352 ↓ 5,488.0 5,488 1

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

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

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

  • Filter: ((branch_name <> 'HQ'::text) AND (branch_name <> 'HQ OVC'::text))
  • Rows Removed by Filter: 109
109. 658.560 658.560 ↓ 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.026..0.120 rows=1,092 loops=5,488)

110. 812.224 812.224 ↓ 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.041..0.148 rows=1,246 loops=5,488)

111. 598.192 598.192 ↓ 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.023..0.109 rows=1,021 loops=5,488)

112. 669.536 669.536 ↓ 1,176.0 1,176 5,488

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

Planning time : 34.088 ms
Execution time : 296,545.857 ms