explain.depesz.com

PostgreSQL's explain analyze made readable

Result: axJC : Optimization for: plan #GG5D

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 8.371 355,040.360 ↓ 163.0 163 1

HashAggregate (cost=13,497,625.48..13,497,625.53 rows=1 width=264) (actual time=355,040.077..355,040.360 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. 3.132 108.368 ↓ 5,597.0 5,597 1

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

4. 6.887 99.639 ↓ 5,597.0 5,597 1

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

5. 1.691 70.051 ↓ 7,567.0 7,567 1

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

6. 4.978 7.456 ↓ 7,613.0 7,613 1

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

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

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

8. 0.012 0.102 ↑ 3.5 25 1

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

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

  • Index Cond: (type = 'district_hospital'::text)
10. 60.904 60.904 ↑ 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.007..0.008 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. 12.809 476.823 ↑ 16.5 1,719 1

GroupAggregate (cost=72,148.67..72,786.56 rows=28,351 width=82) (actual time=458.432..476.823 rows=1,719 loops=1)

  • Group Key: form.chw, form.chw_area, 0
15. 54.313 464.014 ↓ 1.1 29,932 1

Sort (cost=72,148.67..72,219.54 rows=28,351 width=74) (actual time=458.389..464.014 rows=29,932 loops=1)

  • Sort Key: form.chw, form.chw_area
  • Sort Method: quicksort Memory: 4978kB
16. 12.185 409.701 ↓ 1.1 29,932 1

Nested Loop (cost=2,328.15..70,051.95 rows=28,351 width=74) (actual time=56.638..409.701 rows=29,932 loops=1)

17. 27.261 68.264 ↓ 1.1 29,932 1

HashAggregate (cost=2,327.60..2,611.11 rows=28,351 width=40) (actual time=56.590..68.264 rows=29,932 loops=1)

  • Group Key: i.xmlform_uuid, i.reported
18. 3.919 41.003 ↓ 1.1 29,932 1

Append (cost=0.31..2,185.84 rows=28,351 width=40) (actual time=0.158..41.003 rows=29,932 loops=1)

19. 27.981 27.981 ↓ 1.1 22,351 1

Index Scan using useview_immunization_ug_reported on useview_immunization_ug i (cost=0.31..1,437.26 rows=21,028 width=45) (actual time=0.156..27.981 rows=22,351 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. 9.103 9.103 ↓ 1.0 7,581 1

Index Scan using useview_immunization_ug_follow_up_reported on useview_immunization_ug_follow_up f (cost=0.31..465.07 rows=7,323 width=45) (actual time=0.075..9.103 rows=7,581 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. 329.252 329.252 ↑ 1.0 1 29,932

Index Scan using form_metadata_uuid on form_metadata form (cost=0.56..2.36 rows=1 width=107) (actual time=0.011..0.011 rows=1 loops=29,932)

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

CTE chw_pool

23. 25.365 920.068 ↑ 32.4 2,029 1

GroupAggregate (cost=152,346.95..153,827.76 rows=65,814 width=82) (actual time=882.862..920.068 rows=2,029 loops=1)

  • Group Key: form_1.chw, form_1.chw_area, 0
24. 115.262 894.703 ↑ 1.0 65,814 1

Sort (cost=152,346.95..152,511.48 rows=65,814 width=74) (actual time=882.804..894.703 rows=65,814 loops=1)

  • Sort Key: form_1.chw, form_1.chw_area
  • Sort Method: quicksort Memory: 12328kB
25. 45.018 779.441 ↑ 1.0 65,814 1

Nested Loop (cost=4,286.91..147,079.82 rows=65,814 width=74) (actual time=50.413..779.441 rows=65,814 loops=1)

26. 45.123 76.283 ↑ 1.0 65,814 1

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

  • Group Key: i_1.xmlform_uuid, i_1.reported
27. 6.598 31.160 ↑ 1.0 65,814 1

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

28. 18.913 18.913 ↑ 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.020..18.913 rows=50,279 loops=1)

29. 5.649 5.649 ↑ 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.024..5.649 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=107) (actual time=0.010..0.010 rows=1 loops=65,814)

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

CTE u2_child_numerator

32. 5.406 162.466 ↓ 1,094.0 1,094 1

GroupAggregate (cost=5,972.59..5,972.62 rows=1 width=82) (actual time=156.360..162.466 rows=1,094 loops=1)

  • Group Key: form_2.chw, form_2.chw_area, 0
33. 9.019 157.060 ↓ 6,300.0 6,300 1

Sort (cost=5,972.59..5,972.60 rows=1 width=110) (actual time=156.332..157.060 rows=6,300 loops=1)

  • Sort Key: form_2.chw, form_2.chw_area
  • Sort Method: quicksort Memory: 1771kB
34. 0.909 148.041 ↓ 6,300.0 6,300 1

Nested Loop (cost=4,568.43..5,972.58 rows=1 width=110) (actual time=65.158..148.041 rows=6,300 loops=1)

35. 11.083 90.432 ↓ 6,300.0 6,300 1

Hash Join (cost=4,567.87..5,970.03 rows=1 width=73) (actual time=65.109..90.432 rows=6,300 loops=1)

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

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

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

38. 2.134 26.675 ↓ 1.9 6,536 1

Hash (cost=1,700.11..1,700.11 rows=3,505 width=81) (actual time=26.675..26.675 rows=6,536 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 811kB
39. 24.541 24.541 ↓ 1.9 6,536 1

Index Scan using useview_immunization_ug_reported on useview_immunization_ug i_2 (cost=0.31..1,700.11 rows=3,505 width=81) (actual time=0.075..24.541 rows=6,536 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: 15815
40. 56.700 56.700 ↑ 1.0 1 6,300

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

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

CTE u2_children_registered

42. 12,750.797 112,977.994 ↑ 3,985.7 2,635 1

GroupAggregate (cost=2,456,144.86..2,692,449.83 rows=10,502,443 width=82) (actual time=31,409.140..112,977.994 rows=2,635 loops=1)

  • Group Key: form_3.chw, demo.chp_area_uuid, 0
43. 84,777.195 100,227.197 ↓ 2.3 24,079,304 1

Sort (cost=2,456,144.86..2,482,400.97 rows=10,502,443 width=110) (actual time=31,382.152..100,227.197 rows=24,079,304 loops=1)

  • Sort Key: form_3.chw, demo.chp_area_uuid
  • Sort Method: external merge Disk: 2924304kB
44. 7,220.755 15,450.002 ↓ 2.3 24,079,304 1

Hash Join (cost=586,553.37..882,624.32 rows=10,502,443 width=110) (actual time=8,187.604..15,450.002 rows=24,079,304 loops=1)

  • Hash Cond: (demo.chp_area_uuid = form_3.chw_area)
45. 46.216 3,495.322 ↓ 2.5 14,286 1

Bitmap Heap Scan on useview_population_demographics demo (cost=132,635.04..138,545.48 rows=5,811 width=71) (actual time=3,451.929..3,495.322 rows=14,286 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=12760
46. 11.549 3,449.106 ↓ 0.0 0 1

BitmapAnd (cost=132,635.04..132,635.04 rows=5,811 width=0) (actual time=3,449.106..3,449.106 rows=0 loops=1)

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

  • Index Cond: (age_in_months <= 23)
48. 3,333.876 3,333.876 ↓ 1.0 74,280 1

Bitmap Index Scan on useview_population_demographics_chp_area_reported (cost=0.00..104,656.65 rows=73,689 width=0) (actual time=3,333.875..3,333.876 rows=74,280 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,747.993 4,733.925 ↑ 1.0 8,320,173 1

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

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

51.          

CTE u5_child_numerator

52. 12.257 268.868 ↓ 1,249.0 1,249 1

GroupAggregate (cost=5,972.59..5,972.62 rows=1 width=82) (actual time=254.933..268.868 rows=1,249 loops=1)

  • Group Key: form_4.chw, form_4.chw_area, 0
53. 22.062 256.611 ↓ 13,523.0 13,523 1

Sort (cost=5,972.59..5,972.60 rows=1 width=110) (actual time=254.891..256.611 rows=13,523 loops=1)

  • Sort Key: form_4.chw, form_4.chw_area
  • Sort Method: quicksort Memory: 3772kB
54. 12.800 234.549 ↓ 13,523.0 13,523 1

Nested Loop (cost=4,568.43..5,972.58 rows=1 width=110) (actual time=67.232..234.549 rows=13,523 loops=1)

55. 17.419 100.042 ↓ 13,523.0 13,523 1

Hash Join (cost=4,567.87..5,970.03 rows=1 width=73) (actual time=67.210..100.042 rows=13,523 loops=1)

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

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

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

58. 5.074 31.199 ↓ 4.0 13,985 1

Hash (cost=1,700.11..1,700.11 rows=3,505 width=81) (actual time=31.199..31.199 rows=13,985 loops=1)

  • Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1726kB
59. 26.125 26.125 ↓ 4.0 13,985 1

Index Scan using useview_immunization_ug_reported on useview_immunization_ug i_3 (cost=0.31..1,700.11 rows=3,505 width=81) (actual time=0.071..26.125 rows=13,985 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: 8366
60. 121.707 121.707 ↑ 1.0 1 13,523

Index Scan using form_metadata_uuid on form_metadata form_4 (cost=0.56..2.55 rows=1 width=107) (actual time=0.009..0.009 rows=1 loops=13,523)

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

CTE u5_children_registered

62. 25,883.854 239,731.098 ↑ 10,674.6 2,829 1

GroupAggregate (cost=7,202,325.51..7,962,086.24 rows=30,198,450 width=82) (actual time=62,387.341..239,731.098 rows=2,829 loops=1)

  • Group Key: form_5.chw, demo_1.chp_area_uuid, 0
63. 198,624.768 213,847.244 ↓ 1.2 42,970,722 1

Sort (cost=7,202,325.51..7,293,880.76 rows=36,622,098 width=110) (actual time=62,384.233..213,847.244 rows=42,970,722 loops=1)

  • Sort Key: form_5.chw, demo_1.chp_area_uuid
  • Sort Method: external merge Disk: 5218472kB
64. 10,400.081 15,222.476 ↓ 1.2 42,970,722 1

Hash Join (cost=453,918.91..1,385,482.69 rows=36,622,098 width=110) (actual time=4,261.351..15,222.476 rows=42,970,722 loops=1)

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

Index Scan using useview_population_demographics_chp_area_reported on useview_population_demographics demo_1 (cost=0.58..162,330.56 rows=20,263 width=71) (actual time=0.282..573.968 rows=29,868 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: 44412
66. 2,593.729 4,248.427 ↑ 1.0 8,320,173 1

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

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

68.          

CTE u2_defaulters_treated

69. 3.386 136.405 ↓ 1,021.0 1,021 1

GroupAggregate (cost=6,351.67..6,351.70 rows=1 width=82) (actual time=132.619..136.405 rows=1,021 loops=1)

  • Group Key: form_6.chw, form_6.chw_area, 0
70. 5.389 133.019 ↓ 3,604.0 3,604 1

Sort (cost=6,351.67..6,351.68 rows=1 width=110) (actual time=132.605..133.019 rows=3,604 loops=1)

  • Sort Key: form_6.chw, form_6.chw_area
  • Sort Method: quicksort Memory: 1009kB
71. 1.172 127.630 ↓ 3,604.0 3,604 1

Nested Loop (cost=5,163.22..6,351.66 rows=1 width=110) (actual time=71.416..127.630 rows=3,604 loops=1)

72. 9.386 94.022 ↓ 3,604.0 3,604 1

Hash Join (cost=5,162.67..6,349.11 rows=1 width=73) (actual time=71.393..94.022 rows=3,604 loops=1)

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

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

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

75. 1.468 33.807 ↓ 6.2 3,777 1

Hash (cost=2,338.30..2,338.30 rows=612 width=117) (actual time=33.807..33.807 rows=3,777 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 601kB
76. 2.163 32.339 ↓ 6.2 3,777 1

Hash Semi Join (cost=556.92..2,338.30 rows=612 width=117) (actual time=7.236..32.339 rows=3,777 loops=1)

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

Index Scan using useview_immunization_ug_reported on useview_immunization_ug i_4 (cost=0.31..1,752.68 rows=3,504 width=81) (actual time=0.091..23.061 rows=4,495 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: 17856
78. 1.897 7.115 ↓ 1.0 7,581 1

Hash (cost=465.07..465.07 rows=7,323 width=36) (actual time=7.115..7.115 rows=7,581 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 575kB
79. 5.218 5.218 ↓ 1.0 7,581 1

Index Scan using useview_immunization_ug_follow_up_reported on useview_immunization_ug_follow_up (cost=0.31..465.07 rows=7,323 width=36) (actual time=0.055..5.218 rows=7,581 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.436 32.436 ↑ 1.0 1 3,604

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

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

CTE u2_defaulters

82. 4.245 136.184 ↓ 1,176.0 1,176 1

GroupAggregate (cost=6,025.15..6,025.17 rows=1 width=82) (actual time=131.322..136.184 rows=1,176 loops=1)

  • Group Key: form_7.chw, form_7.chw_area, 0
83. 6.984 131.939 ↓ 4,309.0 4,309 1

Sort (cost=6,025.15..6,025.15 rows=1 width=110) (actual time=131.300..131.939 rows=4,309 loops=1)

  • Sort Key: form_7.chw, form_7.chw_area
  • Sort Method: quicksort Memory: 1284kB
84. 2.017 124.955 ↓ 4,309.0 4,309 1

Nested Loop (cost=4,620.98..6,025.14 rows=1 width=110) (actual time=60.387..124.955 rows=4,309 loops=1)

85. 9.705 84.157 ↓ 4,309.0 4,309 1

Hash Join (cost=4,620.42..6,022.58 rows=1 width=73) (actual time=60.365..84.157 rows=4,309 loops=1)

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

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

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

88. 1.449 25.063 ↓ 1.3 4,495 1

Hash (cost=1,752.68..1,752.68 rows=3,504 width=81) (actual time=25.063..25.063 rows=4,495 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 578kB
89. 23.614 23.614 ↓ 1.3 4,495 1

Index Scan using useview_immunization_ug_reported on useview_immunization_ug i_5 (cost=0.31..1,752.68 rows=3,504 width=81) (actual time=0.094..23.614 rows=4,495 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: 17856
90. 38.781 38.781 ↑ 1.0 1 4,309

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

  • Index Cond: (uuid = i_5.xmlform_uuid)
91. 3.310 355,031.989 ↑ 72.2 5,488 1

Hash Left Join (cost=2,457,925.09..2,574,954.55 rows=396,355 width=296) (actual time=355,012.601..355,031.989 rows=5,488 loops=1)

  • Hash Cond: ((chwlist.chw_uuid = u2_defaulters.chw_uuid) AND (chwlist.interval_number = u2_defaulters.interval_number))
92. 4.082 354,891.507 ↑ 72.2 5,488 1

Merge Left Join (cost=2,457,925.05..2,567,027.42 rows=396,355 width=228) (actual time=354,875.409..354,891.507 rows=5,488 loops=1)

  • Merge Cond: ((chwlist.chw_uuid = u2_children_registered.chw_uuid) AND (chwlist.interval_number = u2_children_registered.interval_number))
93. 2.625 241,901.013 ↓ 3.6 5,488 1

Merge Left Join (cost=838,455.65..838,561.37 rows=1,510 width=220) (actual time=241,889.792..241,901.013 rows=5,488 loops=1)

  • Merge Cond: ((chwlist.chw_uuid = u2_defaulters_treated.chw_uuid) AND (chwlist.interval_number = u2_defaulters_treated.interval_number))
94. 2.759 241,760.757 ↓ 3.6 5,488 1

Merge Left Join (cost=838,455.62..838,527.36 rows=1,510 width=212) (actual time=241,752.472..241,760.757 rows=5,488 loops=1)

  • Merge Cond: ((chwlist.chw_uuid = u5_child_numerator.chw_uuid) AND (chwlist.interval_number = u5_child_numerator.interval_number))
95. 3.582 241,487.623 ↓ 3.6 5,488 1

Merge Left Join (cost=838,455.59..838,493.35 rows=1,510 width=204) (actual time=241,482.437..241,487.623 rows=5,488 loops=1)

  • Merge Cond: ((chwlist.chw_uuid = u2_child_numerator.chw_uuid) AND (chwlist.interval_number = u2_child_numerator.interval_number))
96. 14.217 241,320.267 ↓ 3.6 5,488 1

Sort (cost=838,455.56..838,459.34 rows=1,510 width=196) (actual time=241,318.975..241,320.267 rows=5,488 loops=1)

  • Sort Key: chwlist.chw_uuid, chwlist.interval_number
  • Sort Method: quicksort Memory: 1650kB
97. 4.142 241,306.050 ↓ 3.6 5,488 1

Merge Left Join (cost=837,864.14..838,375.83 rows=1,510 width=196) (actual time=241,300.692..241,306.050 rows=5,488 loops=1)

  • Merge Cond: ((chwlist.interval_number = chw_pool.interval_number) AND (chwlist.chw_area_uuid = chw_pool.chw_area_uuid))
98. 26.780 240,376.267 ↓ 7.3 5,488 1

Sort (cost=831,280.73..831,282.62 rows=755 width=220) (actual time=240,375.288..240,376.267 rows=5,488 loops=1)

  • Sort Key: chwlist.interval_number, chwlist.chw_area_uuid
  • Sort Method: quicksort Memory: 1650kB
99. 9.996 240,349.487 ↓ 7.3 5,488 1

Hash Right Join (cost=779.72..831,244.64 rows=755 width=220) (actual time=62,989.901..240,349.487 rows=5,488 loops=1)

  • Hash Cond: ((u5_children_registered.chw_uuid = chwlist.chw_uuid) AND (u5_children_registered.interval_number = chwlist.interval_number))
100. 239,736.953 239,736.953 ↑ 10,674.6 2,829 1

CTE Scan on u5_children_registered (cost=0.00..603,969.00 rows=30,198,450 width=44) (actual time=62,387.345..239,736.953 rows=2,829 loops=1)

101. 2.487 602.538 ↓ 5,488.0 5,488 1

Hash (cost=779.70..779.70 rows=1 width=212) (actual time=602.538..602.538 rows=5,488 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1259kB
102. 2.472 600.051 ↓ 5,488.0 5,488 1

Hash Right Join (cost=0.04..779.70 rows=1 width=212) (actual time=578.400..600.051 rows=5,488 loops=1)

  • Hash Cond: ((chw_immunization_pool.chw_area_uuid = chwlist.chw_area_uuid) AND (chw_immunization_pool.interval_number = chwlist.interval_number))
103. 477.634 477.634 ↑ 16.5 1,719 1

CTE Scan on chw_immunization_pool (cost=0.00..567.02 rows=28,351 width=44) (actual time=458.436..477.634 rows=1,719 loops=1)

104. 3.708 119.945 ↓ 5,488.0 5,488 1

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

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

CTE Scan on chwlist (cost=0.00..0.03 rows=1 width=204) (actual time=0.216..116.237 rows=5,488 loops=1)

  • Filter: ((branch_name <> 'HQ'::text) AND (branch_name <> 'HQ OVC'::text))
  • Rows Removed by Filter: 109
106. 4.383 925.641 ↑ 32.4 2,029 1

Sort (cost=6,583.41..6,747.94 rows=65,814 width=44) (actual time=925.400..925.641 rows=2,029 loops=1)

  • Sort Key: chw_pool.interval_number, chw_pool.chw_area_uuid
  • Sort Method: quicksort Memory: 319kB
107. 921.258 921.258 ↑ 32.4 2,029 1

CTE Scan on chw_pool (cost=0.00..1,316.28 rows=65,814 width=44) (actual time=882.866..921.258 rows=2,029 loops=1)

108. 0.163 163.774 ↓ 1,094.0 1,094 1

Materialize (cost=0.03..0.04 rows=1 width=44) (actual time=163.457..163.774 rows=1,094 loops=1)

109. 0.546 163.611 ↓ 1,094.0 1,094 1

Sort (cost=0.03..0.04 rows=1 width=44) (actual time=163.454..163.611 rows=1,094 loops=1)

  • Sort Key: u2_child_numerator.chw_uuid, u2_child_numerator.interval_number
  • Sort Method: quicksort Memory: 195kB
110. 163.065 163.065 ↓ 1,094.0 1,094 1

CTE Scan on u2_child_numerator (cost=0.00..0.02 rows=1 width=44) (actual time=156.362..163.065 rows=1,094 loops=1)

111. 0.170 270.375 ↓ 1,249.0 1,249 1

Materialize (cost=0.03..0.04 rows=1 width=44) (actual time=270.032..270.375 rows=1,249 loops=1)

112. 0.661 270.205 ↓ 1,249.0 1,249 1

Sort (cost=0.03..0.04 rows=1 width=44) (actual time=270.030..270.205 rows=1,249 loops=1)

  • Sort Key: u5_child_numerator.chw_uuid, u5_child_numerator.interval_number
  • Sort Method: quicksort Memory: 216kB
113. 269.544 269.544 ↓ 1,249.0 1,249 1

CTE Scan on u5_child_numerator (cost=0.00..0.02 rows=1 width=44) (actual time=254.934..269.544 rows=1,249 loops=1)

114. 0.135 137.631 ↓ 1,021.0 1,021 1

Materialize (cost=0.03..0.04 rows=1 width=44) (actual time=137.316..137.631 rows=1,021 loops=1)

115. 0.558 137.496 ↓ 1,021.0 1,021 1

Sort (cost=0.03..0.04 rows=1 width=44) (actual time=137.315..137.496 rows=1,021 loops=1)

  • Sort Key: u2_defaulters_treated.chw_uuid, u2_defaulters_treated.interval_number
  • Sort Method: quicksort Memory: 162kB
116. 136.938 136.938 ↓ 1,021.0 1,021 1

CTE Scan on u2_defaulters_treated (cost=0.00..0.02 rows=1 width=44) (actual time=132.622..136.938 rows=1,021 loops=1)

117. 0.380 112,986.412 ↑ 3,985.7 2,635 1

Materialize (cost=1,619,469.40..1,671,981.62 rows=10,502,443 width=44) (actual time=112,985.612..112,986.412 rows=2,635 loops=1)

118. 3.018 112,986.032 ↑ 3,985.7 2,635 1

Sort (cost=1,619,469.40..1,645,725.51 rows=10,502,443 width=44) (actual time=112,985.609..112,986.032 rows=2,635 loops=1)

  • Sort Key: u2_children_registered.chw_uuid, u2_children_registered.interval_number
  • Sort Method: quicksort Memory: 439kB
119. 112,983.014 112,983.014 ↑ 3,985.7 2,635 1

CTE Scan on u2_children_registered (cost=0.00..210,048.86 rows=10,502,443 width=44) (actual time=31,409.144..112,983.014 rows=2,635 loops=1)

120. 0.373 137.172 ↓ 1,176.0 1,176 1

Hash (cost=0.02..0.02 rows=1 width=44) (actual time=137.172..137.172 rows=1,176 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 113kB
121. 136.799 136.799 ↓ 1,176.0 1,176 1

CTE Scan on u2_defaulters (cost=0.00..0.02 rows=1 width=44) (actual time=131.325..136.799 rows=1,176 loops=1)

Planning time : 55.447 ms
Execution time : 356,024.283 ms