explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WgCs

Settings
# exclusive inclusive rows x rows loops node
1. 0.207 19,711.134 ↓ 42.0 84 1

GroupAggregate (cost=106,180.00..106,180.08 rows=2 width=652) (actual time=19,711.045..19,711.134 rows=84 loops=1)

  • Group Key: ((filtered_places.schedule_id)::character varying), ((filtered_places.route_order)::integer), filtered_places.place_id, ((COALESCE(employee_names_temp1.employee_name, 'N/A'::text))::character varying), filtered_places.place_room, ((filtered_places.component_id)::character varying)
  • Functions: 202
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 22.696 ms, Inlining 0.000 ms, Optimization 9.606 ms, Emission 168.077 ms, Total 200.379 ms
2.          

CTE places_agg

3. 416.931 431.999 ↑ 1.0 36,659 1

Bitmap Heap Scan on places (cost=630.61..92,881.68 rows=36,992 width=370) (actual time=203.761..431.999 rows=36,659 loops=1)

  • Recheck Cond: (company_id = 3,437)
  • Filter: (disabled_at IS NULL)
  • Rows Removed by Filter: 10,947
  • Heap Blocks: exact=34,936
4. 15.068 15.068 ↑ 1.0 47,606 1

Bitmap Index Scan on places_company_id_metadata_gin_idx (cost=0.00..621.36 rows=47,648 width=0) (actual time=15.068..15.068 rows=47,606 loops=1)

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

CTE filtered_places

6. 0.081 668.459 ↓ 84.0 84 1

Unique (cost=9,810.55..9,810.57 rows=1 width=656) (actual time=668.354..668.459 rows=84 loops=1)

7. 0.225 668.378 ↓ 84.0 84 1

Sort (cost=9,810.55..9,810.55 rows=1 width=656) (actual time=668.352..668.378 rows=84 loops=1)

  • Sort Key: places_agg_1.id, places_agg_1.name, ((places_agg_1.metadata -> 'component_id'::text)), (upper((places_agg_1.metadata -> 'schedule_id'::text))), (CASE WHEN (char_length(regexp_replace((places_agg_1.custom_place_id)::text, '\d'::text, ''::text, 'g'::text)) < char_length((places_agg_1.custom_place_id)::text)) THEN regexp_replace((places_agg_1.custom_place_id)::text, '[^0-9]'::text, ''::text, 'g'::text) ELSE NULL::text END), (CASE WHEN ((catalog_items.metadata -> 'time_zone'::text) IS NULL) THEN CASE WHEN ((companies.time_zone)::text = 'Eastern Time (US & Canada)'::text) THEN 'US/Eastern'::text WHEN ((companies.time_zone)::text = 'Central Time (US & Canada)'::text) THEN 'US/Central'::text WHEN ((companies.time_zone)::text = 'Mountain Time (US & Canada)'::text) THEN 'US/Mountain'::text WHEN ((companies.time_zone)::text = 'Pacific Time (US & Canada)'::text) THEN 'US/Pacific'::text ELSE NULL::text END ELSE (catalog_items.metadata -> 'time_zone'::text) END), (CASE WHEN ((catalog_items.metadata -> 'time_zone'::text) IS NULL) THEN CASE WHEN (date_part('hour'::text, timezone(CASE WHEN ((companies.time_zone)::text = 'Eastern Time (US & Canada)'::text) THEN 'US/Eastern'::text WHEN ((companies.time_zone)::text = 'Central Time (US & Canada)'::text) THEN 'US/Central'::text WHEN ((companies.time_zone)::text = 'Mountain Time (US & Canada)'::text) THEN 'US/Mountain'::text WHEN ((companies.time_zone)::text = 'Pacific Time (US & Canada)'::text) THEN 'US/Pacific'::text ELSE NULL::text END, now())) < '4'::double precision) THEN (((timezone(CASE WHEN ((companies.time_zone)::text = 'Eastern Time (US & Canada)'::text) THEN 'US/Eastern'::text WHEN ((companies.time_zone)::text = 'Central Time (US & Canada)'::text) THEN 'US/Central'::text WHEN ((companies.time_zone)::text = 'Mountain Time (US & Canada)'::text) THEN 'US/Mountain'::text WHEN ((companies.time_zone)::text = 'Pacific Time (US & Canada)'::text) THEN 'US/Pacific'::text ELSE NULL::text END, now()))::date - '1 day'::interval) + '04:00:00'::interval) ELSE ((timezone(CASE WHEN ((companies.time_zone)::text = 'Eastern Time (US & Canada)'::text) THEN 'US/Eastern'::text WHEN ((companies.time_zone)::text = 'Central Time (US & Canada)'::text) THEN 'US/Central'::text WHEN ((companies.time_zone)::text = 'Mountain Time (US & Canada)'::text) THEN 'US/Mountain'::text WHEN ((companies.time_zone)::text = 'Pacific Time (US & Canada)'::text) THEN 'US/Pacific'::text ELSE NULL::text END, now()))::date + '04:00:00'::interval) END ELSE CASE WHEN (date_part('hour'::text, timezone((catalog_items.metadata -> 'time_zone'::text), now())) < '4'::double precision) THEN (((timezone((catalog_items.metadata -> 'time_zone'::text), now()))::date - '1 day'::interval) + '04:00:00'::interval) ELSE ((timezone((catalog_items.metadata -> 'time_zone'::text), now()))::date + '04:00:00'::interval) END END)
  • Sort Method: quicksort Memory: 36kB
8. 2.157 668.153 ↓ 84.0 84 1

Nested Loop (cost=23.76..9,810.54 rows=1 width=656) (actual time=305.850..668.153 rows=84 loops=1)

9. 6.870 665.492 ↓ 84.0 84 1

Nested Loop Left Join (cost=23.48..9,806.09 rows=1 width=1,252) (actual time=305.421..665.492 rows=84 loops=1)

  • Join Filter: ((lower((catalog_items.name)::text) = places_agg_1.component_id) AND (lower((catalog_items.metadata -> 'component_name'::text)) = places_agg_1.component_name))
  • Rows Removed by Join Filter: 17,976
10. 483.482 483.482 ↓ 84.0 84 1

CTE Scan on places_agg places_agg_1 (cost=0.00..1,109.76 rows=1 width=1,136) (actual time=243.155..483.482 rows=84 loops=1)

  • Filter: ((company_id = 3,437) AND (lower((metadata -> 'schedule_id'::text)) = 'uop.12'::text))
  • Rows Removed by Filter: 36,575
11. 7.354 175.140 ↑ 5.8 214 84

Hash Join (cost=23.48..8,668.54 rows=1,235 width=212) (actual time=1.895..2.085 rows=214 loops=84)

  • Hash Cond: ((catalog_items.metadata -> 'time_zone'::text) = pg_timezone_names.name)
12. 1.932 110.712 ↑ 1.2 214 84

Nested Loop (cost=0.97..8,571.32 rows=247 width=212) (actual time=1.212..1.318 rows=214 loops=84)

13. 102.648 102.648 ↑ 1.0 1 84

Index Scan using index_catalogs_on_company_id_and_name on catalogs (cost=0.41..1,111.95 rows=1 width=4) (actual time=1.196..1.222 rows=1 loops=84)

  • Index Cond: ((name)::text = 'account_time_zones'::text)
  • Filter: (disabled_at IS NULL)
14. 6.132 6.132 ↑ 6.6 214 84

Index Scan using catalog_items_catalog_id_name_idx on catalog_items (cost=0.56..7,445.22 rows=1,415 width=216) (actual time=0.012..0.073 rows=214 loops=84)

  • Index Cond: (catalog_id = catalogs.id)
  • Filter: (disabled_at IS NULL)
15. 0.239 57.074 ↓ 1.2 1,218 1

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

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 76kB
16. 56.835 56.835 ↓ 1.2 1,218 1

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

17. 0.504 0.504 ↑ 1.0 1 84

Index Scan using companies_pkey on companies (cost=0.28..4.30 rows=1 width=29) (actual time=0.005..0.006 rows=1 loops=84)

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

CTE campaigns_agg

19. 1.118 1.118 ↓ 94.2 1,131 1

Index Scan using index_campaigns_on_company_id on campaigns (cost=0.43..566.81 rows=12 width=36) (actual time=0.179..1.118 rows=1,131 loops=1)

  • Index Cond: (company_id = 3,437)
  • Filter: ((metadata -> 'category'::text) = ANY ('{virtual_guide,employee_name}'::text[]))
  • Rows Removed by Filter: 4
20.          

CTE employee_names_temp1

21. 0.001 18.617 ↓ 0.0 0 1

ProjectSet (cost=1.71..32.75 rows=10 width=48) (actual time=18.617..18.617 rows=0 loops=1)

22. 0.001 18.616 ↓ 0.0 0 1

Nested Loop (cost=1.71..32.69 rows=1 width=119) (actual time=18.616..18.616 rows=0 loops=1)

23. 0.002 18.615 ↓ 0.0 0 1

Nested Loop (cost=1.14..32.10 rows=1 width=123) (actual time=18.615..18.615 rows=0 loops=1)

24. 0.106 18.613 ↓ 0.0 0 1

Nested Loop (cost=0.57..4.91 rows=1 width=20) (actual time=18.613..18.613 rows=0 loops=1)

25. 0.027 0.027 ↓ 84.0 84 1

CTE Scan on filtered_places filtered_places_2 (cost=0.00..0.02 rows=1 width=44) (actual time=0.001..0.027 rows=84 loops=1)

26. 0.336 18.480 ↓ 0.0 0 84

Nested Loop (cost=0.57..4.88 rows=1 width=20) (actual time=0.220..0.220 rows=0 loops=84)

27. 16.884 16.884 ↓ 5.0 5 84

CTE Scan on campaigns_agg campaigns_agg_1 (cost=0.00..0.27 rows=1 width=4) (actual time=0.000..0.201 rows=5 loops=84)

  • Filter: (category = 'employee_name'::text)
  • Rows Removed by Filter: 1,126
28. 1.260 1.260 ↓ 0.0 0 420

Index Scan using index_mission_responses_on_place_id_and_campaign_id_and_state on mission_responses mission_responses_1 (cost=0.57..4.60 rows=1 width=24) (actual time=0.003..0.003 rows=0 loops=420)

  • Index Cond: ((place_id = filtered_places_2.place_id) AND (campaign_id = campaigns_agg_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)
29. 0.000 0.000 ↓ 0.0 0

Index Scan using index_task_responses_on_mission_response_id on task_responses (cost=0.57..17.75 rows=944 width=111) (never executed)

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

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

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

CTE company_memberships_agg

32. 4.812 4.999 ↓ 1.4 603 1

Bitmap Heap Scan on company_memberships (cost=31.75..841.45 rows=430 width=132) (actual time=0.373..4.999 rows=603 loops=1)

  • Recheck Cond: ((company_id = 3,437) AND (disabled_at IS NULL))
  • Heap Blocks: exact=426
33. 0.187 0.187 ↓ 1.4 603 1

Bitmap Index Scan on index_memberships_on_company_id_role (cost=0.00..31.65 rows=430 width=0) (actual time=0.187..0.187 rows=603 loops=1)

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

CTE employee_list

35. 71.878 93.755 ↓ 198.2 36,659 1

Hash Join (cost=14.13..987.02 rows=185 width=85) (actual time=11.887..93.755 rows=36,659 loops=1)

  • Hash Cond: (upper(places_agg_2.schedule_id) = upper(company_memberships_agg_4.schedule_id))
36. 10.054 10.054 ↑ 1.0 36,659 1

CTE Scan on places_agg places_agg_2 (cost=0.00..739.84 rows=36,992 width=36) (actual time=0.003..10.054 rows=36,659 loops=1)

37. 0.843 11.823 ↓ 591.0 591 1

Hash (cost=14.12..14.12 rows=1 width=113) (actual time=11.822..11.823 rows=591 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 77kB
38. 0.478 10.980 ↓ 591.0 591 1

Nested Loop (cost=0.42..14.12 rows=1 width=113) (actual time=0.625..10.980 rows=591 loops=1)

39. 6.365 6.365 ↓ 591.0 591 1

CTE Scan on company_memberships_agg company_memberships_agg_4 (cost=0.00..9.67 rows=1 width=100) (actual time=0.476..6.365 rows=591 loops=1)

  • Filter: (user_type ~~* 'FLA SCHEDULE'::text)
  • Rows Removed by Filter: 12
40. 4.137 4.137 ↑ 1.0 1 591

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

  • Index Cond: (id = company_memberships_agg_4.user_id)
41. 0.261 19,710.927 ↓ 42.0 84 1

Sort (cost=1,059.72..1,059.73 rows=2 width=624) (actual time=19,710.920..19,710.927 rows=84 loops=1)

  • Sort Key: ((filtered_places.schedule_id)::character varying), ((filtered_places.route_order)::integer), filtered_places.place_id, ((COALESCE(employee_names_temp1.employee_name, 'N/A'::text))::character varying), filtered_places.place_room, ((filtered_places.component_id)::character varying)
  • Sort Method: quicksort Memory: 32kB
42. 0.366 19,710.666 ↓ 42.0 84 1

Hash Left Join (cost=1,050.82..1,059.71 rows=2 width=624) (actual time=19,710.503..19,710.666 rows=84 loops=1)

  • Hash Cond: (lower(filtered_places.schedule_id) = lower(company_memberships_agg.schedule_id))
43. 0.091 19,691.657 ↓ 42.0 84 1

Hash Join (cost=1,039.94..1,048.79 rows=2 width=620) (actual time=19,691.566..19,691.657 rows=84 loops=1)

  • Hash Cond: (employee_list.place_id = filtered_places.place_id)
44. 0.097 161.596 ↑ 4.4 84 1

HashAggregate (cost=1,034.98..1,038.70 rows=372 width=8) (actual time=161.560..161.596 rows=84 loops=1)

  • Group Key: users.id, employee_list.place_id
45. 0.018 161.499 ↑ 4.4 84 1

Append (cost=4.45..1,033.12 rows=372 width=8) (actual time=156.112..161.499 rows=84 loops=1)

46. 0.002 155.772 ↓ 0.0 0 1

Nested Loop (cost=4.45..19.48 rows=1 width=8) (actual time=155.771..155.772 rows=0 loops=1)

47. 31.034 155.770 ↓ 0.0 0 1

Hash Join (cost=4.45..8.72 rows=1 width=8) (actual time=155.770..155.770 rows=0 loops=1)

  • Hash Cond: (lower(employee_list.supervisor) = lower(concat(users.first_name, ' ', users.last_name)))
48. 124.700 124.700 ↓ 198.2 36,659 1

CTE Scan on employee_list (cost=0.00..3.70 rows=185 width=36) (actual time=11.890..124.700 rows=36,659 loops=1)

49. 0.020 0.036 ↑ 1.0 1 1

Hash (cost=4.44..4.44 rows=1 width=17) (actual time=0.036..0.036 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
50. 0.016 0.016 ↑ 1.0 1 1

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

  • Index Cond: (id = 308,071)
51. 0.000 0.000 ↓ 0.0 0

CTE Scan on company_memberships_agg company_memberships_agg_1 (cost=0.00..10.75 rows=1 width=4) (never executed)

  • Filter: ((user_type ~~* 'SUPERVISOR'::text) AND (user_id = 308,071))
52. 4.757 4.757 ↓ 84.0 84 1

CTE Scan on employee_list employee_list_1 (cost=0.00..4.16 rows=1 width=8) (actual time=0.335..4.757 rows=84 loops=1)

  • Filter: (user_id = 308,071)
  • Rows Removed by Filter: 36,575
53. 0.005 0.952 ↓ 0.0 0 1

Nested Loop (cost=21.44..1,003.90 rows=370 width=8) (actual time=0.952..0.952 rows=0 loops=1)

54. 0.122 0.122 ↑ 1.0 1 1

Index Only Scan using users_pkey on users users_1 (cost=0.42..4.44 rows=1 width=4) (actual time=0.120..0.122 rows=1 loops=1)

  • Index Cond: (id = 308,071)
  • Heap Fetches: 0
55. 0.019 0.825 ↓ 0.0 0 1

Hash Join (cost=21.02..995.76 rows=370 width=8) (actual time=0.824..0.825 rows=0 loops=1)

  • Hash Cond: (upper(places_agg.schedule_id) = upper(company_memberships_agg_3.schedule_id))
56. 0.002 0.002 ↑ 36,992.0 1 1

CTE Scan on places_agg (cost=0.00..739.84 rows=36,992 width=36) (actual time=0.002..0.002 rows=1 loops=1)

57. 0.000 0.804 ↓ 0.0 0 1

Hash (cost=20.99..20.99 rows=2 width=36) (actual time=0.804..0.804 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
58. 0.010 0.804 ↓ 0.0 0 1

Hash Join (cost=10.76..20.99 rows=2 width=36) (actual time=0.803..0.804 rows=0 loops=1)

  • Hash Cond: (company_memberships_agg_3.component_id = company_memberships_agg_2.component_id)
59. 0.002 0.002 ↑ 430.0 1 1

CTE Scan on company_memberships_agg company_memberships_agg_3 (cost=0.00..8.60 rows=430 width=64) (actual time=0.002..0.002 rows=1 loops=1)

60. 0.001 0.792 ↓ 0.0 0 1

Hash (cost=10.75..10.75 rows=1 width=36) (actual time=0.792..0.792 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
61. 0.791 0.791 ↓ 0.0 0 1

CTE Scan on company_memberships_agg company_memberships_agg_2 (cost=0.00..10.75 rows=1 width=36) (actual time=0.791..0.791 rows=0 loops=1)

  • Filter: ((user_type ~~* 'MANAGER'::text) AND (user_id = 308,071))
  • Rows Removed by Filter: 603
62. 0.565 19,529.970 ↓ 84.0 84 1

Hash (cost=4.94..4.94 rows=1 width=620) (actual time=19,529.970..19,529.970 rows=84 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
63. 0.444 19,529.405 ↓ 84.0 84 1

Nested Loop Left Join (cost=0.57..4.94 rows=1 width=620) (actual time=850.490..19,529.405 rows=84 loops=1)

  • Join Filter: (mission_responses.place_id = filtered_places.place_id)
64. 668.441 668.441 ↓ 84.0 84 1

CTE Scan on filtered_places (cost=0.00..0.02 rows=1 width=616) (actual time=668.360..668.441 rows=84 loops=1)

65. 8.904 18,860.520 ↓ 0.0 0 84

Nested Loop (cost=0.57..4.91 rows=1 width=8) (actual time=224.530..224.530 rows=0 loops=84)

66. 5.040 5.040 ↓ 84.0 84 84

CTE Scan on filtered_places filtered_places_1 (cost=0.00..0.02 rows=1 width=44) (actual time=0.000..0.060 rows=84 loops=84)

67. 1,213.632 18,846.576 ↓ 0.0 0 7,056

Nested Loop (cost=0.57..4.88 rows=1 width=16) (actual time=2.671..2.671 rows=0 loops=7,056)

68. 1,742.832 1,742.832 ↓ 1,126.0 1,126 7,056

CTE Scan on campaigns_agg (cost=0.00..0.27 rows=1 width=4) (actual time=0.001..0.247 rows=1,126 loops=7,056)

  • Filter: (category = 'virtual_guide'::text)
  • Rows Removed by Filter: 5
69. 15,890.112 15,890.112 ↓ 0.0 0 7,945,056

Index Scan using index_mission_responses_on_place_id_and_campaign_id_and_state on mission_responses (cost=0.57..4.60 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=7,945,056)

  • Index Cond: ((place_id = filtered_places_1.place_id) AND (campaign_id = campaigns_agg.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
70. 0.001 18.643 ↓ 0.0 0 1

Hash (cost=10.86..10.86 rows=2 width=64) (actual time=18.643..18.643 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
71. 0.019 18.642 ↓ 0.0 0 1

Hash Join (cost=0.92..10.86 rows=2 width=64) (actual time=18.642..18.642 rows=0 loops=1)

  • Hash Cond: ((employee_names_temp1_1.user_id = employee_names_temp1.user_id) AND ((max(employee_names_temp1_1.completed_at)) = employee_names_temp1.completed_at))
72. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=0.57..10.33 rows=22 width=48) (never executed)

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

CTE Scan on company_memberships_agg (cost=0.00..8.60 rows=430 width=36) (never executed)

74. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.45..0.45 rows=10 width=12) (never executed)

75. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=0.25..0.35 rows=10 width=12) (never executed)

  • Group Key: employee_names_temp1_1.user_id
76. 0.000 0.000 ↓ 0.0 0

CTE Scan on employee_names_temp1 employee_names_temp1_1 (cost=0.00..0.20 rows=10 width=12) (never executed)

77. 0.002 18.623 ↓ 0.0 0 1

Hash (cost=0.20..0.20 rows=10 width=44) (actual time=18.623..18.623 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
78. 18.621 18.621 ↓ 0.0 0 1

CTE Scan on employee_names_temp1 (cost=0.00..0.20 rows=10 width=44) (actual time=18.621..18.621 rows=0 loops=1)

Execution time : 19,761.362 ms