explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5RE7 : Optimization for: plan #2hIk

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 5.494 73,700.962 ↓ 23.0 23 1

GroupAggregate (cost=3,226,999.04..3,227,275.80 rows=1 width=264) (actual time=73,694.898..73,700.962 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. 0.927 87.401 ↓ 5,597.0 5,597 1

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

4. 1.619 80.877 ↓ 5,597.0 5,597 1

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

5. 5.262 56.557 ↓ 7,567.0 7,567 1

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

6. 4.204 5.617 ↓ 7,613.0 7,613 1

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

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

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

8. 0.008 0.042 ↑ 3.5 25 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
9. 0.034 0.034 ↑ 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.024..0.034 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.02 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=5,597)

13.          

CTE chw_immunization_pool

14. 3.260 73.741 ↓ 2.5 834 1

GroupAggregate (cost=9,420.90..9,466.74 rows=340 width=76) (actual time=69.917..73.741 rows=834 loops=1)

  • Group Key: x.chw, x.chw_area_uuid, 0
15. 7.519 70.481 ↓ 1.6 5,549 1

Sort (cost=9,420.90..9,429.39 rows=3,395 width=68) (actual time=69.898..70.481 rows=5,549 loops=1)

  • Sort Key: x.chw, x.chw_area_uuid
  • Sort Method: quicksort Memory: 973kB
16. 0.778 62.962 ↓ 1.6 5,549 1

Subquery Scan on x (cost=9,153.90..9,221.80 rows=3,395 width=68) (actual time=60.835..62.962 rows=5,549 loops=1)

17. 4.416 62.184 ↓ 1.6 5,549 1

HashAggregate (cost=9,153.90..9,187.85 rows=3,395 width=104) (actual time=60.833..62.184 rows=5,549 loops=1)

  • Group Key: i.xmlform_uuid, i.reported, form.chw, form.chw_area
18. 0.638 57.768 ↓ 1.6 5,549 1

Append (cost=0.87..9,119.95 rows=3,395 width=104) (actual time=0.091..57.768 rows=5,549 loops=1)

19. 2.328 41.204 ↓ 1.8 4,010 1

Nested Loop (cost=0.87..5,932.19 rows=2,214 width=115) (actual time=0.091..41.204 rows=4,010 loops=1)

20. 2.786 2.786 ↓ 1.8 4,010 1

Index Scan using useview_immunization_ug_reported on useview_immunization_ug i (cost=0.32..250.00 rows=2,214 width=45) (actual time=0.070..2.786 rows=4,010 loops=1)

  • Index Cond: ((reported >= (((now())::timestamp without time zone - '7 days'::interval))::timestamp without time zone) AND (reported < ((((now())::timestamp without time zone)::timestamp with time zone + '1 day'::interval))::timestamp without time zone))
21. 36.090 36.090 ↑ 1.0 1 4,010

Index Scan using form_metadata_uuid on form_metadata form (cost=0.56..2.56 rows=1 width=107) (actual time=0.009..0.009 rows=1 loops=4,010)

  • Index Cond: (uuid = i.xmlform_uuid)
22. 1.060 15.926 ↓ 1.3 1,539 1

Nested Loop (cost=0.87..3,153.81 rows=1,181 width=115) (actual time=0.081..15.926 rows=1,539 loops=1)

23. 1.015 1.015 ↓ 1.3 1,539 1

Index Scan using useview_immunization_ug_follow_up_reported on useview_immunization_ug_follow_up f (cost=0.31..111.93 rows=1,181 width=45) (actual time=0.069..1.015 rows=1,539 loops=1)

  • Index Cond: ((reported >= (((now())::timestamp without time zone - '7 days'::interval))::timestamp without time zone) AND (reported < ((((now())::timestamp without time zone)::timestamp with time zone + '1 day'::interval))::timestamp without time zone))
24. 13.851 13.851 ↑ 1.0 1 1,539

Index Scan using form_metadata_uuid on form_metadata form_1 (cost=0.56..2.57 rows=1 width=107) (actual time=0.009..0.009 rows=1 loops=1,539)

  • Index Cond: (uuid = f.xmlform_uuid)
25.          

CTE chw_pool

26. 26.177 14,533.598 ↑ 3.2 2,029 1

GroupAggregate (cost=161,694.02..162,582.50 rows=6,581 width=76) (actual time=14,493.517..14,533.598 rows=2,029 loops=1)

  • Group Key: x_1.chw, x_1.chw_area_uuid, 0
27. 116.731 14,507.421 ↑ 1.0 65,814 1

Sort (cost=161,694.02..161,858.55 rows=65,814 width=68) (actual time=14,493.455..14,507.421 rows=65,814 loops=1)

  • Sort Key: x_1.chw, x_1.chw_area_uuid
  • Sort Method: quicksort Memory: 12328kB
28. 9.367 14,390.690 ↑ 1.0 65,814 1

Subquery Scan on x_1 (cost=155,110.61..156,426.89 rows=65,814 width=68) (actual time=14,360.088..14,390.690 rows=65,814 loops=1)

29. 90.534 14,381.323 ↑ 1.0 65,814 1

HashAggregate (cost=155,110.61..155,768.75 rows=65,814 width=104) (actual time=14,360.087..14,381.323 rows=65,814 loops=1)

  • Group Key: i_1.xmlform_uuid, i_1.reported, form_2.chw, form_2.chw_area
30. 12.577 14,290.789 ↑ 1.0 65,814 1

Append (cost=0.56..154,452.47 rows=65,814 width=104) (actual time=0.923..14,290.789 rows=65,814 loops=1)

31. 66.484 12,158.829 ↑ 1.0 50,279 1

Nested Loop (cost=0.56..114,844.00 rows=50,279 width=115) (actual time=0.922..12,158.829 rows=50,279 loops=1)

32. 25.385 25.385 ↑ 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.526..25.385 rows=50,279 loops=1)

33. 12,066.960 12,066.960 ↑ 1.0 1 50,279

Index Scan using form_metadata_uuid on form_metadata form_2 (cost=0.56..2.22 rows=1 width=107) (actual time=0.237..0.240 rows=1 loops=50,279)

  • Index Cond: (uuid = i_1.xmlform_uuid)
34. 15.202 2,119.383 ↑ 1.0 15,535 1

Nested Loop (cost=0.56..38,950.32 rows=15,535 width=115) (actual time=0.835..2,119.383 rows=15,535 loops=1)

35. 6.956 6.956 ↑ 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.466..6.956 rows=15,535 loops=1)

36. 2,097.225 2,097.225 ↑ 1.0 1 15,535

Index Scan using form_metadata_uuid on form_metadata form_3 (cost=0.56..2.45 rows=1 width=107) (actual time=0.133..0.135 rows=1 loops=15,535)

  • Index Cond: (uuid = f_1.xmlform_uuid)
37.          

CTE u2_child_numerator

38. 1.266 78.959 ↓ 394.0 394 1

GroupAggregate (cost=4,503.14..4,503.17 rows=1 width=82) (actual time=77.493..78.959 rows=394 loops=1)

  • Group Key: form_4.chw, form_4.chw_area, 0
39. 1.793 77.693 ↓ 1,176.0 1,176 1

Sort (cost=4,503.14..4,503.15 rows=1 width=110) (actual time=77.477..77.693 rows=1,176 loops=1)

  • Sort Key: form_4.chw, form_4.chw_area
  • Sort Method: quicksort Memory: 339kB
40. 0.443 75.900 ↓ 1,176.0 1,176 1

Nested Loop (cost=3,098.95..4,503.13 rows=1 width=110) (actual time=44.249..75.900 rows=1,176 loops=1)

41. 7.453 63.697 ↓ 1,176.0 1,176 1

Hash Join (cost=3,098.39..4,500.55 rows=1 width=73) (actual time=44.225..63.697 rows=1,176 loops=1)

  • Hash Cond: ((useview_immunization_ug.id = i_2.id) AND ((max(useview_immunization_ug.reported)) = i_2.reported))
42. 42.212 50.564 ↓ 1.0 43,194 1

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

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

44. 0.432 5.680 ↓ 3.2 1,196 1

Hash (cost=277.67..277.67 rows=369 width=81) (actual time=5.680..5.680 rows=1,196 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 153kB
45. 5.248 5.248 ↓ 3.2 1,196 1

Index Scan using useview_immunization_ug_reported on useview_immunization_ug i_2 (cost=0.32..277.67 rows=369 width=81) (actual time=0.431..5.248 rows=1,196 loops=1)

  • Index Cond: ((reported >= (((now())::timestamp without time zone - '7 days'::interval))::timestamp without time zone) AND (reported < ((((now())::timestamp without time zone)::timestamp with time zone + '1 day'::interval))::timestamp without time zone))
  • Filter: ((NOT (defaulter)::boolean) AND ((patient_age_in_months)::integer <= 23))
  • Rows Removed by Filter: 2814
46. 11.760 11.760 ↑ 1.0 1 1,176

Index Scan using form_metadata_uuid on form_metadata form_4 (cost=0.56..2.58 rows=1 width=107) (actual time=0.010..0.010 rows=1 loops=1,176)

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

CTE u2_children_registered

48. 2,042.003 20,302.456 ↑ 1,784.3 941 1

GroupAggregate (cost=927,644.67..965,422.55 rows=1,679,017 width=82) (actual time=9,372.403..20,302.456 rows=941 loops=1)

  • Group Key: form_5.chw, demo.chp_area_uuid, 0
49. 11,848.245 18,260.453 ↓ 2.8 4,653,472 1

Sort (cost=927,644.67..931,842.21 rows=1,679,017 width=110) (actual time=9,368.724..18,260.453 rows=4,653,472 loops=1)

  • Sort Key: form_5.chw, demo.chp_area_uuid
  • Sort Method: external merge Disk: 564584kB
50. 2,100.341 6,412.208 ↓ 2.8 4,653,472 1

Hash Join (cost=453,918.92..698,291.15 rows=1,679,017 width=110) (actual time=3,968.224..6,412.208 rows=4,653,472 loops=1)

  • Hash Cond: (demo.chp_area_uuid = form_5.chw_area)
51. 360.292 360.292 ↓ 2.8 2,587 1

Index Scan using useview_population_demographics_chp_area_reported on useview_population_demographics demo (cost=0.58..116,048.87 rows=929 width=71) (actual time=0.728..360.292 rows=2,587 loops=1)

  • Index Cond: ((reported >= (((now())::timestamp without time zone - '7 days'::interval))::timestamp without time zone) AND (reported < ((((now())::timestamp without time zone)::timestamp with time zone + '1 day'::interval))::timestamp without time zone))
  • Filter: (age_in_months <= 23)
  • Rows Removed by Filter: 9506
52. 2,482.117 3,951.575 ↑ 1.0 8,320,173 1

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

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

54.          

CTE u5_child_numerator

55. 2.405 96.454 ↓ 486.0 486 1

GroupAggregate (cost=4,503.14..4,503.17 rows=1 width=82) (actual time=93.635..96.454 rows=486 loops=1)

  • Group Key: form_6.chw, form_6.chw_area, 0
56. 3.447 94.049 ↓ 2,470.0 2,470 1

Sort (cost=4,503.14..4,503.15 rows=1 width=110) (actual time=93.616..94.049 rows=2,470 loops=1)

  • Sort Key: form_6.chw, form_6.chw_area
  • Sort Method: quicksort Memory: 707kB
57. 2.558 90.602 ↓ 2,470.0 2,470 1

Nested Loop (cost=3,098.95..4,503.13 rows=1 width=110) (actual time=44.461..90.602 rows=2,470 loops=1)

58. 8.249 65.814 ↓ 2,470.0 2,470 1

Hash Join (cost=3,098.39..4,500.55 rows=1 width=73) (actual time=44.440..65.814 rows=2,470 loops=1)

  • Hash Cond: ((useview_immunization_ug_1.id = i_3.id) AND ((max(useview_immunization_ug_1.reported)) = i_3.reported))
59. 43.481 52.131 ↓ 1.0 43,194 1

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

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

61. 0.774 5.434 ↓ 6.8 2,506 1

Hash (cost=277.67..277.67 rows=369 width=81) (actual time=5.434..5.434 rows=2,506 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 319kB
62. 4.660 4.660 ↓ 6.8 2,506 1

Index Scan using useview_immunization_ug_reported on useview_immunization_ug i_3 (cost=0.32..277.67 rows=369 width=81) (actual time=0.062..4.660 rows=2,506 loops=1)

  • Index Cond: ((reported >= (((now())::timestamp without time zone - '7 days'::interval))::timestamp without time zone) AND (reported < ((((now())::timestamp without time zone)::timestamp with time zone + '1 day'::interval))::timestamp without time zone))
  • Filter: ((NOT (defaulter)::boolean) AND ((patient_age_in_months)::integer <= 59))
  • Rows Removed by Filter: 1504
63. 22.230 22.230 ↑ 1.0 1 2,470

Index Scan using form_metadata_uuid on form_metadata form_6 (cost=0.56..2.58 rows=1 width=107) (actual time=0.009..0.009 rows=1 loops=2,470)

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

CTE u5_children_registered

65. 4,122.143 36,990.859 ↑ 5,256.5 1,114 1

GroupAggregate (cost=1,627,562.13..1,759,317.09 rows=5,855,776 width=82) (actual time=13,637.009..36,990.859 rows=1,114 loops=1)

  • Group Key: form_7.chw, demo_1.chp_area_uuid, 0
66. 24,118.027 32,868.716 ↓ 1.4 8,228,387 1

Sort (cost=1,627,562.13..1,642,201.57 rows=5,855,776 width=110) (actual time=13,635.606..32,868.716 rows=8,228,387 loops=1)

  • Sort Key: form_7.chw, demo_1.chp_area_uuid
  • Sort Method: external merge Disk: 997192kB
67. 2,818.602 8,750.689 ↓ 1.4 8,228,387 1

Hash Join (cost=453,918.92..774,899.07 rows=5,855,776 width=110) (actual time=4,356.229..8,750.689 rows=8,228,387 loops=1)

  • Hash Cond: (demo_1.chp_area_uuid = form_7.chw_area)
68. 1,617.261 1,617.261 ↓ 1.6 5,322 1

Index Scan using useview_population_demographics_chp_area_reported on useview_population_demographics demo_1 (cost=0.58..116,048.87 rows=3,240 width=71) (actual time=1.333..1,617.261 rows=5,322 loops=1)

  • Index Cond: ((reported >= (((now())::timestamp without time zone - '7 days'::interval))::timestamp without time zone) AND (reported < ((((now())::timestamp without time zone)::timestamp with time zone + '1 day'::interval))::timestamp without time zone))
  • Filter: (age_in_months <= 59)
  • Rows Removed by Filter: 6771
69. 2,542.019 4,314.826 ↑ 1.0 8,320,173 1

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

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

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

71.          

CTE u2_defaulters_treated

72. 0.526 67.713 ↓ 208.0 208 1

GroupAggregate (cost=4,415.67..4,415.69 rows=1 width=82) (actual time=67.139..67.713 rows=208 loops=1)

  • Group Key: form_8.chw, form_8.chw_area, 0
73. 0.550 67.187 ↓ 390.0 390 1

Sort (cost=4,415.67..4,415.67 rows=1 width=110) (actual time=67.125..67.187 rows=390 loops=1)

  • Sort Key: form_8.chw, form_8.chw_area
  • Sort Method: quicksort Memory: 119kB
74. 0.277 66.637 ↓ 390.0 390 1

Nested Loop (cost=3,227.19..4,415.66 rows=1 width=110) (actual time=44.694..66.637 rows=390 loops=1)

75. 6.619 62.460 ↓ 390.0 390 1

Hash Join (cost=3,226.63..4,413.07 rows=1 width=73) (actual time=44.672..62.460 rows=390 loops=1)

  • Hash Cond: ((useview_immunization_ug_2.id = i_4.id) AND ((max(useview_immunization_ug_2.reported)) = i_4.reported))
76. 40.410 48.789 ↓ 1.0 43,194 1

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

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

78. 0.138 7.052 ↓ 33.1 397 1

Hash (cost=411.27..411.27 rows=12 width=117) (actual time=7.052..7.052 rows=397 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 68kB
79. 0.278 6.914 ↓ 33.1 397 1

Hash Semi Join (cost=127.00..411.27 rows=12 width=117) (actual time=2.302..6.914 rows=397 loops=1)

  • Hash Cond: (i_4.id = useview_immunization_ug_follow_up.patient_id)
80. 4.414 4.414 ↓ 2.1 790 1

Index Scan using useview_immunization_ug_reported on useview_immunization_ug i_4 (cost=0.32..283.21 rows=369 width=81) (actual time=0.073..4.414 rows=790 loops=1)

  • Index Cond: ((reported >= (((now())::timestamp without time zone - '7 days'::interval))::timestamp without time zone) AND (reported < ((((now())::timestamp without time zone)::timestamp with time zone + '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: 3220
81. 0.346 2.222 ↓ 1.3 1,539 1

Hash (cost=111.93..111.93 rows=1,181 width=36) (actual time=2.222..2.222 rows=1,539 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 120kB
82. 1.876 1.876 ↓ 1.3 1,539 1

Index Scan using useview_immunization_ug_follow_up_reported on useview_immunization_ug_follow_up (cost=0.31..111.93 rows=1,181 width=36) (actual time=0.450..1.876 rows=1,539 loops=1)

  • Index Cond: ((reported >= (((now())::timestamp without time zone - '7 days'::interval))::timestamp without time zone) AND (reported < ((((now())::timestamp without time zone)::timestamp with time zone + '1 day'::interval))::timestamp without time zone))
83. 3.900 3.900 ↑ 1.0 1 390

Index Scan using form_metadata_uuid on form_metadata form_8 (cost=0.56..2.58 rows=1 width=107) (actual time=0.010..0.010 rows=1 loops=390)

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

CTE u2_defaulters

85. 0.972 73.292 ↓ 374.0 374 1

GroupAggregate (cost=4,508.68..4,508.70 rows=1 width=82) (actual time=72.234..73.292 rows=374 loops=1)

  • Group Key: form_9.chw, form_9.chw_area, 0
86. 1.145 72.320 ↓ 781.0 781 1

Sort (cost=4,508.68..4,508.68 rows=1 width=110) (actual time=72.215..72.320 rows=781 loops=1)

  • Sort Key: form_9.chw, form_9.chw_area
  • Sort Method: quicksort Memory: 216kB
87. 0.359 71.175 ↓ 781.0 781 1

Nested Loop (cost=3,104.48..4,508.67 rows=1 width=110) (actual time=43.841..71.175 rows=781 loops=1)

88. 7.360 63.006 ↓ 781.0 781 1

Hash Join (cost=3,103.93..4,506.08 rows=1 width=73) (actual time=43.814..63.006 rows=781 loops=1)

  • Hash Cond: ((useview_immunization_ug_3.id = i_5.id) AND ((max(useview_immunization_ug_3.reported)) = i_5.reported))
89. 42.343 50.890 ↓ 1.0 43,194 1

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

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

91. 0.247 4.756 ↓ 2.1 790 1

Hash (cost=283.21..283.21 rows=369 width=81) (actual time=4.756..4.756 rows=790 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 99kB
92. 4.509 4.509 ↓ 2.1 790 1

Index Scan using useview_immunization_ug_reported on useview_immunization_ug i_5 (cost=0.32..283.21 rows=369 width=81) (actual time=0.085..4.509 rows=790 loops=1)

  • Index Cond: ((reported >= (((now())::timestamp without time zone - '7 days'::interval))::timestamp without time zone) AND (reported < ((((now())::timestamp without time zone)::timestamp with time zone + '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: 3220
93. 7.810 7.810 ↑ 1.0 1 781

Index Scan using form_metadata_uuid on form_metadata form_9 (cost=0.56..2.58 rows=1 width=107) (actual time=0.010..0.010 rows=1 loops=781)

  • Index Cond: (uuid = i_5.xmlform_uuid)
94. 6.499 73,695.468 ↑ 1.1 5,488 1

Sort (cost=311,926.09..311,941.46 rows=6,149 width=296) (actual time=73,694.771..73,695.468 rows=5,488 loops=1)

  • Sort Key: chwlist.branch_uuid, chwlist.branch_name, (date(chwlist.interval_start)), chwlist.interval_number
  • Sort Method: quicksort Memory: 1140kB
95. 77.163 73,688.969 ↑ 1.1 5,488 1

Hash Left Join (cost=73,715.68..311,539.13 rows=6,149 width=296) (actual time=50,313.614..73,688.969 rows=5,488 loops=1)

  • Hash Cond: ((chwlist.chw_uuid = u2_defaulters.chw_uuid) AND (chwlist.interval_number = u2_defaulters.interval_number))
96. 76.688 73,611.806 ↑ 1.1 5,488 1

Hash Left Join (cost=73,715.64..311,416.11 rows=6,149 width=164) (actual time=50,240.000..73,611.806 rows=5,488 loops=1)

  • Hash Cond: ((chwlist.chw_area_uuid = chw_immunization_pool.chw_area_uuid) AND (chwlist.interval_number = chw_immunization_pool.interval_number))
97. 20,315.745 73,535.118 ↑ 1.1 5,488 1

Hash Left Join (cost=73,703.74..310,850.28 rows=6,149 width=188) (actual time=50,165.589..73,535.118 rows=5,488 loops=1)

  • Hash Cond: ((chwlist.chw_uuid = u2_children_registered.chw_uuid) AND (chwlist.interval_number = u2_children_registered.interval_number))
98. 4.314 53,219.373 ↓ 37.6 5,488 1

Hash Right Join (cost=181.15..161,216.45 rows=146 width=180) (actual time=29,859.378..53,219.373 rows=5,488 loops=1)

  • Hash Cond: ((u5_children_registered.chw_uuid = chwlist.chw_uuid) AND (u5_children_registered.interval_number = chwlist.interval_number))
99. 36,992.705 36,992.705 ↑ 5,256.5 1,114 1

CTE Scan on u5_children_registered (cost=0.00..117,115.52 rows=5,855,776 width=44) (actual time=13,637.012..36,992.705 rows=1,114 loops=1)

100. 2.416 16,222.354 ↓ 5,488.0 5,488 1

Hash (cost=181.13..181.13 rows=1 width=172) (actual time=16,222.354..16,222.354 rows=5,488 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 996kB
101. 326.276 16,219.938 ↓ 5,488.0 5,488 1

Nested Loop Left Join (cost=0.04..181.13 rows=1 width=172) (actual time=14,827.424..16,219.938 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: 1141296
102. 695.360 15,893.662 ↓ 5,488.0 5,488 1

Nested Loop Left Join (cost=0.04..181.10 rows=1 width=164) (actual time=14,759.579..15,893.662 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: 2666685
103. 565.934 15,198.302 ↓ 5,488.0 5,488 1

Nested Loop Left Join (cost=0.04..181.06 rows=1 width=156) (actual time=14,665.934..15,198.302 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: 2161879
104. 3.028 14,632.368 ↓ 5,488.0 5,488 1

Hash Right Join (cost=0.04..181.03 rows=1 width=148) (actual time=14,588.431..14,632.368 rows=5,488 loops=1)

  • Hash Cond: ((chw_pool.chw_area_uuid = chwlist.chw_area_uuid) AND (chw_pool.interval_number = chwlist.interval_number))
105. 14,534.440 14,534.440 ↑ 3.2 2,029 1

CTE Scan on chw_pool (cost=0.00..131.62 rows=6,581 width=44) (actual time=14,493.519..14,534.440 rows=2,029 loops=1)

106. 94.900 94.900 ↓ 5,488.0 5,488 1

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