explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mHo4

Settings
# exclusive inclusive rows x rows loops node
1. 3.644 378,659.697 ↓ 6.5 202 1

GroupAggregate (cost=874,987.12..874,993.16 rows=31 width=300) (actual time=378,655.991..378,659.697 rows=202 loops=1)

  • Group Key: cmd.uuid, cmd.name, cmeta.uuid, cmeta.name, ''::text, ''::text, ''::text, ''::text, (date((date_trunc('day'::text, date_trunc('month'::text, now()))))), (0)
2. 1.140 378,656.053 ↓ 6.5 202 1

Sort (cost=874,987.12..874,987.20 rows=31 width=568) (actual time=378,655.860..378,656.053 rows=202 loops=1)

  • Sort Key: cmd.uuid, cmd.name, cmeta.uuid, cmeta.name, (date((date_trunc('day'::text, date_trunc('month'::text, now()))))), (0)
  • Sort Method: quicksort Memory: 73kB
3. 0.872 378,654.913 ↓ 6.5 202 1

Merge Right Join (cost=874,801.18..874,986.35 rows=31 width=568) (actual time=378,637.093..378,654.913 rows=202 loops=1)

  • Merge Cond: ((active_visited.supervisor_uuid = cmeta.uuid) AND (active_visited.interval_number = (0)))
4. 11.652 377,409.521 ↑ 5.0 146 1

GroupAggregate (cost=867,891.19..868,064.45 rows=730 width=204) (actual time=377,393.605..377,409.521 rows=146 loops=1)

  • Group Key: active_visited.supervisor_uuid, active_visited.interval_number
5.          

CTE active_cte

6. 219.754 352,095.339 ↑ 1.0 7,241 1

GroupAggregate (cost=758.02..854,814.61 rows=7,295 width=83) (actual time=945.662..352,095.339 rows=7,241 loops=1)

  • Group Key: chp.supervisor_uuid, chp.uuid, 0
7. 41.272 48.603 ↑ 1.0 7,241 1

Sort (cost=758.02..776.26 rows=7,295 width=75) (actual time=39.081..48.603 rows=7,241 loops=1)

  • Sort Key: chp.supervisor_uuid, chp.uuid
  • Sort Method: quicksort Memory: 1,211kB
8. 7.331 7.331 ↑ 1.0 7,241 1

Seq Scan on contactview_chp chp (cost=0.00..289.95 rows=7,295 width=75) (actual time=0.012..7.331 rows=7,241 loops=1)

9.          

SubPlan (for GroupAggregate)

10. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=96.20..97.33 rows=1 width=0) (never executed)

  • Join Filter: (meta_1.formname = "*VALUES*".column1)
11. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on form_metadata meta_1 (cost=96.20..97.23 rows=1 width=14) (never executed)

  • Recheck Cond: ((chw = chp.uuid) AND (reported >= (date_trunc('day'::text, date_trunc('month'::text, now())))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
12. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=96.20..96.20 rows=1 width=0) (never executed)

13. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on form_metadata_chw (cost=0.00..23.22 rows=1,422 width=0) (never executed)

  • Index Cond: (chw = chp.uuid)
14. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on form_metadata_reported (cost=0.00..72.72 rows=5,427 width=0) (never executed)

  • Index Cond: ((reported >= (date_trunc('day'::text, date_trunc('month'::text, now())))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
15. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..0.05 rows=3 width=32) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=32) (never executed)

17. 301.871 810.342 ↓ 215.8 100,338 1

Hash Semi Join (cost=0.53..2,127.13 rows=465 width=35) (actual time=0.141..810.342 rows=100,338 loops=1)

  • Hash Cond: (meta_2.formname = "*VALUES*_1".column1)
18. 508.459 508.459 ↓ 60.3 327,022 1

Index Scan using form_metadata_reported on form_metadata meta_2 (cost=0.45..2,107.64 rows=5,427 width=49) (actual time=0.109..508.459 rows=327,022 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, date_trunc('month'::text, now())))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
19. 0.007 0.012 ↑ 1.0 3 1

Hash (cost=0.04..0.04 rows=3 width=32) (actual time=0.012..0.012 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
20. 0.005 0.005 ↑ 1.0 3 1

Values Scan on "*VALUES*_1" (cost=0.00..0.04 rows=3 width=32) (actual time=0.003..0.005 rows=3 loops=1)

21. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.79..11.45 rows=1 width=0) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.23..8.85 rows=1 width=450) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.67..6.25 rows=1 width=36) (never executed)

  • Join Filter: (chw.parent_uuid = cmd_1.parent_uuid)
24. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.11..5.15 rows=1 width=108) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Index Scan using contactview_metadata_uuid on contactview_metadata chw (cost=0.56..2.57 rows=1 width=72) (never executed)

  • Index Cond: (uuid = chp.uuid)
26. 0.000 0.000 ↓ 0.0 0

Index Only Scan using contactview_metadata_uuid on contactview_metadata chwarea (cost=0.56..2.57 rows=1 width=36) (never executed)

  • Index Cond: (uuid = chw.parent_uuid)
  • Heap Fetches: 0
27. 0.000 0.000 ↓ 0.0 0

Index Scan using contactview_metadata_parent_uuid on contactview_metadata cmd_1 (cost=0.56..1.08 rows=1 width=36) (never executed)

  • Index Cond: (parent_uuid = chwarea.uuid)
  • Filter: ((type = 'clinic'::text) AND (reported >= (date_trunc('day'::text, date_trunc('month'::text, now())))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
28. 0.000 0.000 ↓ 0.0 0

Index Scan using couchdb_form_id on couchdb (cost=0.56..2.59 rows=1 width=450) (never executed)

  • Index Cond: ((doc ->> '_id'::text) = chp.uuid)
  • Filter: (((doc ->> 'type'::text) = 'person'::text) AND ((doc ->> 'type'::text) = ANY ('{clinic,district_hospital,health_center,person}'::text[])))
29. 0.000 0.000 ↓ 0.0 0

Index Scan using couchdb_form_id on couchdb couchdb_1 (cost=0.56..2.60 rows=1 width=450) (never executed)

  • Index Cond: ((doc ->> '_id'::text) = (couchdb.doc #>> '{parent,_id}'::text[]))
  • Filter: (((doc ->> 'type'::text) = 'health_center'::text) AND ((doc ->> 'type'::text) = ANY ('{clinic,district_hospital,health_center,person}'::text[])))
30. 482.343 350,981.319 ↓ 35,278.0 35,278 1

Nested Loop (cost=2.67..72,113.15 rows=1 width=32) (actual time=24.882..350,981.319 rows=35,278 loops=1)

31. 6,867.457 348,629.242 ↓ 35,278.0 35,278 1

Nested Loop (cost=2.10..72,111.31 rows=1 width=450) (actual time=24.823..348,629.242 rows=35,278 loops=1)

32. 4,202.007 40,909.215 ↓ 55,651.6 2,949,535 1

Nested Loop (cost=1.54..72,058.00 rows=53 width=36) (actual time=1.417..40,909.215 rows=2,949,535 loops=1)

33. 77.188 8,373.489 ↓ 18,927.0 18,927 1

Nested Loop (cost=0.99..72,057.11 rows=1 width=72) (actual time=1.370..8,373.489 rows=18,927 loops=1)

34. 5,476.178 5,476.178 ↓ 18,927.0 18,927 1

Index Scan using contactview_metadata_type on contactview_metadata cmd_2 (cost=0.43..72,054.53 rows=1 width=36) (actual time=1.302..5,476.178 rows=18,927 loops=1)

  • Index Cond: (type = 'clinic'::text)
  • Filter: ((reported >= (date_trunc('day'::text, date_trunc('month'::text, now())))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 732,069
35. 2,820.123 2,820.123 ↑ 1.0 1 18,927

Index Only Scan using contactview_metadata_uuid on contactview_metadata chwarea_1 (cost=0.56..2.57 rows=1 width=36) (actual time=0.147..0.149 rows=1 loops=18,927)

  • Index Cond: (uuid = cmd_2.parent_uuid)
  • Heap Fetches: 10,536
36. 28,333.719 28,333.719 ↓ 14.2 156 18,927

Index Scan using contactview_metadata_parent_uuid on contactview_metadata chw_1 (cost=0.56..0.78 rows=11 width=72) (actual time=0.396..1.497 rows=156 loops=18,927)

  • Index Cond: (parent_uuid = chwarea_1.uuid)
37. 300,852.570 300,852.570 ↓ 0.0 0 2,949,535

Index Scan using couchdb_doc_uuid on couchdb couchdb_2 (cost=0.56..1.00 rows=1 width=450) (actual time=0.102..0.102 rows=0 loops=2,949,535)

  • Index Cond: ((doc ->> '_id'::text) = chw_1.uuid)
  • Filter: (((doc ->> 'type'::text) = 'person'::text) AND ((doc ->> 'type'::text) = ANY ('{clinic,district_hospital,health_center,person}'::text[])))
  • Rows Removed by Filter: 1
38. 1,869.734 1,869.734 ↑ 1.0 1 35,278

Index Scan using couchdb_doc_uuid on couchdb couchdb_3 (cost=0.56..1.83 rows=1 width=450) (actual time=0.051..0.053 rows=1 loops=35,278)

  • Index Cond: ((doc ->> '_id'::text) = (couchdb_2.doc #>> '{parent,_id}'::text[]))
  • Filter: (((doc ->> 'type'::text) = 'health_center'::text) AND ((doc ->> 'type'::text) = ANY ('{clinic,district_hospital,health_center,person}'::text[])))
39. 0.000 0.000 ↓ 0.0 0

Index Scan using useview_postnatal_reported_chw_patient_id on useview_postnatal_care pnc (cost=0.57..8.27 rows=1 width=0) (never executed)

  • Index Cond: ((reported >= (date_trunc('day'::text, date_trunc('month'::text, now())))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone) AND (chw = chp.uuid))
  • Filter: (follow_up_count = '1'::text)
40. 35.321 35.321 ↓ 38.5 3,816 1

Index Scan using useview_postnatal_care_count_reported_uuid on useview_postnatal_care pnc_1 (cost=0.44..93.92 rows=99 width=35) (actual time=3.920..35.321 rows=3,816 loops=1)

  • Index Cond: ((follow_up_count = '1'::text) AND (reported >= (date_trunc('day'::text, date_trunc('month'::text, now())))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
41.          

CTE visited_cte

42. 3.732 15,850.003 ↓ 831.5 1,663 1

Unique (cost=12,041.55..12,041.59 rows=2 width=100) (actual time=15,844.436..15,850.003 rows=1,663 loops=1)

43. 8.507 15,846.271 ↓ 831.5 1,663 1

Sort (cost=12,041.55..12,041.55 rows=2 width=100) (actual time=15,844.434..15,846.271 rows=1,663 loops=1)

  • Sort Key: "*SELECT* 1".supervisor_uuid, "*SELECT* 1".chw_uuid, "*SELECT* 1".chws_visited, "*SELECT* 1".days_in_field, (0), "*SELECT* 1".chp_audit_visits, ((0)::bigint)
  • Sort Method: quicksort Memory: 448kB
44. 1.642 15,837.764 ↓ 831.5 1,663 1

Append (cost=4,547.86..12,041.54 rows=2 width=100) (actual time=14,783.288..15,837.764 rows=1,663 loops=1)

45. 1.709 14,795.015 ↓ 1,540.0 1,540 1

Subquery Scan on *SELECT* 1 (cost=4,547.86..4,547.91 rows=1 width=107) (actual time=14,783.286..14,795.015 rows=1,540 loops=1)

46. 8.911 14,793.306 ↓ 1,540.0 1,540 1

GroupAggregate (cost=4,547.86..4,547.90 rows=1 width=103) (actual time=14,783.282..14,793.306 rows=1,540 loops=1)

  • Group Key: meta_3.chw, hierarchy.chw_uuid, 0
47. 8.069 14,784.395 ↓ 1,670.0 1,670 1

Sort (cost=4,547.86..4,547.87 rows=1 width=155) (actual time=14,783.236..14,784.395 rows=1,670 loops=1)

  • Sort Key: meta_3.chw, hierarchy.chw_uuid
  • Sort Method: quicksort Memory: 492kB
48. 165.778 14,776.326 ↓ 1,670.0 1,670 1

Nested Loop (cost=346.68..4,547.85 rows=1 width=155) (actual time=14,456.148..14,776.326 rows=1,670 loops=1)

49. 226.803 527.976 ↓ 4,557.5 68,362 1

Hash Join (cost=346.12..4,516.59 rows=15 width=108) (actual time=204.304..527.976 rows=68,362 loops=1)

  • Hash Cond: (visit_1.place_id = hierarchy.chw_area_uuid)
50. 96.935 96.935 ↑ 1.0 68,425 1

Seq Scan on useview_chp_visit visit_1 (cost=0.00..3,910.05 rows=69,405 width=72) (actual time=0.026..96.935 rows=68,425 loops=1)

51. 6.414 204.238 ↓ 7,201.0 7,201 1

Hash (cost=346.11..346.11 rows=1 width=72) (actual time=204.238..204.238 rows=7,201 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 801kB
52. 7.321 197.824 ↓ 7,201.0 7,201 1

Subquery Scan on hierarchy (cost=346.07..346.11 rows=1 width=72) (actual time=175.612..197.824 rows=7,201 loops=1)

53. 9.760 190.503 ↓ 7,201.0 7,201 1

Group (cost=346.07..346.10 rows=1 width=201) (actual time=175.610..190.503 rows=7,201 loops=1)

  • Group Key: cmd_3.uuid, cmd_3.name, chp_1.supervisor_uuid, chp_1.area_uuid, cmeta_1.name, chp_1.uuid, chp_1.name, chp_1.phone
54. 52.413 180.743 ↓ 7,201.0 7,201 1

Sort (cost=346.07..346.08 rows=1 width=201) (actual time=175.603..180.743 rows=7,201 loops=1)

  • Sort Key: cmd_3.uuid, cmd_3.name, chp_1.supervisor_uuid, chp_1.area_uuid, cmeta_1.name, chp_1.uuid, chp_1.name, chp_1.phone
  • Sort Method: quicksort Memory: 2,105kB
55. 16.011 128.330 ↓ 7,201.0 7,201 1

Nested Loop (cost=26.88..346.06 rows=1 width=201) (actual time=0.317..128.330 rows=7,201 loops=1)

56. 13.133 18.225 ↓ 7,238.0 7,238 1

Hash Join (cost=26.32..343.64 rows=1 width=185) (actual time=0.255..18.225 rows=7,238 loops=1)

  • Hash Cond: (chp_1.branch_uuid = cmd_3.uuid)
57. 4.866 4.866 ↑ 1.0 7,241 1

Seq Scan on contactview_chp chp_1 (cost=0.00..289.95 rows=7,295 width=166) (actual time=0.007..4.866 rows=7,241 loops=1)

58. 0.035 0.226 ↑ 1.6 25 1

Hash (cost=25.81..25.81 rows=41 width=52) (actual time=0.226..0.226 rows=25 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
59. 0.191 0.191 ↑ 1.6 25 1

Index Scan using contactview_metadata_type on contactview_metadata cmd_3 (cost=0.43..25.81 rows=41 width=52) (actual time=0.045..0.191 rows=25 loops=1)

  • Index Cond: (type = 'district_hospital'::text)
60. 94.094 94.094 ↑ 1.0 1 7,238

Index Scan using contactview_metadata_uuid on contactview_metadata cmeta_1 (cost=0.56..2.41 rows=1 width=52) (actual time=0.012..0.013 rows=1 loops=7,238)

  • Index Cond: (uuid = chp_1.supervisor_uuid)
61. 14,082.572 14,082.572 ↓ 0.0 0 68,362

Index Scan using form_metadata_uuid on form_metadata meta_3 (cost=0.56..2.07 rows=1 width=80) (actual time=0.206..0.206 rows=0 loops=68,362)

  • Index Cond: (uuid = visit_1.xmlforms_uuid)
  • Filter: ((reported >= (date_trunc('day'::text, date_trunc('month'::text, now())))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
  • Rows Removed by Filter: 1
62. 0.171 1,041.107 ↓ 123.0 123 1

Subquery Scan on *SELECT* 2 (cost=7,493.58..7,493.63 rows=1 width=107) (actual time=1,039.993..1,041.107 rows=123 loops=1)

63. 0.875 1,040.936 ↓ 123.0 123 1

GroupAggregate (cost=7,493.58..7,493.61 rows=1 width=103) (actual time=1,039.990..1,040.936 rows=123 loops=1)

  • Group Key: meta_4.chw, hierarchy_1.chw_uuid, 0
64. 0.738 1,040.061 ↓ 125.0 125 1

Sort (cost=7,493.58..7,493.58 rows=1 width=533) (actual time=1,039.928..1,040.061 rows=125 loops=1)

  • Sort Key: meta_4.chw, hierarchy_1.chw_uuid
  • Sort Method: quicksort Memory: 276kB
65. 0.679 1,039.323 ↓ 125.0 125 1

Hash Join (cost=2,522.59..7,493.57 rows=1 width=533) (actual time=921.663..1,039.323 rows=125 loops=1)

  • Hash Cond: ((form.doc #>> '{fields,place_id}'::text[]) = hierarchy_1.chw_area_uuid)
66. 0.524 849.154 ↓ 31.2 125 1

Nested Loop (cost=2,176.47..7,147.42 rows=4 width=493) (actual time=732.140..849.154 rows=125 loops=1)

67. 12.390 823.755 ↓ 31.2 125 1

Hash Join (cost=2,175.91..7,137.12 rows=4 width=117) (actual time=732.073..823.755 rows=125 loops=1)

  • Hash Cond: (fm.uuid = meta_4.uuid)
68. 81.186 81.186 ↓ 2.7 13,173 1

Index Scan using form_metadata_formname on form_metadata fm (cost=0.43..4,943.15 rows=4,921 width=37) (actual time=1.792..81.186 rows=13,173 loops=1)

  • Index Cond: (formname = 'community_events'::text)
69. 263.339 730.179 ↓ 60.3 327,022 1

Hash (cost=2,107.64..2,107.64 rows=5,427 width=80) (actual time=730.179..730.179 rows=327,022 loops=1)

  • Buckets: 524,288 (originally 8192) Batches: 1 (originally 1) Memory Usage: 41,461kB
70. 466.840 466.840 ↓ 60.3 327,022 1

Index Scan using form_metadata_reported on form_metadata meta_4 (cost=0.45..2,107.64 rows=5,427 width=80) (actual time=0.040..466.840 rows=327,022 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, date_trunc('month'::text, now())))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
71. 24.875 24.875 ↑ 1.0 1 125

Index Scan using couchdb_doc_uuid on couchdb form (cost=0.56..2.56 rows=1 width=450) (actual time=0.197..0.199 rows=1 loops=125)

  • Index Cond: ((doc ->> '_id'::text) = fm.uuid)
72. 4.714 189.490 ↓ 7,201.0 7,201 1

Hash (cost=346.11..346.11 rows=1 width=72) (actual time=189.490..189.490 rows=7,201 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 801kB
73. 6.259 184.776 ↓ 7,201.0 7,201 1

Subquery Scan on hierarchy_1 (cost=346.07..346.11 rows=1 width=72) (actual time=166.528..184.776 rows=7,201 loops=1)

74. 8.099 178.517 ↓ 7,201.0 7,201 1

Group (cost=346.07..346.10 rows=1 width=201) (actual time=166.528..178.517 rows=7,201 loops=1)

  • Group Key: cmd_4.uuid, cmd_4.name, chp_2.supervisor_uuid, chp_2.area_uuid, cmeta_2.name, chp_2.uuid, chp_2.name, chp_2.phone
75. 42.503 170.418 ↓ 7,201.0 7,201 1

Sort (cost=346.07..346.08 rows=1 width=201) (actual time=166.525..170.418 rows=7,201 loops=1)

  • Sort Key: cmd_4.uuid, cmd_4.name, chp_2.supervisor_uuid, chp_2.area_uuid, cmeta_2.name, chp_2.uuid, chp_2.name, chp_2.phone
  • Sort Method: quicksort Memory: 2,105kB
76. 22.816 127.915 ↓ 7,201.0 7,201 1

Nested Loop (cost=26.88..346.06 rows=1 width=201) (actual time=0.141..127.915 rows=7,201 loops=1)

77. 13.269 18.243 ↓ 7,238.0 7,238 1

Hash Join (cost=26.32..343.64 rows=1 width=185) (actual time=0.102..18.243 rows=7,238 loops=1)

  • Hash Cond: (chp_2.branch_uuid = cmd_4.uuid)
78. 4.898 4.898 ↑ 1.0 7,241 1

Seq Scan on contactview_chp chp_2 (cost=0.00..289.95 rows=7,295 width=166) (actual time=0.008..4.898 rows=7,241 loops=1)

79. 0.019 0.076 ↑ 1.6 25 1

Hash (cost=25.81..25.81 rows=41 width=52) (actual time=0.076..0.076 rows=25 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
80. 0.057 0.057 ↑ 1.6 25 1

Index Scan using contactview_metadata_type on contactview_metadata cmd_4 (cost=0.43..25.81 rows=41 width=52) (actual time=0.016..0.057 rows=25 loops=1)

  • Index Cond: (type = 'district_hospital'::text)
81. 86.856 86.856 ↑ 1.0 1 7,238

Index Scan using contactview_metadata_uuid on contactview_metadata cmeta_2 (cost=0.56..2.41 rows=1 width=52) (actual time=0.012..0.012 rows=1 loops=7,238)

  • Index Cond: (uuid = chp_2.supervisor_uuid)
82.          

CTE active_visited

83. 9.971 377,372.111 ↑ 1.0 7,241 1

Hash Left Join (cost=165.65..421.00 rows=7,295 width=180) (actual time=377,334.895..377,372.111 rows=7,241 loops=1)

  • Hash Cond: ((a.supervisor_uuid = c.supervisor_id) AND (a.interval_number = c.interval_no))
84. 22.071 377,358.200 ↑ 1.0 7,241 1

Hash Left Join (cost=165.49..366.12 rows=7,295 width=52) (actual time=377,330.937..377,358.200 rows=7,241 loops=1)

  • Hash Cond: ((a.supervisor_uuid = b.supervisor_uuid) AND (a.interval_number = b.interval_number))
85. 950.898 950.898 ↑ 1.0 7,241 1

CTE Scan on active_cte a (cost=0.00..145.90 rows=7,295 width=44) (actual time=945.666..950.898 rows=7,241 loops=1)

86. 0.179 376,385.231 ↓ 96.0 96 1

Hash (cost=165.48..165.48 rows=1 width=44) (actual time=376,385.231..376,385.231 rows=96 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
87. 0.136 376,385.052 ↓ 96.0 96 1

Subquery Scan on b (cost=165.45..165.48 rows=1 width=44) (actual time=376,380.134..376,385.052 rows=96 loops=1)

88. 3.960 376,384.916 ↓ 96.0 96 1

GroupAggregate (cost=165.45..165.47 rows=1 width=44) (actual time=376,380.132..376,384.916 rows=96 loops=1)

  • Group Key: x.supervisor_uuid, x.interval_number
89. 5.351 376,380.956 ↓ 1,435.0 1,435 1

Sort (cost=165.45..165.45 rows=1 width=68) (actual time=376,380.039..376,380.956 rows=1,435 loops=1)

  • Sort Key: x.supervisor_uuid, x.interval_number
  • Sort Method: quicksort Memory: 250kB
90. 3,671.567 376,375.605 ↓ 1,435.0 1,435 1

Nested Loop (cost=0.00..165.44 rows=1 width=68) (actual time=15,844.466..376,375.605 rows=1,435 loops=1)

  • Join Filter: (v.chw_uuid = x.chp_uuid)
  • Rows Removed by Join Filter: 5,576,267
91. 15,854.172 15,854.172 ↓ 831.5 1,663 1

CTE Scan on visited_cte v (cost=0.00..0.04 rows=2 width=32) (actual time=15,844.440..15,854.172 rows=1,663 loops=1)

92. 356,849.866 356,849.866 ↓ 93.2 3,354 1,663

CTE Scan on active_cte x (cost=0.00..164.14 rows=36 width=68) (actual time=0.001..214.582 rows=3,354 loops=1,663)

  • Filter: (active = 1)
  • Rows Removed by Filter: 3,887
93. 0.114 3.940 ↓ 47.0 94 1

Hash (cost=0.13..0.13 rows=2 width=164) (actual time=3.940..3.940 rows=94 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
94. 0.102 3.826 ↓ 47.0 94 1

Subquery Scan on c (cost=0.07..0.13 rows=2 width=164) (actual time=3.585..3.826 rows=94 loops=1)

95. 2.442 3.724 ↓ 47.0 94 1

HashAggregate (cost=0.07..0.11 rows=2 width=164) (actual time=3.583..3.724 rows=94 loops=1)

  • Group Key: visited_cte.supervisor_uuid, visited_cte.interval_number
96. 1.282 1.282 ↓ 831.5 1,663 1

CTE Scan on visited_cte (cost=0.00..0.04 rows=2 width=68) (actual time=0.001..1.282 rows=1,663 loops=1)

97. 11.621 377,397.869 ↑ 1.0 7,241 1

Sort (cost=613.97..632.21 rows=7,295 width=180) (actual time=377,393.480..377,397.869 rows=7,241 loops=1)

  • Sort Key: active_visited.supervisor_uuid, active_visited.interval_number
  • Sort Method: quicksort Memory: 1,196kB
98. 377,386.248 377,386.248 ↑ 1.0 7,241 1

CTE Scan on active_visited (cost=0.00..145.90 rows=7,295 width=180) (actual time=377,334.901..377,386.248 rows=7,241 loops=1)

99. 0.300 1,244.520 ↓ 6.5 202 1

Materialize (cost=6,909.99..6,910.71 rows=31 width=276) (actual time=1,243.477..1,244.520 rows=202 loops=1)

100. 0.543 1,244.220 ↓ 6.5 202 1

Merge Left Join (cost=6,909.99..6,910.63 rows=31 width=276) (actual time=1,243.471..1,244.220 rows=202 loops=1)

  • Merge Cond: ((cmeta.uuid = stocks.supervisor_uuid) AND ((0) = stocks.interval_number))
101. 1.010 168.057 ↓ 6.5 202 1

Sort (cost=555.86..555.94 rows=31 width=116) (actual time=167.906..168.057 rows=202 loops=1)

  • Sort Key: cmeta.uuid, (0)
  • Sort Method: quicksort Memory: 56kB
102. 0.550 167.047 ↓ 6.5 202 1

Group (cost=554.16..554.78 rows=31 width=116) (actual time=166.252..167.047 rows=202 loops=1)

  • Group Key: cmd.uuid, cmd.name, cmeta.uuid, cmeta.name
103. 1.494 166.497 ↓ 6.5 202 1

Sort (cost=554.16..554.24 rows=31 width=104) (actual time=166.239..166.497 rows=202 loops=1)

  • Sort Key: cmd.uuid, cmd.name, cmeta.uuid, cmeta.name
  • Sort Method: quicksort Memory: 53kB
104. 0.426 165.003 ↓ 6.5 202 1

Nested Loop (cost=0.99..553.39 rows=31 width=104) (actual time=0.807..165.003 rows=202 loops=1)

105. 0.127 0.127 ↑ 1.8 23 1

Index Scan using contactview_metadata_type on contactview_metadata cmd (cost=0.43..26.22 rows=41 width=52) (actual time=0.035..0.127 rows=23 loops=1)

  • Index Cond: (type = 'district_hospital'::text)
  • Filter: ((name <> 'HQ'::text) AND (name <> 'HQ OVC'::text) AND (name <> 'HQ'::text) AND (name <> 'HQ OVC'::text))
  • Rows Removed by Filter: 2
106. 164.450 164.450 ↓ 1.1 9 23

Index Scan using contactview_metadata_parent_uuid on contactview_metadata cmeta (cost=0.56..12.78 rows=8 width=88) (actual time=0.808..7.150 rows=9 loops=23)

  • Index Cond: (parent_uuid = cmd.uuid)
  • Filter: ((name !~~* '%Branch Manager%'::text) AND (type = 'person'::text))
  • Rows Removed by Filter: 311
107. 0.237 1,075.620 ↓ 1.8 94 1

Sort (cost=6,354.13..6,354.26 rows=53 width=199) (actual time=1,075.557..1,075.620 rows=94 loops=1)

  • Sort Key: stocks.supervisor_uuid, stocks.interval_number
  • Sort Method: quicksort Memory: 38kB
108. 0.125 1,075.383 ↓ 1.8 94 1

Subquery Scan on stocks (cost=6,347.84..6,352.61 rows=53 width=199) (actual time=1,070.571..1,075.383 rows=94 loops=1)

109. 3.756 1,075.258 ↓ 1.8 94 1

GroupAggregate (cost=6,347.84..6,352.08 rows=53 width=199) (actual time=1,070.569..1,075.258 rows=94 loops=1)

  • Group Key: meta.chw, 0
110. 4.882 1,071.502 ↓ 31.5 1,670 1

Sort (cost=6,347.84..6,347.97 rows=53 width=49) (actual time=1,070.474..1,071.502 rows=1,670 loops=1)

  • Sort Key: meta.chw
  • Sort Method: quicksort Memory: 283kB
111. 107.610 1,066.620 ↓ 31.5 1,670 1

Hash Join (cost=2,175.48..6,346.32 rows=53 width=49) (actual time=1,058.555..1,066.620 rows=1,670 loops=1)

  • Hash Cond: (visit.xmlforms_uuid = meta.uuid)
112. 57.231 57.231 ↑ 1.0 68,425 1

Seq Scan on useview_chp_visit visit (cost=0.00..3,910.05 rows=69,405 width=47) (actual time=0.162..57.231 rows=68,425 loops=1)

113. 328.836 901.779 ↓ 60.3 327,022 1

Hash (cost=2,107.64..2,107.64 rows=5,427 width=72) (actual time=901.779..901.779 rows=327,022 loops=1)

  • Buckets: 524,288 (originally 8192) Batches: 1 (originally 1) Memory Usage: 37,763kB
114. 572.943 572.943 ↓ 60.3 327,022 1

Index Scan using form_metadata_reported on form_metadata meta (cost=0.45..2,107.64 rows=5,427 width=72) (actual time=0.039..572.943 rows=327,022 loops=1)

  • Index Cond: ((reported >= (date_trunc('day'::text, date_trunc('month'::text, now())))::timestamp without time zone) AND (reported < ((date_trunc('day'::text, now()) + '1 day'::interval))::timestamp without time zone))
Planning time : 55.759 ms
Execution time : 378,664.093 ms