explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gtpo

Settings
# exclusive inclusive rows x rows loops node
1. 89.695 238,149.220 ↓ 79.1 15,813 1

HashAggregate (cost=3,381,291.68..3,381,302.18 rows=200 width=213) (actual time=238,088.553..238,149.220 rows=15,813 loops=1)

  • Group Key: chwlist.branch_uuid, chwlist.branch_name, chwlist.supervisor_uuid, chwlist.supervisor_name, chwlist.chw_uuid, chwlist.chw_name, chwlist.chw_phone, date(chwlist.interval_start), chwlist.interval_number
  • Functions: 371
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 69.626 ms, Inlining 91.104 ms, Optimization 4064.905 ms, Emission 2663.711 ms, Total 6889.346 ms
2. 9.722 238,059.525 ↑ 463.4 15,813 1

Merge Left Join (cost=2,875,442.53..3,069,892.56 rows=7,327,038 width=245) (actual time=237,992.140..238,059.525 rows=15,813 loops=1)

  • Merge Cond: (chwlist.chw_uuid = form_13.chw)
  • Join Filter: (chwlist.interval_number = (0))
  • Rows Removed by Join Filter: 1014
3. 8.898 237,817.396 ↑ 463.4 15,813 1

Merge Left Join (cost=2,874,299.84..3,031,473.53 rows=7,327,038 width=241) (actual time=237,760.991..237,817.396 rows=15,813 loops=1)

  • Merge Cond: ((chwlist.chw_uuid = u5_children_registered.chw_uuid) AND (chwlist.interval_number = u5_children_registered.interval_number))
4. 8.888 116,917.227 ↑ 4.8 15,813 1

Merge Left Join (cost=1,039,283.15..1,058,119.38 rows=75,241 width=233) (actual time=116,871.066..116,917.227 rows=15,813 loops=1)

  • Merge Cond: ((chwlist.chw_uuid = u2_children_registered.chw_uuid) AND (chwlist.interval_number = u2_children_registered.interval_number))
5. 6.584 21,013.497 ↓ 9.5 15,813 1

Merge Left Join (cost=304,612.96..304,634.17 rows=1,667 width=225) (actual time=20,977.334..21,013.497 rows=15,813 loops=1)

  • Merge Cond: (chwlist.chw_uuid = form_10.chw)
  • Join Filter: (chwlist.interval_number = (0))
  • Rows Removed by Join Filter: 642
6. 7.774 20,316.957 ↓ 9.5 15,813 1

Merge Left Join (cost=303,354.70..303,371.57 rows=1,667 width=217) (actual time=20,287.982..20,316.957 rows=15,813 loops=1)

  • Merge Cond: (chwlist.chw_uuid = form_9.chw)
  • Join Filter: (chwlist.interval_number = (0))
  • Rows Removed by Join Filter: 3342
7. 11.386 20,232.070 ↓ 9.5 15,813 1

Merge Left Join (cost=302,223.61..302,236.13 rows=1,667 width=209) (actual time=20,217.330..20,232.070 rows=15,813 loops=1)

  • Merge Cond: ((chwlist.chw_uuid = u2_child_numerator.chw_uuid) AND (chwlist.interval_number = u2_child_numerator.interval_number))
8. 55.325 20,173.529 ↓ 9.5 15,813 1

Sort (cost=301,092.47..301,096.63 rows=1,667 width=201) (actual time=20,170.307..20,173.529 rows=15,813 loops=1)

  • Sort Key: chwlist.chw_uuid, chwlist.interval_number
  • Sort Method: quicksort Memory: 4585kB
9. 6.776 20,118.204 ↓ 9.5 15,813 1

Merge Left Join (cost=300,888.85..301,003.26 rows=1,667 width=201) (actual time=20,098.802..20,118.204 rows=15,813 loops=1)

  • Merge Cond: ((chwlist.interval_number = chw_pool.interval_number) AND (chwlist.chw_area_uuid = chw_pool.chw_area_uuid))
10. 8.035 14,808.953 ↓ 9.5 15,813 1

Merge Left Join (cost=100,175.00..100,217.21 rows=1,667 width=229) (actual time=14,796.914..14,808.953 rows=15,813 loops=1)

  • Merge Cond: ((chwlist.interval_number = chw_immunization_pool.interval_number) AND (chwlist.chw_area_uuid = chw_immunization_pool.chw_area_uuid))
11. 70.832 6,958.334 ↓ 9.5 15,813 1

Sort (cost=511.03..515.20 rows=1,667 width=221) (actual time=6,954.888..6,958.334 rows=15,813 loops=1)

  • Sort Key: chwlist.interval_number, chwlist.chw_area_uuid
  • Sort Method: quicksort Memory: 4619kB
12. 2.141 6,887.502 ↓ 9.5 15,813 1

Subquery Scan on chwlist (cost=299.70..421.82 rows=1,667 width=221) (actual time=6,871.313..6,887.502 rows=15,813 loops=1)

13. 5.983 6,885.361 ↓ 9.5 15,813 1

Hash Left Join (cost=299.70..405.15 rows=1,667 width=221) (actual time=6,871.310..6,885.361 rows=15,813 loops=1)

  • Hash Cond: (chp.uuid = chp_muting.chw)
  • Filter: ((chp_muting.chw IS NULL) OR ((COALESCE(gss.interval_start, (((date_trunc('quarter'::text, now()))::date - '3 mons'::interval))::timestamp with time zone)) < chp_muting.start_on) OR ((COALESCE(gss.interval_start, (((date_trunc('quarter'::text, now()))::date - '3 mons'::interval))::timestamp with time zone)) > chp_muting.end_on))
  • Rows Removed by Filter: 4878
14. 29.541 6,879.162 ↓ 6.9 20,691 1

HashAggregate (cost=277.83..345.33 rows=3,000 width=233) (actual time=6,871.074..6,879.162 rows=20,691 loops=1)

  • Group Key: cmd.uuid, cmd.name, chp.supervisor_uuid, chp.area_uuid, cmeta.name, chp.uuid, chp.name, chp.phone, gss.interval_start, gsn.interval_number
15. 6.658 6,849.621 ↓ 6.9 20,691 1

Nested Loop Left Join (cost=4.62..202.83 rows=3,000 width=221) (actual time=6,822.327..6,849.621 rows=20,691 loops=1)

16. 2.402 6,842.963 ↓ 6.9 6,897 1

Nested Loop Left Join (cost=4.55..142.75 rows=1,000 width=217) (actual time=6,822.310..6,842.963 rows=6,897 loops=1)

17. 0.173 6,840.561 ↓ 2,299.0 2,299 1

Nested Loop (cost=4.52..122.72 rows=1 width=209) (actual time=6,822.290..6,840.561 rows=2,299 loops=1)

18. 1.187 6,824.295 ↓ 2,299.0 2,299 1

Hash Join (cost=4.09..120.45 rows=1 width=192) (actual time=6,822.256..6,824.295 rows=2,299 loops=1)

  • Hash Cond: (chp.branch_uuid = cmd.uuid)
19. 0.893 0.893 ↑ 1.0 2,405 1

Seq Scan on contactview_chp chp (cost=0.00..110.05 rows=2,405 width=172) (actual time=0.022..0.893 rows=2,405 loops=1)

20. 0.012 6,822.215 ↑ 2.4 13 1

Hash (cost=3.70..3.70 rows=31 width=53) (actual time=6,822.214..6,822.215 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 6,822.203 6,822.203 ↑ 2.4 13 1

Index Scan using contactview_metadata_type on contactview_metadata cmd (cost=0.43..3.70 rows=31 width=53) (actual time=6,822.162..6,822.203 rows=13 loops=1)

  • Index Cond: (type = 'district_hospital'::text)
  • Filter: ((name <> 'HQ'::text) AND (name <> 'HQ OVC'::text))
  • Rows Removed by Filter: 1
22. 16.093 16.093 ↑ 1.0 1 2,299

Index Scan using contactview_metadata_uuid on contactview_metadata cmeta (cost=0.43..2.27 rows=1 width=53) (actual time=0.007..0.007 rows=1 loops=2,299)

  • Index Cond: (uuid = chp.supervisor_uuid)
23. 0.000 0.000 ↑ 333.3 3 2,299

Function Scan on generate_series gss (cost=0.03..10.03 rows=1,000 width=8) (actual time=0.000..0.000 rows=3 loops=2,299)

24. 0.000 0.000 ↑ 1.0 3 6,897

Function Scan on generate_series gsn (cost=0.07..0.10 rows=3 width=4) (actual time=0.000..0.000 rows=3 loops=6,897)

25. 0.115 0.216 ↑ 1.0 661 1

Hash (cost=13.61..13.61 rows=661 width=45) (actual time=0.216..0.216 rows=661 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 60kB
26. 0.101 0.101 ↑ 1.0 661 1

Seq Scan on chp_muting (cost=0.00..13.61 rows=661 width=45) (actual time=0.019..0.101 rows=661 loops=1)

27. 4.545 7,842.584 ↓ 1.1 3,963 1

Sort (cost=99,663.96..99,673.35 rows=3,753 width=44) (actual time=7,841.984..7,842.584 rows=3,963 loops=1)

  • Sort Key: chw_immunization_pool.interval_number, chw_immunization_pool.chw_area_uuid
  • Sort Method: quicksort Memory: 239kB
28. 0.339 7,838.039 ↑ 2.8 1,353 1

Subquery Scan on chw_immunization_pool (cost=98,896.93..99,441.15 rows=3,753 width=44) (actual time=7,820.571..7,838.039 rows=1,353 loops=1)

29. 13.120 7,837.700 ↑ 2.8 1,353 1

GroupAggregate (cost=98,896.93..99,403.62 rows=3,753 width=76) (actual time=7,820.568..7,837.700 rows=1,353 loops=1)

  • Group Key: x.chw, x.chw_area_uuid, 0
30. 52.395 7,824.580 ↑ 1.7 21,781 1

Sort (cost=98,896.93..98,990.76 rows=37,533 width=68) (actual time=7,820.522..7,824.580 rows=21,781 loops=1)

  • Sort Key: x.chw, x.chw_area_uuid
  • Sort Method: quicksort Memory: 3831kB
31. 4.407 7,772.185 ↑ 1.7 21,781 1

Subquery Scan on x (cost=95,294.53..96,045.19 rows=37,533 width=68) (actual time=7,758.568..7,772.185 rows=21,781 loops=1)

32. 72.374 7,767.778 ↑ 1.7 21,781 1

HashAggregate (cost=95,294.53..95,669.86 rows=37,533 width=104) (actual time=7,758.565..7,767.778 rows=21,781 loops=1)

  • Group Key: i.uuid, i.reported, form.chw, form.chw_area
33. 8.910 7,695.404 ↑ 1.0 37,411 1

Append (cost=1.01..94,919.20 rows=37,533 width=104) (actual time=0.477..7,695.404 rows=37,411 loops=1)

34. 26.579 5,754.832 ↑ 1.0 15,630 1

Nested Loop (cost=1.01..38,520.60 rows=15,678 width=117) (actual time=0.476..5,754.832 rows=15,630 loops=1)

35. 163.973 163.973 ↑ 1.0 15,630 1

Index Only Scan using useview_immunization_registration_dashboard_uuid on useview_immunization_registration_dashboard i (cost=0.45..1,279.75 rows=15,678 width=45) (actual time=0.043..163.973 rows=15,630 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '3 mons'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '1 day'::interval))::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
  • Heap Fetches: 15630
36. 5,564.280 5,564.280 ↑ 1.0 1 15,630

Index Scan using form_metadata_uuid on form_metadata form (cost=0.56..2.38 rows=1 width=108) (actual time=0.356..0.356 rows=1 loops=15,630)

  • Index Cond: (uuid = i.uuid)
37. 1.752 459.235 ↑ 1.0 1,532 1

Nested Loop (cost=1.01..5,181.61 rows=1,538 width=117) (actual time=0.978..459.235 rows=1,532 loops=1)

38. 31.587 31.587 ↑ 1.0 1,532 1

Index Only Scan using useview_immunization_follow_up_dashboard_uuid on useview_immunization_follow_up_dashboard f (cost=0.45..1,253.26 rows=1,538 width=45) (actual time=0.460..31.587 rows=1,532 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '3 mons'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '1 day'::interval))::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
  • Heap Fetches: 1532
39. 425.896 425.896 ↑ 1.0 1 1,532

Index Scan using form_metadata_uuid on form_metadata form_1 (cost=0.56..2.55 rows=1 width=108) (actual time=0.278..0.278 rows=1 loops=1,532)

  • Index Cond: (uuid = f.uuid)
40. 9.307 210.205 ↑ 1.0 15,630 1

Nested Loop (cost=1.01..38,520.60 rows=15,678 width=117) (actual time=0.086..210.205 rows=15,630 loops=1)

41. 28.968 28.968 ↑ 1.0 15,630 1

Index Only Scan using useview_immunization_registration_dashboard_uuid on useview_immunization_registration_dashboard r (cost=0.45..1,279.75 rows=15,678 width=45) (actual time=0.061..28.968 rows=15,630 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '3 mons'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '1 day'::interval))::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
  • Heap Fetches: 15630
42. 171.930 171.930 ↑ 1.0 1 15,630

Index Scan using form_metadata_uuid on form_metadata form_2 (cost=0.56..2.38 rows=1 width=108) (actual time=0.011..0.011 rows=1 loops=15,630)

  • Index Cond: (uuid = r.uuid)
43. 8.582 1,262.222 ↑ 1.0 4,619 1

Nested Loop (cost=1.00..12,133.39 rows=4,639 width=117) (actual time=1.073..1,262.222 rows=4,619 loops=1)

44. 61.938 61.938 ↑ 1.0 4,619 1

Index Only Scan using useview_immunization_visit_dashboard_uuid on useview_immunization_visit_dashboard v (cost=0.45..483.97 rows=4,639 width=45) (actual time=0.451..61.938 rows=4,619 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '3 mons'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '1 day'::interval))::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
  • Heap Fetches: 4619
45. 1,191.702 1,191.702 ↑ 1.0 1 4,619

Index Scan using form_metadata_uuid on form_metadata form_3 (cost=0.56..2.51 rows=1 width=108) (actual time=0.258..0.258 rows=1 loops=4,619)

  • Index Cond: (uuid = v.uuid)
46. 5.001 5,302.475 ↑ 1.8 4,380 1

Sort (cost=200,713.85..200,734.02 rows=8,067 width=44) (actual time=5,301.861..5,302.475 rows=4,380 loops=1)

  • Sort Key: chw_pool.interval_number, chw_pool.chw_area_uuid
  • Sort Method: quicksort Memory: 261kB
47. 0.395 5,297.474 ↑ 5.3 1,510 1

Subquery Scan on chw_pool (cost=199,020.73..200,190.39 rows=8,067 width=44) (actual time=5,253.669..5,297.474 rows=1,510 loops=1)

48. 30.889 5,297.079 ↑ 5.3 1,510 1

GroupAggregate (cost=199,020.73..200,109.72 rows=8,067 width=76) (actual time=5,253.667..5,297.079 rows=1,510 loops=1)

  • Group Key: x_1.chw, x_1.chw_area_uuid, 0
49. 138.285 5,266.190 ↑ 1.4 55,995 1

Sort (cost=199,020.73..199,222.39 rows=80,666 width=68) (actual time=5,253.622..5,266.190 rows=55,995 loops=1)

  • Sort Key: x_1.chw, x_1.chw_area_uuid
  • Sort Method: quicksort Memory: 9411kB
50. 11.140 5,127.905 ↑ 1.4 55,995 1

Subquery Scan on x_1 (cost=190,833.26..192,446.58 rows=80,666 width=68) (actual time=5,093.175..5,127.905 rows=55,995 loops=1)

51. 134.911 5,116.765 ↑ 1.4 55,995 1

HashAggregate (cost=190,833.26..191,639.92 rows=80,666 width=104) (actual time=5,093.173..5,116.765 rows=55,995 loops=1)

  • Group Key: i_1.uuid, i_1.reported, form_4.chw, form_4.chw_area
52. 17.385 4,981.854 ↑ 1.0 80,651 1

Append (cost=0.56..190,026.60 rows=80,666 width=104) (actual time=0.059..4,981.854 rows=80,651 loops=1)

53. 27.046 1,983.322 ↓ 1.0 24,656 1

Nested Loop (cost=0.56..56,971.59 rows=24,654 width=117) (actual time=0.058..1,983.322 rows=24,656 loops=1)

54. 8.452 8.452 ↓ 1.0 24,656 1

Seq Scan on useview_immunization_registration_dashboard i_1 (cost=0.00..779.54 rows=24,654 width=45) (actual time=0.017..8.452 rows=24,656 loops=1)

55. 1,947.824 1,947.824 ↑ 1.0 1 24,656

Index Scan using form_metadata_uuid on form_metadata form_4 (cost=0.56..2.28 rows=1 width=108) (actual time=0.079..0.079 rows=1 loops=24,656)

  • Index Cond: (uuid = i_1.uuid)
56. 28.663 2,278.715 ↑ 1.0 22,122 1

Nested Loop (cost=0.56..51,971.24 rows=22,144 width=117) (actual time=0.042..2,278.715 rows=22,122 loops=1)

57. 15.730 15.730 ↑ 1.0 22,122 1

Seq Scan on useview_immunization_follow_up_dashboard f_1 (cost=0.00..929.44 rows=22,144 width=45) (actual time=0.006..15.730 rows=22,122 loops=1)

58. 2,234.322 2,234.322 ↑ 1.0 1 22,122

Index Scan using form_metadata_uuid on form_metadata form_5 (cost=0.56..2.30 rows=1 width=108) (actual time=0.101..0.101 rows=1 loops=22,122)

  • Index Cond: (uuid = f_1.uuid)
59. 23.527 325.406 ↓ 1.0 24,656 1

Nested Loop (cost=0.56..56,971.59 rows=24,654 width=117) (actual time=0.049..325.406 rows=24,656 loops=1)

60. 6.007 6.007 ↓ 1.0 24,656 1

Seq Scan on useview_immunization_registration_dashboard r_1 (cost=0.00..779.54 rows=24,654 width=45) (actual time=0.007..6.007 rows=24,656 loops=1)

61. 295.872 295.872 ↑ 1.0 1 24,656

Index Scan using form_metadata_uuid on form_metadata form_6 (cost=0.56..2.28 rows=1 width=108) (actual time=0.012..0.012 rows=1 loops=24,656)

  • Index Cond: (uuid = r_1.uuid)
62. 13.207 377.026 ↓ 1.0 9,217 1

Nested Loop (cost=0.56..22,902.19 rows=9,214 width=117) (actual time=0.039..377.026 rows=9,217 loops=1)

63. 4.356 4.356 ↓ 1.0 9,217 1

Seq Scan on useview_immunization_visit_dashboard v_1 (cost=0.00..307.14 rows=9,214 width=45) (actual time=0.008..4.356 rows=9,217 loops=1)

64. 359.463 359.463 ↑ 1.0 1 9,217

Index Scan using form_metadata_uuid on form_metadata form_7 (cost=0.56..2.45 rows=1 width=108) (actual time=0.039..0.039 rows=1 loops=9,217)

  • Index Cond: (uuid = v_1.uuid)
65. 0.364 47.155 ↓ 1,138.0 1,138 1

Sort (cost=1,131.14..1,131.15 rows=1 width=48) (actual time=46.996..47.155 rows=1,138 loops=1)

  • Sort Key: u2_child_numerator.chw_uuid, u2_child_numerator.interval_number
  • Sort Method: quicksort Memory: 79kB
66. 0.079 46.791 ↓ 386.0 386 1

Subquery Scan on u2_child_numerator (cost=1,131.10..1,131.13 rows=1 width=48) (actual time=45.902..46.791 rows=386 loops=1)

67. 0.655 46.712 ↓ 386.0 386 1

GroupAggregate (cost=1,131.10..1,131.12 rows=1 width=84) (actual time=45.899..46.712 rows=386 loops=1)

  • Group Key: form_8.chw, form_8.chw_area, 0
68. 3.227 46.057 ↓ 1,430.0 1,430 1

Sort (cost=1,131.10..1,131.10 rows=1 width=113) (actual time=45.872..46.057 rows=1,430 loops=1)

  • Sort Key: form_8.chw, form_8.chw_area
  • Sort Method: quicksort Memory: 428kB
69. 1.521 42.830 ↓ 1,430.0 1,430 1

Nested Loop (cost=918.92..1,131.09 rows=1 width=113) (actual time=17.368..42.830 rows=1,430 loops=1)

70. 2.785 22.719 ↓ 1,430.0 1,430 1

Hash Join (cost=918.36..1,128.52 rows=1 width=74) (actual time=17.340..22.719 rows=1,430 loops=1)

  • Hash Cond: ((useview_immunization_visit_dashboard.patient_id = i_2.patient_id) AND ((max(useview_immunization_visit_dashboard.reported)) = i_2.reported))
71. 7.892 9.937 ↓ 1.0 7,008 1

HashAggregate (cost=353.21..423.26 rows=7,005 width=45) (actual time=7.308..9.937 rows=7,008 loops=1)

  • Group Key: useview_immunization_visit_dashboard.patient_id
72. 2.045 2.045 ↓ 1.0 9,217 1

Seq Scan on useview_immunization_visit_dashboard (cost=0.00..307.14 rows=9,214 width=45) (actual time=0.015..2.045 rows=9,217 loops=1)

73. 1.186 9.997 ↓ 3.0 2,309 1

Hash (cost=553.55..553.55 rows=773 width=82) (actual time=9.997..9.997 rows=2,309 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 296kB
74. 8.811 8.811 ↓ 3.0 2,309 1

Index Scan using useview_immunization_visit_dashboard_uuid on useview_immunization_visit_dashboard i_2 (cost=0.45..553.55 rows=773 width=82) (actual time=0.058..8.811 rows=2,309 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '3 mons'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '1 day'::interval))::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
  • Filter: ((patient_age_in_months <> ''::text) AND (NOT (defaulter)::boolean) AND ((patient_age_in_months)::integer <= 23))
  • Rows Removed by Filter: 2310
75. 18.590 18.590 ↑ 1.0 1 1,430

Index Scan using form_metadata_uuid on form_metadata form_8 (cost=0.56..2.57 rows=1 width=108) (actual time=0.013..0.013 rows=1 loops=1,430)

  • Index Cond: (uuid = i_2.uuid)
76. 1.017 77.113 ↓ 5,020.0 5,020 1

Materialize (cost=1,131.10..1,131.13 rows=1 width=48) (actual time=70.643..77.113 rows=5,020 loops=1)

77. 4.806 76.096 ↓ 572.0 572 1

GroupAggregate (cost=1,131.10..1,131.12 rows=1 width=84) (actual time=70.640..76.096 rows=572 loops=1)

  • Group Key: form_9.chw, form_9.chw_area, 0
78. 6.801 71.290 ↓ 2,857.0 2,857 1

Sort (cost=1,131.10..1,131.10 rows=1 width=113) (actual time=70.601..71.290 rows=2,857 loops=1)

  • Sort Key: form_9.chw, form_9.chw_area
  • Sort Method: quicksort Memory: 855kB
79. 2.559 64.489 ↓ 2,857.0 2,857 1

Nested Loop (cost=918.92..1,131.09 rows=1 width=113) (actual time=18.387..64.489 rows=2,857 loops=1)

80. 3.687 24.789 ↓ 2,857.0 2,857 1

Hash Join (cost=918.36..1,128.52 rows=1 width=74) (actual time=18.359..24.789 rows=2,857 loops=1)

  • Hash Cond: ((useview_immunization_visit_dashboard_1.patient_id = i_3.patient_id) AND ((max(useview_immunization_visit_dashboard_1.reported)) = i_3.reported))
81. 8.169 10.103 ↓ 1.0 7,008 1

HashAggregate (cost=353.21..423.26 rows=7,005 width=45) (actual time=7.330..10.103 rows=7,008 loops=1)

  • Group Key: useview_immunization_visit_dashboard_1.patient_id
82. 1.934 1.934 ↓ 1.0 9,217 1

Seq Scan on useview_immunization_visit_dashboard useview_immunization_visit_dashboard_1 (cost=0.00..307.14 rows=9,214 width=45) (actual time=0.011..1.934 rows=9,217 loops=1)

83. 1.940 10.999 ↓ 5.1 3,939 1

Hash (cost=553.55..553.55 rows=773 width=82) (actual time=10.998..10.999 rows=3,939 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 483kB
84. 9.059 9.059 ↓ 5.1 3,939 1

Index Scan using useview_immunization_visit_dashboard_uuid on useview_immunization_visit_dashboard i_3 (cost=0.45..553.55 rows=773 width=82) (actual time=0.057..9.059 rows=3,939 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '3 mons'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '1 day'::interval))::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
  • Filter: ((patient_age_in_months <> ''::text) AND (NOT (defaulter)::boolean) AND ((patient_age_in_months)::integer <= 59))
  • Rows Removed by Filter: 680
85. 37.141 37.141 ↑ 1.0 1 2,857

Index Scan using form_metadata_uuid on form_metadata form_9 (cost=0.56..2.57 rows=1 width=108) (actual time=0.013..0.013 rows=1 loops=2,857)

  • Index Cond: (uuid = i_3.uuid)
86. 0.177 689.956 ↓ 956.0 956 1

Materialize (cost=1,258.26..1,258.29 rows=1 width=48) (actual time=689.344..689.956 rows=956 loops=1)

87. 0.435 689.779 ↓ 108.0 108 1

GroupAggregate (cost=1,258.26..1,258.28 rows=1 width=84) (actual time=689.340..689.779 rows=108 loops=1)

  • Group Key: form_10.chw, form_10.chw_area, 0
88. 0.501 689.344 ↓ 166.0 166 1

Sort (cost=1,258.26..1,258.26 rows=1 width=113) (actual time=689.297..689.344 rows=166 loops=1)

  • Sort Key: form_10.chw, form_10.chw_area
  • Sort Method: quicksort Memory: 69kB
89. 1.518 688.843 ↓ 166.0 166 1

Nested Loop Semi Join (cost=930.96..1,258.25 rows=1 width=113) (actual time=30.180..688.843 rows=166 loops=1)

  • Join Filter: (i_4.patient_id = useview_immunization_referral_follow_up_dashboard.patient_id)
90. 0.538 27.325 ↓ 264.0 264 1

Nested Loop (cost=930.51..1,142.68 rows=1 width=146) (actual time=16.414..27.325 rows=264 loops=1)

91. 2.986 22.299 ↓ 264.0 264 1

Hash Join (cost=929.96..1,140.12 rows=1 width=111) (actual time=16.386..22.299 rows=264 loops=1)

  • Hash Cond: ((useview_immunization_visit_dashboard_2.patient_id = i_4.patient_id) AND ((max(useview_immunization_visit_dashboard_2.reported)) = i_4.reported))
92. 8.427 10.381 ↓ 1.0 7,008 1

HashAggregate (cost=353.21..423.26 rows=7,005 width=45) (actual time=7.416..10.381 rows=7,008 loops=1)

  • Group Key: useview_immunization_visit_dashboard_2.patient_id
93. 1.954 1.954 ↓ 1.0 9,217 1

Seq Scan on useview_immunization_visit_dashboard useview_immunization_visit_dashboard_2 (cost=0.00..307.14 rows=9,214 width=45) (actual time=0.011..1.954 rows=9,217 loops=1)

94. 0.239 8.932 ↑ 2.0 392 1

Hash (cost=565.15..565.15 rows=773 width=82) (actual time=8.932..8.932 rows=392 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 53kB
95. 8.693 8.693 ↑ 2.0 392 1

Index Scan using useview_immunization_visit_dashboard_uuid on useview_immunization_visit_dashboard i_4 (cost=0.45..565.15 rows=773 width=82) (actual time=0.068..8.693 rows=392 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '3 mons'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '1 day'::interval))::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
  • Filter: ((patient_age_in_months <> ''::text) AND (patient_age_in_months <> ''::text) AND (defaulter)::boolean AND ((patient_age_in_months)::integer <= 23))
  • Rows Removed by Filter: 4227
96. 4.488 4.488 ↑ 1.0 1 264

Index Scan using form_metadata_uuid on form_metadata form_10 (cost=0.56..2.57 rows=1 width=108) (actual time=0.017..0.017 rows=1 loops=264)

  • Index Cond: (uuid = i_4.uuid)
97. 660.000 660.000 ↑ 1.0 1 264

Index Only Scan using useview_immunization_referral_follow_up_dashboard_uuid on useview_immunization_referral_follow_up_dashboard (cost=0.45..115.55 rows=1 width=37) (actual time=2.500..2.500 rows=1 loops=264)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '3 mons'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '1 day'::interval))::timestamp with time zone) + '1 day'::interval))::timestamp without time zone) AND (patient_id = useview_immunization_visit_dashboard_2.patient_id))
  • Heap Fetches: 166
98. 0.903 95,894.842 ↑ 391.3 4,614 1

Materialize (cost=734,670.19..743,697.33 rows=1,805,428 width=48) (actual time=95,893.704..95,894.842 rows=4,614 loops=1)

99. 3.356 95,893.939 ↑ 1,142.7 1,580 1

Sort (cost=734,670.19..739,183.76 rows=1,805,428 width=48) (actual time=95,893.701..95,893.939 rows=1,580 loops=1)

  • Sort Key: u2_children_registered.chw_uuid, u2_children_registered.interval_number
  • Sort Method: quicksort Memory: 271kB
100. 1.629 95,890.583 ↑ 1,142.7 1,580 1

Subquery Scan on u2_children_registered (cost=454,278.95..515,312.93 rows=1,805,428 width=48) (actual time=71,264.335..95,890.583 rows=1,580 loops=1)

101. 13,265.294 95,888.954 ↑ 1,142.7 1,580 1

GroupAggregate (cost=454,278.95..497,258.65 rows=1,805,428 width=84) (actual time=71,264.332..95,888.954 rows=1,580 loops=1)

  • Group Key: form_11.chw, demo.chp_area_uuid, 0
102. 40,584.993 82,623.660 ↓ 9.2 18,425,442 1

Sort (cost=454,278.95..459,264.03 rows=1,994,034 width=112) (actual time=71,263.062..82,623.660 rows=18,425,442 loops=1)

  • Sort Key: form_11.chw, demo.chp_area_uuid
  • Sort Method: external merge Disk: 2290032kB
103. 4,987.641 42,038.667 ↓ 9.2 18,425,442 1

Nested Loop (cost=1.02..179,420.62 rows=1,994,034 width=112) (actual time=0.969..42,038.667 rows=18,425,442 loops=1)

104. 70.461 70.461 ↓ 7.7 6,437 1

Index Scan using useview_population_demographics_reported_idx on useview_population_demographics demo (cost=0.46..13,594.72 rows=834 width=72) (actual time=0.132..70.461 rows=6,437 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '3 mons'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '1 day'::interval))::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
  • Filter: (age_in_months <= 23)
  • Rows Removed by Filter: 9230
105. 36,980.565 36,980.565 ↓ 1.3 2,862 6,437

Index Scan using form_metadata_chw_area on form_metadata form_11 (cost=0.56..176.40 rows=2,243 width=72) (actual time=0.232..5.745 rows=2,862 loops=6,437)

  • Index Cond: (chw_area = demo.chp_area_uuid)
106. 1.131 120,891.271 ↑ 824.6 4,724 1

Materialize (cost=1,835,016.69..1,854,492.87 rows=3,895,237 width=48) (actual time=120,889.899..120,891.271 rows=4,724 loops=1)

107. 3.295 120,890.140 ↑ 2,401.5 1,622 1

Sort (cost=1,835,016.69..1,844,754.78 rows=3,895,237 width=48) (actual time=120,889.895..120,890.140 rows=1,622 loops=1)

  • Sort Key: u5_children_registered.chw_uuid, u5_children_registered.interval_number
  • Sort Method: quicksort Memory: 277kB
108. 1.760 120,886.845 ↑ 2,401.5 1,622 1

Subquery Scan on u5_children_registered (cost=1,186,360.28..1,340,147.12 rows=3,895,237 width=48) (actual time=87,510.848..120,886.845 rows=1,622 loops=1)

109. 18,471.473 120,885.085 ↑ 2,401.5 1,622 1

GroupAggregate (cost=1,186,360.28..1,301,194.75 rows=3,895,237 width=84) (actual time=87,510.845..120,885.085 rows=1,622 loops=1)

  • Group Key: form_12.chw, demo_1.chp_area_uuid, 0
110. 53,163.836 102,413.612 ↓ 3.8 23,000,142 1

Sort (cost=1,186,360.28..1,201,536.70 rows=6,070,568 width=112) (actual time=87,507.419..102,413.612 rows=23,000,142 loops=1)

  • Sort Key: form_12.chw, demo_1.chp_area_uuid
  • Sort Method: external merge Disk: 2858608kB
111. 6,969.516 49,249.776 ↓ 3.8 23,000,142 1

Nested Loop (cost=1.02..300,845.59 rows=6,070,568 width=112) (actual time=0.156..49,249.776 rows=23,000,142 loops=1)

112. 89.864 89.864 ↓ 3.2 8,084 1

Index Scan using useview_population_demographics_reported_idx on useview_population_demographics demo_1 (cost=0.46..13,594.72 rows=2,539 width=72) (actual time=0.106..89.864 rows=8,084 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '3 mons'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '1 day'::interval))::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
  • Filter: (age_in_months <= 59)
  • Rows Removed by Filter: 7583
113. 42,190.396 42,190.396 ↓ 1.3 2,845 8,084

Index Scan using form_metadata_chw_area on form_metadata form_12 (cost=0.56..90.71 rows=2,243 width=72) (actual time=0.032..5.219 rows=2,845 loops=8,084)

  • Index Cond: (chw_area = demo_1.chp_area_uuid)
114. 0.310 232.407 ↓ 1,516.0 1,516 1

Materialize (cost=1,142.69..1,142.73 rows=1 width=48) (actual time=231.139..232.407 rows=1,516 loops=1)

115. 0.927 232.097 ↓ 172.0 172 1

GroupAggregate (cost=1,142.69..1,142.72 rows=1 width=84) (actual time=231.135..232.097 rows=172 loops=1)

  • Group Key: form_13.chw, form_13.chw_area, 0
116. 0.752 231.170 ↓ 264.0 264 1

Sort (cost=1,142.69..1,142.70 rows=1 width=113) (actual time=231.098..231.170 rows=264 loops=1)

  • Sort Key: form_13.chw, form_13.chw_area
  • Sort Method: quicksort Memory: 95kB
117. 0.646 230.418 ↓ 264.0 264 1

Nested Loop (cost=930.51..1,142.68 rows=1 width=113) (actual time=55.715..230.418 rows=264 loops=1)

118. 2.386 47.612 ↓ 264.0 264 1

Hash Join (cost=929.96..1,140.12 rows=1 width=74) (actual time=42.491..47.612 rows=264 loops=1)

  • Hash Cond: ((useview_immunization_visit_dashboard_3.patient_id = i_5.patient_id) AND ((max(useview_immunization_visit_dashboard_3.reported)) = i_5.reported))
119. 7.690 25.994 ↓ 1.0 7,008 1

HashAggregate (cost=353.21..423.26 rows=7,005 width=45) (actual time=23.216..25.994 rows=7,008 loops=1)

  • Group Key: useview_immunization_visit_dashboard_3.patient_id
120. 18.304 18.304 ↓ 1.0 9,217 1

Seq Scan on useview_immunization_visit_dashboard useview_immunization_visit_dashboard_3 (cost=0.00..307.14 rows=9,214 width=45) (actual time=6.714..18.304 rows=9,217 loops=1)

121. 0.280 19.232 ↑ 2.0 392 1

Hash (cost=565.15..565.15 rows=773 width=82) (actual time=19.232..19.232 rows=392 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 53kB
122. 18.952 18.952 ↑ 2.0 392 1

Index Scan using useview_immunization_visit_dashboard_uuid on useview_immunization_visit_dashboard i_5 (cost=0.45..565.15 rows=773 width=82) (actual time=8.278..18.952 rows=392 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '3 mons'::interval))::timestamp with time zone))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, (((date_trunc('quarter'::text, now()))::date - '1 day'::interval))::timestamp with time zone) + '1 day'::interval))::timestamp without time zone))
  • Filter: ((patient_age_in_months <> ''::text) AND (patient_age_in_months <> ''::text) AND (defaulter)::boolean AND ((patient_age_in_months)::integer <= 23))
  • Rows Removed by Filter: 4227
123. 182.160 182.160 ↑ 1.0 1 264

Index Scan using form_metadata_uuid on form_metadata form_13 (cost=0.56..2.57 rows=1 width=108) (actual time=0.690..0.690 rows=1 loops=264)

  • Index Cond: (uuid = i_5.uuid)
Execution time : 238,936.605 ms