explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iUwn

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=81,965.89..81,965.93 rows=1 width=652) (actual rows= loops=)

  • 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.000 0.000 ↓ 0.0

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

  • Hash Cond: ((catalog_items.metadata -> 'time_zone'::text) = pg_timezone_names.name)
4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.97..12,431.90 rows=238 width=217) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((name)::text = 'account_time_zones'::text)
  • Filter: (disabled_at IS NULL)
6. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (catalog_id = catalogs.id)
  • Filter: (disabled_at IS NULL)
7. 0.000 0.000 ↓ 0.0

Hash (cost=10.00..10.00 rows=1,000 width=32) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

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

9.          

CTE company_time_zone

10. 0.000 0.000 ↓ 0.0

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

  • Recheck Cond: (id = 3,437)
11. 0.000 0.000 ↓ 0.0

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

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

CTE places_agg

13. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on places (cost=740.59..57,813.70 rows=22,757 width=369) (actual rows= loops=)

  • Recheck Cond: (company_id = 3,437)
  • Filter: (disabled_at IS NULL)
14. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on places_company_id_metadata_gin_idx (cost=0.00..734.90 rows=29,186 width=0) (actual rows= loops=)

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

CTE place_info

16. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=543.36..614.70 rows=114 width=624) (actual rows= loops=)

  • Hash Cond: ((account_time_zones.component_id = places_agg.component_id) AND (account_time_zones.component_name = places_agg.component_name))
17. 0.000 0.000 ↓ 0.0

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

18. 0.000 0.000 ↓ 0.0

Hash (cost=541.65..541.65 rows=114 width=1,164) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.03..541.65 rows=114 width=1,164) (actual rows= loops=)

  • Hash Cond: (places_agg.company_id = company_time_zone.company_id)
20. 0.000 0.000 ↓ 0.0

CTE Scan on places_agg (cost=0.00..455.14 rows=22,757 width=1,136) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

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

22. 0.000 0.000 ↓ 0.0

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

23.          

CTE filtered_places

24. 0.000 0.000 ↓ 0.0

HashAggregate (cost=5.13..7.13 rows=114 width=656) (actual rows= loops=)

  • 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. 0.000 0.000 ↓ 0.0

CTE Scan on place_info (cost=0.00..3.14 rows=114 width=656) (actual rows= loops=)

26.          

CTE qualifying_mission_responses

27. 0.000 0.000 ↓ 0.0

Nested Loop (cost=29.08..4,785.38 rows=1 width=16) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on campaigns (cost=28.51..2,143.43 rows=5 width=4) (actual rows= loops=)

  • Recheck Cond: (company_id = 3,437)
  • Filter: (lower((metadata -> 'category'::text)) = 'virtual_guide'::text)
29. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on campaign_cf_template_id_idx (cost=0.00..28.51 rows=1,078 width=0) (actual rows= loops=)

  • Index Cond: (company_id = 3,437)
30. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..527.25 rows=114 width=20) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

CTE Scan on filtered_places filtered_places_1 (cost=0.00..2.28 rows=114 width=44) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

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 rows= loops=)

  • 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)
33.          

CTE employee_names_temp1

34. 0.000 0.000 ↓ 0.0

ProjectSet (cost=30.22..4,810.45 rows=100 width=48) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Nested Loop (cost=30.22..4,809.94 rows=1 width=116) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Nested Loop (cost=29.65..4,809.35 rows=1 width=120) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Nested Loop (cost=29.08..4,785.38 rows=1 width=20) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on campaigns campaigns_1 (cost=28.51..2,143.43 rows=5 width=4) (actual rows= loops=)

  • Recheck Cond: (company_id = 3,437)
  • Filter: (lower((metadata -> 'category'::text)) = 'employee_name'::text)
39. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on campaign_cf_template_id_idx (cost=0.00..28.51 rows=1,078 width=0) (actual rows= loops=)

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

Nested Loop (cost=0.56..527.25 rows=114 width=24) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

CTE Scan on filtered_places filtered_places_2 (cost=0.00..2.28 rows=114 width=44) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

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 rows= loops=)

  • 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)
43. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (mission_response_id = mission_responses_1.id)
44. 0.000 0.000 ↓ 0.0

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

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

CTE employee_names_temp2

46. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2.50..3.50 rows=100 width=12) (actual rows= loops=)

  • Group Key: employee_names_temp1.user_id
47. 0.000 0.000 ↓ 0.0

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

48.          

CTE company_memberships_agg

49. 0.000 0.000 ↓ 0.0

Index Scan using index_memberships_on_company_id_role on company_memberships (cost=0.42..54.36 rows=26 width=132) (actual rows= loops=)

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

CTE employee_names

51. 0.000 0.000 ↓ 0.0

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

  • 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. 0.000 0.000 ↓ 0.0

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

53. 0.000 0.000 ↓ 0.0

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

54. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.84..3.48 rows=26 width=116) (actual rows= loops=)

  • Hash Cond: (employee_names_temp1_1.user_id = company_memberships_agg.user_id)
55. 0.000 0.000 ↓ 0.0

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

56. 0.000 0.000 ↓ 0.0

Hash (cost=0.52..0.52 rows=26 width=68) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

CTE Scan on company_memberships_agg (cost=0.00..0.52 rows=26 width=68) (actual rows= loops=)

58.          

CTE supervisor_list

59. 0.000 0.000 ↓ 0.0

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

60. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = 308,060)
61. 0.000 0.000 ↓ 0.0

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

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

CTE employee_list

63. 0.000 0.000 ↓ 0.0

Hash Join (cost=5.04..603.55 rows=114 width=85) (actual rows= loops=)

  • Hash Cond: (upper(places_agg_1.schedule_id) = upper(company_memberships_agg_2.schedule_id))
64. 0.000 0.000 ↓ 0.0

CTE Scan on places_agg places_agg_1 (cost=0.00..455.14 rows=22,757 width=36) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

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

66. 0.000 0.000 ↓ 0.0

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

67. 0.000 0.000 ↓ 0.0

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

  • Filter: (user_type ~~* 'FLA SCHEDULE'::text)
68. 0.000 0.000 ↓ 0.0

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

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

CTE supervisor_agg

70. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.03..3.18 rows=1 width=8) (actual rows= loops=)

  • Hash Cond: (lower(employee_list.supervisor) = lower(concat(supervisor_list.first_name, ' ', supervisor_list.last_name)))
71. 0.000 0.000 ↓ 0.0

CTE Scan on employee_list (cost=0.00..2.28 rows=114 width=36) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

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

73. 0.000 0.000 ↓ 0.0

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

74.          

CTE manager_list

75. 0.000 0.000 ↓ 0.0

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

76. 0.000 0.000 ↓ 0.0

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

  • Filter: (user_type ~~* 'MANAGER'::text)
77. 0.000 0.000 ↓ 0.0

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

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

CTE component_list

79. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.84..627.80 rows=2,958 width=68) (actual rows= loops=)

  • Hash Cond: (upper(places_agg_2.schedule_id) = upper(company_memberships_agg_4.schedule_id))
80. 0.000 0.000 ↓ 0.0

CTE Scan on places_agg places_agg_2 (cost=0.00..455.14 rows=22,757 width=36) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Hash (cost=0.52..0.52 rows=26 width=64) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

CTE Scan on company_memberships_agg company_memberships_agg_4 (cost=0.00..0.52 rows=26 width=64) (actual rows= loops=)

83.          

CTE manager_agg

84. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.03..70.44 rows=15 width=40) (actual rows= loops=)

  • Hash Cond: (component_list.component_id = manager_list.component_id)
85. 0.000 0.000 ↓ 0.0

CTE Scan on component_list (cost=0.00..59.16 rows=2,958 width=36) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

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

87. 0.000 0.000 ↓ 0.0

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

88.          

CTE place_list

89. 0.000 0.000 ↓ 0.0

HashAggregate (cost=5.20..6.50 rows=130 width=8) (actual rows= loops=)

  • Group Key: supervisor_agg.supervisor_user, supervisor_agg.place_id
90. 0.000 0.000 ↓ 0.0

Append (cost=0.00..4.55 rows=130 width=8) (actual rows= loops=)

91. 0.000 0.000 ↓ 0.0

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

92. 0.000 0.000 ↓ 0.0

CTE Scan on employee_list employee_list_1 (cost=0.00..2.28 rows=114 width=8) (actual rows= loops=)

93. 0.000 0.000 ↓ 0.0

CTE Scan on manager_agg (cost=0.00..0.30 rows=15 width=8) (actual rows= loops=)

94. 0.000 0.000 ↓ 0.0

Sort (cost=5.87..5.87 rows=1 width=624) (actual rows= loops=)

  • 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)
95. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.00..5.86 rows=1 width=624) (actual rows= loops=)

  • Join Filter: (employee_names.schedule_id = filtered_places.schedule_id)
96. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..5.82 rows=1 width=620) (actual rows= loops=)

  • Join Filter: (filtered_places.place_id = place_list.place_id)
97. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.00..2.88 rows=1 width=620) (actual rows= loops=)

  • Join Filter: (qualifying_mission_responses.place_id = filtered_places.place_id)
98. 0.000 0.000 ↓ 0.0

CTE Scan on filtered_places (cost=0.00..2.85 rows=1 width=616) (actual rows= loops=)

  • Filter: (lower(schedule_id) = 'uop.01'::text)
99. 0.000 0.000 ↓ 0.0

CTE Scan on qualifying_mission_responses (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

100. 0.000 0.000 ↓ 0.0

CTE Scan on place_list (cost=0.00..2.92 rows=1 width=4) (actual rows= loops=)

  • Filter: (user_id = 308,060)
101. 0.000 0.000 ↓ 0.0

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