explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ofzR

Settings
# exclusive inclusive rows x rows loops node
1. 0.042 82,958.402 ↓ 82.0 82 1

GroupAggregate (cost=89,169.52..89,169.56 rows=1 width=652) (actual time=82,958.367..82,958.402 rows=82 loops=1)

  • Group Key: ((filtered_places.schedule_id)::character varying), ((filtered_places.route_order)::integer), filtered_places.place_id, ((CASE WHEN (employee_names.employee_name IS NULL) THEN 'N/A'::text ELSE employee_names.employee_name END)::character varying), filtered_places.place_room, ((filtered_places.component_id)::character varying)
2.          

CTE account_time_zones

3. 0.370 71.831 ↑ 5.6 214 1

Hash Join (cost=23.48..12,538.29 rows=1,190 width=96) (actual time=71.304..71.831 rows=214 loops=1)

  • Hash Cond: ((catalog_items.metadata -> 'time_zone'::text) = pg_timezone_names.name)
4. 0.032 2.880 ↑ 1.1 214 1

Nested Loop (cost=0.97..12,431.90 rows=238 width=217) (actual time=2.684..2.880 rows=214 loops=1)

5. 2.656 2.656 ↑ 1.0 1 1

Index Scan using index_catalogs_on_company_id_and_name on catalogs (cost=0.41..4,482.95 rows=1 width=4) (actual time=2.645..2.656 rows=1 loops=1)

  • Index Cond: ((name)::text = 'account_time_zones'::text)
  • Filter: (disabled_at IS NULL)
6. 0.192 0.192 ↑ 6.0 214 1

Index Scan using catalog_items_catalog_id_name_idx on catalog_items (cost=0.56..7,936.15 rows=1,279 width=221) (actual time=0.032..0.192 rows=214 loops=1)

  • Index Cond: (catalog_id = catalogs.id)
  • Filter: (disabled_at IS NULL)
7. 0.268 68.581 ↓ 1.2 1,216 1

Hash (cost=10.00..10.00 rows=1,000 width=32) (actual time=68.580..68.581 rows=1,216 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 76kB
8. 68.313 68.313 ↓ 1.2 1,216 1

Function Scan on pg_timezone_names (cost=0.00..10.00 rows=1,000 width=32) (actual time=68.170..68.313 rows=1,216 loops=1)

9.          

CTE company_time_zone

10. 0.008 0.024 ↑ 1.0 1 1

Bitmap Heap Scan on companies (cost=2.29..4.31 rows=1 width=36) (actual time=0.024..0.024 rows=1 loops=1)

  • Recheck Cond: (id = 3,437)
  • Heap Blocks: exact=1
11. 0.016 0.016 ↑ 1.0 1 1

Bitmap Index Scan on companies_pkey (cost=0.00..2.29 rows=1 width=0) (actual time=0.016..0.016 rows=1 loops=1)

  • Index Cond: (id = 3,437)
12.          

CTE places_agg

13. 63.194 75.093 ↓ 1.2 31,311 1

Bitmap Heap Scan on places (cost=1,161.49..64,448.34 rows=25,255 width=369) (actual time=13.381..75.093 rows=31,311 loops=1)

  • Recheck Cond: (company_id = 3,437)
  • Filter: (disabled_at IS NULL)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=8,623
14. 11.899 11.899 ↓ 1.9 63,042 1

Bitmap Index Scan on places_company_id_metadata_gin_idx (cost=0.00..1,155.17 rows=32,423 width=0) (actual time=11.899..11.899 rows=63,042 loops=1)

  • Index Cond: (company_id = 3,437)
15.          

CTE place_info

16. 39.984 253.654 ↓ 248.5 31,311 1

Hash Right Join (cost=602.99..681.22 rows=126 width=624) (actual time=213.805..253.654 rows=31,311 loops=1)

  • Hash Cond: ((account_time_zones.component_id = places_agg.component_id) AND (account_time_zones.component_name = places_agg.component_name))
17. 71.937 71.937 ↑ 5.6 214 1

CTE Scan on account_time_zones (cost=0.00..23.80 rows=1,190 width=96) (actual time=71.307..71.937 rows=214 loops=1)

18. 27.633 141.733 ↓ 248.5 31,311 1

Hash (cost=601.10..601.10 rows=126 width=1,164) (actual time=141.733..141.733 rows=31,311 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 15,025kB
19. 9.788 114.100 ↓ 248.5 31,311 1

Hash Join (cost=0.03..601.10 rows=126 width=1,164) (actual time=13.432..114.100 rows=31,311 loops=1)

  • Hash Cond: (places_agg.company_id = company_time_zone.company_id)
20. 104.282 104.282 ↓ 1.2 31,311 1

CTE Scan on places_agg (cost=0.00..505.10 rows=25,255 width=1,136) (actual time=13.385..104.282 rows=31,311 loops=1)

21. 0.003 0.030 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=36) (actual time=0.029..0.030 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
22. 0.027 0.027 ↑ 1.0 1 1

CTE Scan on company_time_zone (cost=0.00..0.02 rows=1 width=36) (actual time=0.026..0.027 rows=1 loops=1)

23.          

CTE filtered_places

24. 39.208 341.220 ↓ 248.5 31,311 1

HashAggregate (cost=5.67..7.88 rows=126 width=656) (actual time=326.241..341.220 rows=31,311 loops=1)

  • Group Key: place_info.place_id, place_info.place_room, (place_info.metadata -> 'component_id'::text), upper((place_info.metadata -> 'schedule_id'::text)), place_info.route_order, place_info.place_time_zone, place_info.converted_cutoff_time
25. 302.012 302.012 ↓ 248.5 31,311 1

CTE Scan on place_info (cost=0.00..3.46 rows=126 width=656) (actual time=213.809..302.012 rows=31,311 loops=1)

26.          

CTE qualifying_mission_responses

27. 2.838 82,419.326 ↓ 0.0 0 1

Nested Loop (cost=28.70..4,964.33 rows=1 width=16) (actual time=82,419.325..82,419.326 rows=0 loops=1)

28. 30.110 30.446 ↓ 225.2 1,126 1

Bitmap Heap Scan on campaigns (cost=28.13..2,044.28 rows=5 width=4) (actual time=0.533..30.446 rows=1,126 loops=1)

  • Recheck Cond: (company_id = 3,437)
  • Filter: (lower((metadata -> 'category'::text)) = 'virtual_guide'::text)
  • Rows Removed by Filter: 6
  • Heap Blocks: exact=1,240
29. 0.336 0.336 ↓ 2.0 2,044 1

Bitmap Index Scan on campaign_cf_template_id_idx (cost=0.00..28.13 rows=1,027 width=0) (actual time=0.336..0.336 rows=2,044 loops=1)

  • Index Cond: (company_id = 3,437)
30. 5,910.374 82,386.042 ↓ 0.0 0 1,126

Nested Loop (cost=0.56..582.75 rows=126 width=20) (actual time=73.167..73.167 rows=0 loops=1,126)

31. 5,963.296 5,963.296 ↓ 248.5 31,311 1,126

CTE Scan on filtered_places filtered_places_1 (cost=0.00..2.52 rows=126 width=44) (actual time=0.001..5.296 rows=31,311 loops=1,126)

32. 70,512.372 70,512.372 ↓ 0.0 0 35,256,186

Index Scan using index_mission_responses_on_place_id_and_campaign_id_and_state on mission_responses (cost=0.56..4.60 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=35,256,186)

  • Index Cond: ((place_id = filtered_places_1.place_id) AND (campaign_id = campaigns.id) AND ((state)::text = 'completed'::text))
  • Filter: (timezone(filtered_places_1.place_time_zone, timezone('UTC'::text, completed_at)) >= filtered_places_1.converted_cutoff_time)
  • Rows Removed by Filter: 0
33.          

CTE employee_names_temp1

34. 0.009 133.607 ↑ 100.0 1 1

ProjectSet (cost=29.84..4,989.40 rows=100 width=48) (actual time=95.149..133.607 rows=1 loops=1)

35. 0.004 133.598 ↑ 1.0 1 1

Nested Loop (cost=29.84..4,988.89 rows=1 width=116) (actual time=95.141..133.598 rows=1 loops=1)

36. 0.005 133.561 ↑ 1.0 1 1

Nested Loop (cost=29.27..4,988.30 rows=1 width=120) (actual time=95.105..133.561 rows=1 loops=1)

37. 0.011 133.529 ↑ 1.0 1 1

Nested Loop (cost=28.70..4,964.33 rows=1 width=20) (actual time=95.075..133.529 rows=1 loops=1)

38. 5.890 6.290 ↑ 2.5 2 1

Bitmap Heap Scan on campaigns campaigns_1 (cost=28.13..2,044.28 rows=5 width=4) (actual time=5.270..6.290 rows=2 loops=1)

  • Recheck Cond: (company_id = 3,437)
  • Filter: (lower((metadata -> 'category'::text)) = 'employee_name'::text)
  • Rows Removed by Filter: 1,130
  • Heap Blocks: exact=1,240
39. 0.400 0.400 ↓ 2.0 2,044 1

Bitmap Index Scan on campaign_cf_template_id_idx (cost=0.00..28.13 rows=1,027 width=0) (actual time=0.400..0.400 rows=2,044 loops=1)

  • Index Cond: (company_id = 3,437)
40. 0.000 127.228 ↓ 0.0 0 2

Nested Loop (cost=0.56..582.75 rows=126 width=24) (actual time=44.842..63.614 rows=0 loops=2)

41. 7.820 7.820 ↓ 248.5 31,311 2

CTE Scan on filtered_places filtered_places_2 (cost=0.00..2.52 rows=126 width=44) (actual time=0.001..3.910 rows=31,311 loops=2)

42. 125.244 125.244 ↓ 0.0 0 62,622

Index Scan using index_mission_responses_on_place_id_and_campaign_id_and_state on mission_responses mission_responses_1 (cost=0.56..4.60 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=62,622)

  • Index Cond: ((place_id = filtered_places_2.place_id) AND (campaign_id = campaigns_1.id) AND ((state)::text = 'completed'::text))
  • Filter: (timezone(filtered_places_2.place_time_zone, timezone('UTC'::text, completed_at)) >= filtered_places_2.converted_cutoff_time)
  • Rows Removed by Filter: 0
43. 0.027 0.027 ↑ 827.0 1 1

Index Scan using index_task_responses_on_mission_response_id on task_responses (cost=0.57..15.70 rows=827 width=108) (actual time=0.026..0.027 rows=1 loops=1)

  • Index Cond: (mission_response_id = mission_responses_1.id)
44. 0.033 0.033 ↑ 1.0 1 1

Index Scan using tasks_pkey on tasks (cost=0.56..0.59 rows=1 width=4) (actual time=0.033..0.033 rows=1 loops=1)

  • Index Cond: (id = task_responses.task_id)
  • Filter: (lower((metadata -> 'task_category'::text)) = 'employee_name'::text)
45.          

CTE employee_names_temp2

46. 0.013 133.625 ↑ 100.0 1 1

HashAggregate (cost=2.50..3.50 rows=100 width=12) (actual time=133.624..133.625 rows=1 loops=1)

  • Group Key: employee_names_temp1.user_id
47. 133.612 133.612 ↑ 100.0 1 1

CTE Scan on employee_names_temp1 (cost=0.00..2.00 rows=100 width=12) (actual time=95.153..133.612 rows=1 loops=1)

48.          

CTE company_memberships_agg

49. 0.162 0.162 ↓ 1.6 42 1

Index Scan using index_memberships_on_company_id_role on company_memberships (cost=0.42..54.36 rows=26 width=132) (actual time=0.027..0.162 rows=42 loops=1)

  • Index Cond: (company_id = 3,437)
50.          

CTE employee_names

51. 0.023 133.693 ↑ 1.0 1 1

Hash Join (cost=3.87..6.63 rows=1 width=104) (actual time=133.691..133.693 rows=1 loops=1)

  • Hash Cond: ((employee_names_temp2.user_id = employee_names_temp1_1.user_id) AND (employee_names_temp2.max_completed_at = employee_names_temp1_1.completed_at))
52. 133.629 133.629 ↑ 100.0 1 1

CTE Scan on employee_names_temp2 (cost=0.00..2.00 rows=100 width=12) (actual time=133.627..133.629 rows=1 loops=1)

53. 0.010 0.041 ↑ 26.0 1 1

Hash (cost=3.48..3.48 rows=26 width=116) (actual time=0.041..0.041 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
54. 0.011 0.031 ↑ 26.0 1 1

Hash Join (cost=0.84..3.48 rows=26 width=116) (actual time=0.030..0.031 rows=1 loops=1)

  • Hash Cond: (employee_names_temp1_1.user_id = company_memberships_agg.user_id)
55. 0.001 0.001 ↑ 100.0 1 1

CTE Scan on employee_names_temp1 employee_names_temp1_1 (cost=0.00..2.00 rows=100 width=48) (actual time=0.001..0.001 rows=1 loops=1)

56. 0.009 0.019 ↓ 1.6 42 1

Hash (cost=0.52..0.52 rows=26 width=68) (actual time=0.019..0.019 rows=42 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
57. 0.010 0.010 ↓ 1.6 42 1

CTE Scan on company_memberships_agg (cost=0.00..0.52 rows=26 width=68) (actual time=0.001..0.010 rows=42 loops=1)

58.          

CTE supervisor_list

59. 0.003 0.084 ↓ 0.0 0 1

Nested Loop (cost=0.42..5.10 rows=1 width=113) (actual time=0.084..0.084 rows=0 loops=1)

60. 0.009 0.009 ↑ 1.0 1 1

Index Scan using users_pkey on users (cost=0.42..4.44 rows=1 width=17) (actual time=0.006..0.009 rows=1 loops=1)

  • Index Cond: (id = 308,060)
61. 0.072 0.072 ↓ 0.0 0 1

CTE Scan on company_memberships_agg company_memberships_agg_1 (cost=0.00..0.65 rows=1 width=100) (actual time=0.072..0.072 rows=0 loops=1)

  • Filter: ((user_type ~~* 'SUPERVISOR'::text) AND (user_id = 308,060))
  • Rows Removed by Filter: 42
62.          

CTE employee_list

63. 13.821 18.442 ↓ 16.8 2,114 1

Hash Join (cost=5.04..669.24 rows=126 width=85) (actual time=0.466..18.442 rows=2,114 loops=1)

  • Hash Cond: (upper(places_agg_1.schedule_id) = upper(company_memberships_agg_2.schedule_id))
64. 4.182 4.182 ↓ 1.2 31,311 1

CTE Scan on places_agg places_agg_1 (cost=0.00..505.10 rows=25,255 width=36) (actual time=0.002..4.182 rows=31,311 loops=1)

65. 0.032 0.439 ↓ 40.0 40 1

Hash (cost=5.02..5.02 rows=1 width=113) (actual time=0.439..0.439 rows=40 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
66. 0.011 0.407 ↓ 40.0 40 1

Nested Loop (cost=0.42..5.02 rows=1 width=113) (actual time=0.067..0.407 rows=40 loops=1)

67. 0.236 0.236 ↓ 40.0 40 1

CTE Scan on company_memberships_agg company_memberships_agg_2 (cost=0.00..0.58 rows=1 width=100) (actual time=0.045..0.236 rows=40 loops=1)

  • Filter: (user_type ~~* 'FLA SCHEDULE'::text)
  • Rows Removed by Filter: 2
68. 0.160 0.160 ↑ 1.0 1 40

Index Scan using users_pkey on users users_1 (cost=0.42..4.44 rows=1 width=17) (actual time=0.004..0.004 rows=1 loops=40)

  • Index Cond: (id = company_memberships_agg_2.user_id)
69.          

CTE supervisor_agg

70. 0.007 0.561 ↓ 0.0 0 1

Hash Join (cost=0.03..3.51 rows=1 width=8) (actual time=0.561..0.561 rows=0 loops=1)

  • Hash Cond: (lower(employee_list.supervisor) = lower(concat(supervisor_list.first_name, ' ', supervisor_list.last_name)))
71. 0.468 0.468 ↑ 126.0 1 1

CTE Scan on employee_list (cost=0.00..2.52 rows=126 width=36) (actual time=0.468..0.468 rows=1 loops=1)

72. 0.001 0.086 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=1,036) (actual time=0.086..0.086 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
73. 0.085 0.085 ↓ 0.0 0 1

CTE Scan on supervisor_list (cost=0.00..0.02 rows=1 width=1,036) (actual time=0.085..0.085 rows=0 loops=1)

74.          

CTE manager_list

75. 0.005 0.064 ↑ 1.0 1 1

Nested Loop (cost=0.42..5.02 rows=1 width=113) (actual time=0.037..0.064 rows=1 loops=1)

76. 0.032 0.032 ↑ 1.0 1 1

CTE Scan on company_memberships_agg company_memberships_agg_3 (cost=0.00..0.58 rows=1 width=100) (actual time=0.005..0.032 rows=1 loops=1)

  • Filter: (user_type ~~* 'MANAGER'::text)
  • Rows Removed by Filter: 41
77. 0.027 0.027 ↑ 1.0 1 1

Index Scan using users_pkey on users users_2 (cost=0.42..4.44 rows=1 width=17) (actual time=0.027..0.027 rows=1 loops=1)

  • Index Cond: (id = company_memberships_agg_3.user_id)
78.          

CTE component_list

79. 13.778 17.905 ↑ 1.6 2,114 1

Hash Join (cost=0.84..696.62 rows=3,283 width=68) (actual time=0.063..17.905 rows=2,114 loops=1)

  • Hash Cond: (upper(places_agg_2.schedule_id) = upper(company_memberships_agg_4.schedule_id))
80. 4.085 4.085 ↓ 1.2 31,311 1

CTE Scan on places_agg places_agg_2 (cost=0.00..505.10 rows=25,255 width=36) (actual time=0.001..4.085 rows=31,311 loops=1)

81. 0.031 0.042 ↓ 1.6 41 1

Hash (cost=0.52..0.52 rows=26 width=64) (actual time=0.042..0.042 rows=41 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
82. 0.011 0.011 ↓ 1.6 42 1

CTE Scan on company_memberships_agg company_memberships_agg_4 (cost=0.00..0.52 rows=26 width=64) (actual time=0.003..0.011 rows=42 loops=1)

83.          

CTE manager_agg

84. 0.548 19.240 ↓ 132.1 2,114 1

Hash Join (cost=0.03..78.16 rows=16 width=40) (actual time=0.141..19.240 rows=2,114 loops=1)

  • Hash Cond: (component_list.component_id = manager_list.component_id)
85. 18.624 18.624 ↑ 1.6 2,114 1

CTE Scan on component_list (cost=0.00..65.66 rows=3,283 width=36) (actual time=0.065..18.624 rows=2,114 loops=1)

86. 0.002 0.068 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=36) (actual time=0.068..0.068 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
87. 0.066 0.066 ↑ 1.0 1 1

CTE Scan on manager_list (cost=0.00..0.02 rows=1 width=36) (actual time=0.039..0.066 rows=1 loops=1)

88.          

CTE place_list

89. 2.309 42.536 ↓ 29.6 4,228 1

HashAggregate (cost=5.72..7.15 rows=143 width=8) (actual time=41.526..42.536 rows=4,228 loops=1)

  • Group Key: supervisor_agg.supervisor_user, supervisor_agg.place_id
90. 0.347 40.227 ↓ 29.6 4,228 1

Append (cost=0.00..5.00 rows=143 width=8) (actual time=0.567..40.227 rows=4,228 loops=1)

91. 0.563 0.563 ↓ 0.0 0 1

CTE Scan on supervisor_agg (cost=0.00..0.02 rows=1 width=8) (actual time=0.563..0.563 rows=0 loops=1)

92. 19.284 19.284 ↓ 16.8 2,114 1

CTE Scan on employee_list employee_list_1 (cost=0.00..2.52 rows=126 width=8) (actual time=0.002..19.284 rows=2,114 loops=1)

93. 20.033 20.033 ↓ 132.1 2,114 1

CTE Scan on manager_agg (cost=0.00..0.32 rows=16 width=8) (actual time=0.143..20.033 rows=2,114 loops=1)

94. 0.158 82,958.360 ↓ 82.0 82 1

Sort (cost=6.46..6.46 rows=1 width=624) (actual time=82,958.355..82,958.360 rows=82 loops=1)

  • Sort Key: ((filtered_places.schedule_id)::character varying), ((filtered_places.route_order)::integer), filtered_places.place_id, ((CASE WHEN (employee_names.employee_name IS NULL) THEN 'N/A'::text ELSE employee_names.employee_name END)::character varying), filtered_places.place_room, ((filtered_places.component_id)::character varying)
  • Sort Method: quicksort Memory: 35kB
95. 0.037 82,958.202 ↓ 82.0 82 1

Nested Loop Left Join (cost=0.00..6.45 rows=1 width=624) (actual time=82,922.094..82,958.202 rows=82 loops=1)

  • Join Filter: (employee_names.schedule_id = filtered_places.schedule_id)
  • Rows Removed by Join Filter: 82
96. 0.607 82,824.423 ↓ 82.0 82 1

Nested Loop (cost=0.00..6.41 rows=1 width=620) (actual time=82,788.390..82,824.423 rows=82 loops=1)

  • Join Filter: (filtered_places.place_id = place_list.place_id)
  • Rows Removed by Join Filter: 6,642
97. 0.020 82,757.232 ↓ 82.0 82 1

Nested Loop Left Join (cost=0.00..3.18 rows=1 width=620) (actual time=82,745.678..82,757.232 rows=82 loops=1)

  • Join Filter: (qualifying_mission_responses.place_id = filtered_places.place_id)
98. 337.864 337.864 ↓ 82.0 82 1

CTE Scan on filtered_places (cost=0.00..3.15 rows=1 width=616) (actual time=326.345..337.864 rows=82 loops=1)

  • Filter: (lower(schedule_id) = 'uop.01'::text)
  • Rows Removed by Filter: 31,229
99. 82,419.348 82,419.348 ↓ 0.0 0 82

CTE Scan on qualifying_mission_responses (cost=0.00..0.02 rows=1 width=8) (actual time=1,005.114..1,005.114 rows=0 loops=82)

100. 66.584 66.584 ↓ 82.0 82 82

CTE Scan on place_list (cost=0.00..3.22 rows=1 width=4) (actual time=0.508..0.812 rows=82 loops=82)

  • Filter: (user_id = 308,060)
  • Rows Removed by Filter: 4,146
101. 133.742 133.742 ↑ 1.0 1 82

CTE Scan on employee_names (cost=0.00..0.02 rows=1 width=64) (actual time=1.631..1.631 rows=1 loops=82)

Planning time : 5.512 ms
Execution time : 82,965.587 ms