explain.depesz.com

PostgreSQL's explain analyze made readable

Result: expG

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 78,029.903 ↓ 2.7 8 1

GroupAggregate (cost=1,815,290.72..1,815,290.83 rows=3 width=652) (actual time=78,029.899..78,029.903 rows=8 loops=1)

  • Group Key: ((filtered_places.schedule_id)::character varying), ((filtered_places.route_order)::integer), filtered_places.place_id, ((COALESCE(employee_names.employee_name, 'N/A'::text))::character varying), filtered_places.place_room, ((filtered_places.component_id)::character varying)
2.          

CTE account_time_zones

3. 0.515 100.657 ↑ 5.6 214 1

Hash Join (cost=23.48..12,555.26 rows=1,195 width=96) (actual time=99.856..100.657 rows=214 loops=1)

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

Nested Loop (cost=0.97..12,448.51 rows=239 width=217) (actual time=3.492..3.827 rows=214 loops=1)

5. 3.460 3.460 ↑ 1.0 1 1

Index Scan using index_catalogs_on_company_id_and_name on catalogs (cost=0.41..4,482.98 rows=1 width=4) (actual time=3.444..3.460 rows=1 loops=1)

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

Index Scan using catalog_items_catalog_id_name_idx on catalog_items (cost=0.56..7,952.62 rows=1,291 width=221) (actual time=0.037..0.255 rows=214 loops=1)

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

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 76kB
8. 95.989 95.989 ↓ 1.2 1,216 1

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

9.          

CTE company_time_zone

10. 0.013 0.033 ↑ 1.0 1 1

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

  • Recheck Cond: (id = 3437)
  • Heap Blocks: exact=1
11. 0.020 0.020 ↑ 1.0 1 1

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

  • Index Cond: (id = 3437)
12.          

CTE places_agg

13. 77.502 91.110 ↓ 1.2 31,311 1

Bitmap Heap Scan on places (cost=1,206.77..67,799.47 rows=26,587 width=369) (actual time=15.341..91.110 rows=31,311 loops=1)

  • Recheck Cond: (company_id = 3437)
  • Filter: (disabled_at IS NULL)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=8623
14. 13.608 13.608 ↓ 1.8 63,042 1

Bitmap Index Scan on places_company_id_metadata_gin_idx (cost=0.00..1,200.12 rows=34,149 width=0) (actual time=13.608..13.608 rows=63,042 loops=1)

  • Index Cond: (company_id = 3437)
15.          

CTE place_info

16. 61.424 331.023 ↓ 235.4 31,311 1

Hash Right Join (cost=634.80..713.86 rows=133 width=624) (actual time=269.741..331.023 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. 100.782 100.782 ↑ 5.6 214 1

CTE Scan on account_time_zones (cost=0.00..23.90 rows=1,195 width=96) (actual time=99.861..100.782 rows=214 loops=1)

18. 30.030 168.817 ↓ 235.4 31,311 1

Hash (cost=632.80..632.80 rows=133 width=1,164) (actual time=168.817..168.817 rows=31,311 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 15025kB
19. 14.798 138.787 ↓ 235.4 31,311 1

Hash Join (cost=0.03..632.80 rows=133 width=1,164) (actual time=15.413..138.787 rows=31,311 loops=1)

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

CTE Scan on places_agg (cost=0.00..531.74 rows=26,587 width=1,136) (actual time=15.347..123.948 rows=31,311 loops=1)

21. 0.004 0.041 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.037 0.037 ↑ 1.0 1 1

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

23.          

CTE filtered_places

24. 55.989 456.685 ↓ 235.4 31,311 1

HashAggregate (cost=5.99..8.31 rows=133 width=656) (actual time=436.339..456.685 rows=31,311 loops=1)

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

CTE Scan on place_info (cost=0.00..3.66 rows=133 width=656) (actual time=269.745..400.696 rows=31,311 loops=1)

26.          

CTE campaigns_agg

27. 16.904 17.282 ↓ 1.1 1,132 1

Bitmap Heap Scan on campaigns (cost=28.74..2,126.68 rows=1,072 width=36) (actual time=0.590..17.282 rows=1,132 loops=1)

  • Recheck Cond: (company_id = 3437)
  • Heap Blocks: exact=1240
28. 0.378 0.378 ↓ 1.9 2,044 1

Bitmap Index Scan on campaign_cf_template_id_idx (cost=0.00..28.47 rows=1,072 width=0) (actual time=0.377..0.378 rows=2,044 loops=1)

  • Index Cond: (company_id = 3437)
29.          

CTE qualifying_mission_responses

30. 3.207 77,126.715 ↓ 0.0 0 1

Nested Loop (cost=0.57..3,108.06 rows=1 width=16) (actual time=77,126.715..77,126.715 rows=0 loops=1)

31. 21.784 21.784 ↓ 225.2 1,126 1

CTE Scan on campaigns_agg (cost=0.00..24.12 rows=5 width=4) (actual time=0.594..21.784 rows=1,126 loops=1)

  • Filter: (category = 'virtual_guide'::text)
  • Rows Removed by Filter: 6
32. 1,454.792 77,101.724 ↓ 0.0 0 1,126

Nested Loop (cost=0.57..615.46 rows=133 width=20) (actual time=68.474..68.474 rows=0 loops=1,126)

33. 5,134.560 5,134.560 ↓ 235.4 31,311 1,126

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

34. 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.57..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_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
35.          

CTE tasks_agg

36. 12,391.298 26,547.240 ↑ 174,970.0 1 1

Gather (cost=1,000.00..1,720,183.88 rows=174,970 width=4) (actual time=256.081..26,547.240 rows=1 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
37. 14,155.942 14,155.942 ↑ 24,996.0 1 8 / 8

Parallel Seq Scan on tasks (cost=0.00..1,701,686.88 rows=24,996 width=4) (actual time=11,082.782..14,155.942 rows=1 loops=8)

  • Filter: (lower((metadata -> 'task_category'::text)) = 'employee_name'::text)
  • Rows Removed by Filter: 4376362
38.          

CTE employee_names_temp1

39. 0.001 397.794 ↓ 0.0 0 1

ProjectSet (cost=3,131.84..7,288.93 rows=300 width=48) (actual time=397.794..397.794 rows=0 loops=1)

40. 0.020 397.793 ↓ 0.0 0 1

Hash Join (cost=3,131.84..7,287.41 rows=3 width=116) (actual time=397.792..397.793 rows=0 loops=1)

  • Hash Cond: (tasks_agg.id = task_responses.task_id)
41. 256.085 256.085 ↑ 174,970.0 1 1

CTE Scan on tasks_agg (cost=0.00..3,499.40 rows=174,970 width=4) (actual time=256.085..256.085 rows=1 loops=1)

42. 0.002 141.688 ↓ 0.0 0 1

Hash (cost=3,131.76..3,131.76 rows=6 width=120) (actual time=141.688..141.688 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
43. 0.002 141.686 ↓ 0.0 0 1

Nested Loop (cost=1.14..3,131.76 rows=6 width=120) (actual time=141.686..141.686 rows=0 loops=1)

44. 0.007 141.684 ↓ 0.0 0 1

Nested Loop (cost=0.57..3,108.06 rows=1 width=20) (actual time=141.684..141.684 rows=0 loops=1)

45. 0.119 0.119 ↑ 2.5 2 1

CTE Scan on campaigns_agg campaigns_agg_1 (cost=0.00..24.12 rows=5 width=4) (actual time=0.087..0.119 rows=2 loops=1)

  • Filter: (category = 'employee_name'::text)
  • Rows Removed by Filter: 1130
46. 8.670 141.558 ↓ 0.0 0 2

Nested Loop (cost=0.57..615.46 rows=133 width=24) (actual time=70.778..70.779 rows=0 loops=2)

47. 7.644 7.644 ↓ 235.4 31,311 2

CTE Scan on filtered_places filtered_places_2 (cost=0.00..2.66 rows=133 width=44) (actual time=0.001..3.822 rows=31,311 loops=2)

48. 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.57..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_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)
  • Rows Removed by Filter: 0
49. 0.000 0.000 ↓ 0.0 0

Index Scan using index_task_responses_on_mission_response_id on task_responses (cost=0.57..15.50 rows=821 width=108) (never executed)

  • Index Cond: (mission_response_id = mission_responses_1.id)
50.          

CTE employee_names_temp2

51. 0.004 397.799 ↓ 0.0 0 1

HashAggregate (cost=7.50..9.50 rows=200 width=12) (actual time=397.799..397.799 rows=0 loops=1)

  • Group Key: employee_names_temp1.user_id
52. 397.795 397.795 ↓ 0.0 0 1

CTE Scan on employee_names_temp1 (cost=0.00..6.00 rows=300 width=12) (actual time=397.795..397.795 rows=0 loops=1)

53.          

CTE company_memberships_agg

54. 0.239 0.239 ↓ 41.0 41 1

Index Scan using index_memberships_on_company_id_role on company_memberships (cost=0.42..54.48 rows=1 width=132) (actual time=0.063..0.239 rows=41 loops=1)

  • Index Cond: (company_id = 3437)
  • Filter: (upper((metadata -> 'user_type'::text)) = ANY ('{SUPERVISOR,"FLA SCHEDULE",MANAGER}'::text[]))
  • Rows Removed by Filter: 1
55.          

CTE employee_names

56. 0.001 397.803 ↓ 0.0 0 1

Hash Join (cost=7.21..12.72 rows=1 width=104) (actual time=397.803..397.803 rows=0 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))
57. 397.802 397.802 ↓ 0.0 0 1

CTE Scan on employee_names_temp2 (cost=0.00..4.00 rows=200 width=12) (actual time=397.801..397.802 rows=0 loops=1)

58. 0.000 0.000 ↓ 0.0 0

Hash (cost=7.18..7.18 rows=2 width=116) (never executed)

59. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=0.03..7.18 rows=2 width=116) (never executed)

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

CTE Scan on employee_names_temp1 employee_names_temp1_1 (cost=0.00..6.00 rows=300 width=48) (never executed)

61. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.02..0.02 rows=1 width=68) (never executed)

62. 0.000 0.000 ↓ 0.0 0

CTE Scan on company_memberships_agg (cost=0.00..0.02 rows=1 width=68) (never executed)

63.          

CTE supervisor_list

64. 0.003 0.057 ↓ 0.0 0 1

Nested Loop (cost=0.42..4.48 rows=1 width=145) (actual time=0.057..0.057 rows=0 loops=1)

65. 0.008 0.008 ↑ 1.0 1 1

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

  • Index Cond: (id = 308088)
66. 0.046 0.046 ↓ 0.0 0 1

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

  • Filter: ((user_type ~~* 'SUPERVISOR'::text) AND (user_id = 308088))
  • Rows Removed by Filter: 41
67.          

CTE employee_list

68. 21.582 28.080 ↓ 15.9 2,114 1

Hash Join (cost=4.47..703.71 rows=133 width=85) (actual time=0.598..28.080 rows=2,114 loops=1)

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

CTE Scan on places_agg places_agg_1 (cost=0.00..531.74 rows=26,587 width=36) (actual time=0.001..5.918 rows=31,311 loops=1)

70. 0.046 0.580 ↓ 40.0 40 1

Hash (cost=4.46..4.46 rows=1 width=113) (actual time=0.580..0.580 rows=40 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
71. 0.051 0.534 ↓ 40.0 40 1

Nested Loop (cost=0.42..4.46 rows=1 width=113) (actual time=0.120..0.534 rows=40 loops=1)

72. 0.323 0.323 ↓ 40.0 40 1

CTE Scan on company_memberships_agg company_memberships_agg_2 (cost=0.00..0.02 rows=1 width=100) (actual time=0.091..0.323 rows=40 loops=1)

  • Filter: (user_type ~~* 'FLA SCHEDULE'::text)
  • Rows Removed by Filter: 1
73. 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)
74.          

CTE supervisor_agg

75. 0.008 0.673 ↓ 0.0 0 1

Hash Join (cost=0.03..3.53 rows=1 width=8) (actual time=0.672..0.673 rows=0 loops=1)

  • Hash Cond: (lower(employee_list.supervisor) = lower(supervisor_list.user_full_name))
76. 0.606 0.606 ↑ 133.0 1 1

CTE Scan on employee_list (cost=0.00..2.66 rows=133 width=36) (actual time=0.606..0.606 rows=1 loops=1)

77. 0.000 0.059 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
78. 0.059 0.059 ↓ 0.0 0 1

CTE Scan on supervisor_list (cost=0.00..0.02 rows=1 width=36) (actual time=0.058..0.059 rows=0 loops=1)

79.          

CTE manager_list

80. 0.006 0.096 ↑ 1.0 1 1

Nested Loop (cost=0.42..4.46 rows=1 width=113) (actual time=0.054..0.096 rows=1 loops=1)

81. 0.050 0.050 ↑ 1.0 1 1

CTE Scan on company_memberships_agg company_memberships_agg_3 (cost=0.00..0.02 rows=1 width=100) (actual time=0.009..0.050 rows=1 loops=1)

  • Filter: (user_type ~~* 'MANAGER'::text)
  • Rows Removed by Filter: 40
82. 0.040 0.040 ↑ 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.040..0.040 rows=1 loops=1)

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

CTE component_list

84. 22.488 28.375 ↓ 15.9 2,114 1

Hash Join (cost=0.03..699.27 rows=133 width=68) (actual time=0.090..28.375 rows=2,114 loops=1)

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

CTE Scan on places_agg places_agg_2 (cost=0.00..531.74 rows=26,587 width=36) (actual time=0.002..5.829 rows=31,311 loops=1)

86. 0.037 0.058 ↓ 41.0 41 1

Hash (cost=0.02..0.02 rows=1 width=64) (actual time=0.058..0.058 rows=41 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
87. 0.021 0.021 ↓ 41.0 41 1

CTE Scan on company_memberships_agg company_memberships_agg_4 (cost=0.00..0.02 rows=1 width=64) (actual time=0.004..0.021 rows=41 loops=1)

88.          

CTE manager_agg

89. 0.833 30.391 ↓ 2,114.0 2,114 1

Hash Join (cost=0.03..3.20 rows=1 width=40) (actual time=0.229..30.391 rows=2,114 loops=1)

  • Hash Cond: (component_list.component_id = manager_list.component_id)
90. 29.455 29.455 ↓ 15.9 2,114 1

CTE Scan on component_list (cost=0.00..2.66 rows=133 width=36) (actual time=0.093..29.455 rows=2,114 loops=1)

91. 0.003 0.103 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
92. 0.100 0.100 ↑ 1.0 1 1

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

93.          

CTE place_list

94. 0.019 61.307 ↓ 2.7 8 1

HashAggregate (cost=3.10..3.13 rows=3 width=8) (actual time=61.305..61.307 rows=8 loops=1)

  • Group Key: supervisor_agg.supervisor_user, supervisor_agg.place_id
95. 0.011 61.288 ↓ 2.7 8 1

Append (cost=0.00..3.08 rows=3 width=8) (actual time=3.951..61.288 rows=8 loops=1)

96. 0.674 0.674 ↓ 0.0 0 1

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

  • Filter: (supervisor_user = 308088)
97. 29.121 29.121 ↓ 8.0 8 1

CTE Scan on employee_list employee_list_1 (cost=0.00..2.99 rows=1 width=8) (actual time=3.273..29.121 rows=8 loops=1)

  • Filter: (user_id = 308088)
  • Rows Removed by Filter: 2106
98. 31.482 31.482 ↓ 0.0 0 1

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

  • Filter: (manager_user = 308088)
  • Rows Removed by Filter: 2114
99. 0.024 78,029.887 ↓ 2.7 8 1

Sort (cost=3.48..3.49 rows=3 width=624) (actual time=78,029.887..78,029.887 rows=8 loops=1)

  • Sort Key: ((filtered_places.schedule_id)::character varying), ((filtered_places.route_order)::integer), filtered_places.place_id, ((COALESCE(employee_names.employee_name, 'N/A'::text))::character varying), filtered_places.place_room, ((filtered_places.component_id)::character varying)
  • Sort Method: quicksort Memory: 25kB
100. 0.012 78,029.863 ↓ 2.7 8 1

Nested Loop Left Join (cost=0.10..3.46 rows=3 width=624) (actual time=78,024.338..78,029.863 rows=8 loops=1)

  • Join Filter: (employee_names.schedule_id = filtered_places.schedule_id)
101. 0.010 77,632.043 ↓ 2.7 8 1

Nested Loop Left Join (cost=0.10..3.36 rows=3 width=620) (actual time=77,626.524..77,632.043 rows=8 loops=1)

  • Join Filter: (qualifying_mission_responses.place_id = filtered_places.place_id)
102. 2.924 505.313 ↓ 2.7 8 1

Hash Join (cost=0.10..3.29 rows=3 width=616) (actual time=499.800..505.313 rows=8 loops=1)

  • Hash Cond: (filtered_places.place_id = place_list.place_id)
103. 441.062 441.062 ↓ 235.4 31,311 1

CTE Scan on filtered_places (cost=0.00..2.66 rows=133 width=616) (actual time=436.343..441.062 rows=31,311 loops=1)

104. 0.010 61.327 ↓ 2.7 8 1

Hash (cost=0.06..0.06 rows=3 width=4) (actual time=61.327..61.327 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
105. 61.317 61.317 ↓ 2.7 8 1

CTE Scan on place_list (cost=0.00..0.06 rows=3 width=4) (actual time=61.312..61.317 rows=8 loops=1)

106. 77,126.720 77,126.720 ↓ 0.0 0 8

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

107. 397.808 397.808 ↓ 0.0 0 8

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

Planning time : 9.482 ms
Execution time : 104,330.806 ms