explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2KRC

Settings
# exclusive inclusive rows x rows loops node
1. 524.791 251,642.266 ↓ 67,324.0 134,648 1

Nested Loop Left Join (cost=69,887.67..69,890.11 rows=2 width=1,220) (actual time=13,154.089..251,642.266 rows=134,648 loops=1)

  • Join Filter: ((earned_points_and_dollars.published_campaign_id = published_campaign_availability.published_campaign_id) AND (earned_points_and_dollars.user_id = published_campaign_availability.user_id) AND (earned_points_and_dollars.place_id = published_campaign_availability.place_id))
  • Rows Removed by Join Filter: 2,558,122
2.          

CTE raw_qualifying_campaigns

3. 0.679 14.816 ↓ 2.5 353 1

Finalize GroupAggregate (cost=62,977.71..63,020.54 rows=139 width=168) (actual time=13.443..14.816 rows=353 loops=1)

  • Group Key: campaigns.id
4. 5.012 14.137 ↓ 2.5 353 1

Gather Merge (cost=62,977.71..63,013.59 rows=139 width=209) (actual time=13.428..14.137 rows=353 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
5. 0.279 9.125 ↓ 1.3 176 2 / 2

Partial GroupAggregate (cost=61,977.70..61,997.95 rows=139 width=209) (actual time=8.840..9.125 rows=176 loops=2)

  • Group Key: campaigns.id
6. 0.298 8.846 ↑ 4.4 280 2 / 2

Sort (cost=61,977.70..61,980.78 rows=1,234 width=237) (actual time=8.821..8.846 rows=280 loops=2)

  • Sort Key: campaigns.id
  • Sort Method: quicksort Memory: 232kB
  • Worker 0: Sort Method: quicksort Memory: 79kB
7. 0.000 8.548 ↑ 4.4 280 2 / 2

Nested Loop (cost=21.35..61,914.34 rows=1,234 width=237) (actual time=0.330..8.548 rows=280 loops=2)

8. 3.956 4.108 ↓ 3.4 278 2 / 2

Parallel Bitmap Heap Scan on campaigns (cost=20.79..2,617.95 rows=82 width=177) (actual time=0.272..4.108 rows=278 loops=2)

  • Recheck Cond: (company_id = 2,083)
  • Filter: ((state)::text = ANY ('{versioned,published}'::text[]))
  • Rows Removed by Filter: 392
  • Heap Blocks: exact=677
9. 0.152 0.152 ↑ 1.0 1,340 1 / 2

Bitmap Index Scan on index_campaigns_on_company_id (cost=0.00..20.75 rows=1,377 width=0) (actual time=0.303..0.304 rows=1,340 loops=1)

  • Index Cond: (company_id = 2,083)
10. 4.456 4.456 ↑ 342.0 1 557 / 2

Index Scan using index_tasks_on_campaign_id_catalog_id on tasks tasks_1 (cost=0.56..719.71 rows=342 width=64) (actual time=0.014..0.016 rows=1 loops=557)

  • Index Cond: (campaign_id = campaigns.id)
  • Filter: (exist(metadata, 'points'::text) OR exist(metadata, 'bonus_points'::text) OR exist(metadata, 'payout_dollars'::text))
  • Rows Removed by Filter: 5
11.          

CTE qualifying_campaign_agg

12. 2.686 18.591 ↓ 126.0 126 1

GroupAggregate (cost=10.94..11.00 rows=1 width=652) (actual time=15.770..18.591 rows=126 loops=1)

  • Group Key: published_campaigns.id, published_campaigns.name, published_campaigns.brand_target, published_campaigns.version_identifier
13. 0.271 15.905 ↓ 69.0 138 1

Sort (cost=10.94..10.95 rows=2 width=565) (actual time=15.739..15.905 rows=138 loops=1)

  • Sort Key: published_campaigns.id, published_campaigns.name, published_campaigns.brand_target, published_campaigns.version_identifier
  • Sort Method: quicksort Memory: 44kB
14. 0.036 15.634 ↓ 69.0 138 1

Nested Loop (cost=3.57..10.93 rows=2 width=565) (actual time=15.210..15.634 rows=138 loops=1)

15. 0.092 15.202 ↓ 132.0 132 1

Hash Join (cost=3.14..6.45 rows=1 width=561) (actual time=15.137..15.202 rows=132 loops=1)

  • Hash Cond: (raw_qualifying_campaigns.version_identifier = published_campaigns.version_identifier)
16. 13.477 13.477 ↓ 2.5 353 1

CTE Scan on raw_qualifying_campaigns (cost=0.00..2.78 rows=139 width=9) (actual time=13.445..13.477 rows=353 loops=1)

17. 0.041 1.633 ↓ 126.0 126 1

Hash (cost=3.13..3.13 rows=1 width=556) (actual time=1.632..1.633 rows=126 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
18. 1.592 1.592 ↓ 126.0 126 1

CTE Scan on raw_qualifying_campaigns published_campaigns (cost=0.00..3.13 rows=1 width=556) (actual time=1.026..1.592 rows=126 loops=1)

  • Filter: ((state)::text = 'published'::text)
  • Rows Removed by Filter: 227
19. 0.396 0.396 ↑ 2.0 1 132

Index Only Scan using index_campaign_place_groups_on_campaign_id_place_group_id on campaign_place_groups (cost=0.43..4.46 rows=2 width=8) (actual time=0.003..0.003 rows=1 loops=132)

  • Index Cond: (campaign_id = published_campaigns.id)
  • Heap Fetches: 138
20.          

CTE qualifying_users

21. 0.412 5.060 ↓ 4.6 216 1

Nested Loop (cost=5.32..337.01 rows=47 width=254) (actual time=0.206..5.060 rows=216 loops=1)

22. 1.970 2.056 ↓ 4.6 216 1

Bitmap Heap Scan on company_memberships (cost=4.90..126.69 rows=47 width=108) (actual time=0.131..2.056 rows=216 loops=1)

  • Recheck Cond: ((company_id = 2,083) AND (disabled_at IS NULL))
  • Filter: (metadata IS NOT NULL)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=207
23. 0.086 0.086 ↓ 3.5 217 1

Bitmap Index Scan on index_memberships_on_company_id_role (cost=0.00..4.88 rows=62 width=0) (actual time=0.086..0.086 rows=217 loops=1)

  • Index Cond: (company_id = 2,083)
24. 2.592 2.592 ↑ 1.0 1 216

Index Scan using users_pkey on users (cost=0.42..4.44 rows=1 width=43) (actual time=0.012..0.012 rows=1 loops=216)

  • Index Cond: (id = company_memberships.user_id)
25.          

CTE user_visibility

26. 0.018 5.275 ↑ 1.0 1 1

HashAggregate (cost=2.97..2.98 rows=1 width=552) (actual time=5.273..5.275 rows=1 loops=1)

  • Group Key: logged_in_user.user_id, logged_in_user.email
27. 0.052 5.257 ↓ 4.2 17 1

Nested Loop (cost=0.00..2.94 rows=4 width=524) (actual time=2.355..5.257 rows=17 loops=1)

  • Join Filter: ((logged_in_user.asw_handler = qualifying_users_1.vp_code) OR (logged_in_user.asw_handler = qualifying_users_1.manager_code) OR (logged_in_user.rep_manager_code = qualifying_users_1.manager_code) OR (qualifying_users_1.user_id = logged_in_user.user_id))
  • Rows Removed by Join Filter: 199
28. 2.361 2.361 ↑ 1.0 1 1

CTE Scan on qualifying_users logged_in_user (cost=0.00..1.06 rows=1 width=584) (actual time=2.350..2.361 rows=1 loops=1)

  • Filter: (user_id = 28,955)
  • Rows Removed by Filter: 215
29. 2.844 2.844 ↓ 4.6 216 1

CTE Scan on qualifying_users qualifying_users_1 (cost=0.00..0.94 rows=47 width=68) (actual time=0.000..2.844 rows=216 loops=1)

30.          

CTE user_places

31. 0.000 61.089 ↓ 13,150.0 13,150 1

Nested Loop (cost=0.88..4,179.03 rows=1 width=26) (actual time=0.102..61.089 rows=13,150 loops=1)

32. 1.725 8.946 ↓ 7.5 13,150 1

Nested Loop (cost=0.44..274.96 rows=1,752 width=8) (actual time=0.064..8.946 rows=13,150 loops=1)

33. 0.080 0.132 ↓ 8.5 17 1

Nested Loop (cost=0.00..2.02 rows=2 width=4) (actual time=0.003..0.132 rows=17 loops=1)

  • Join Filter: (qualifying_users_2.user_id = ANY (user_visibility_1.supervisees))
  • Rows Removed by Join Filter: 199
34. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on user_visibility user_visibility_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.002 rows=1 loops=1)

35. 0.050 0.050 ↓ 4.6 216 1

CTE Scan on qualifying_users qualifying_users_2 (cost=0.00..0.94 rows=47 width=4) (actual time=0.000..0.050 rows=216 loops=1)

36. 7.089 7.089 ↑ 1.1 774 17

Index Only Scan using uap_user_and_place_unique on user_place_assignments (cost=0.44..127.71 rows=876 width=8) (actual time=0.021..0.417 rows=774 loops=17)

  • Index Cond: (user_id = qualifying_users_2.user_id)
  • Heap Fetches: 11,636
37. 52.600 52.600 ↑ 1.0 1 13,150

Index Scan using locations_pkey on places (cost=0.44..2.23 rows=1 width=22) (actual time=0.004..0.004 rows=1 loops=13,150)

  • Index Cond: (id = user_place_assignments.place_id)
  • Filter: ((disabled_at IS NULL) AND (company_id = 2,083))
38.          

CTE published_campaign_availability

39. 2,411.043 12,203.826 ↓ 134,644.0 134,644 1

Nested Loop (cost=0.57..5.46 rows=1 width=592) (actual time=15.847..12,203.826 rows=134,644 loops=1)

  • Join Filter: (place_group_places.place_group_id = ANY (qualifying_campaign_agg_1.qualifying_place_group_ids))
  • Rows Removed by Join Filter: 21,647,354
40. 18.963 18.963 ↓ 126.0 126 1

CTE Scan on qualifying_campaign_agg qualifying_campaign_agg_1 (cost=0.00..0.02 rows=1 width=100) (actual time=15.772..18.963 rows=126 loops=1)

41. 2,764.818 9,773.820 ↓ 10,169.0 172,873 126

Nested Loop (cost=0.57..5.05 rows=17 width=528) (actual time=0.006..77.570 rows=172,873 loops=126)

42. 381.402 381.402 ↓ 13,150.0 13,150 126

CTE Scan on user_places user_places_1 (cost=0.00..0.02 rows=1 width=524) (actual time=0.000..3.027 rows=13,150 loops=126)

43. 6,627.600 6,627.600 ↑ 1.3 13 1,656,900

Index Only Scan using index_place_group_places_on_place_id_and_place_group_id on place_group_places (cost=0.57..4.86 rows=17 width=8) (actual time=0.002..0.004 rows=13 loops=1,656,900)

  • Index Cond: (place_id = user_places_1.place_id)
  • Heap Fetches: 1,606,500
44.          

CTE most_recent_responses

45. 0.005 13,132.462 ↓ 19.0 19 1

Subquery Scan on all_qualifying_responses (cost=26.26..26.31 rows=1 width=21) (actual time=13,132.441..13,132.462 rows=19 loops=1)

  • Filter: (all_qualifying_responses.rank = 1)
  • Rows Removed by Filter: 1
46. 0.031 13,132.457 ↓ 20.0 20 1

WindowAgg (cost=26.26..26.30 rows=1 width=37) (actual time=13,132.439..13,132.457 rows=20 loops=1)

47. 0.055 13,132.426 ↓ 20.0 20 1

Sort (cost=26.26..26.26 rows=1 width=60) (actual time=13,132.424..13,132.426 rows=20 loops=1)

  • Sort Key: published_campaign_availability_1.user_id, published_campaign_availability_1.place_id, published_campaign_availability_1.published_campaign_id, mission_responses.completed_at DESC
  • Sort Method: quicksort Memory: 27kB
48. 192.447 13,132.371 ↓ 20.0 20 1

Nested Loop (cost=24.19..26.25 rows=1 width=60) (actual time=7,555.053..13,132.371 rows=20 loops=1)

49. 12,266.704 12,266.704 ↓ 134,644.0 134,644 1

CTE Scan on published_campaign_availability published_campaign_availability_1 (cost=0.00..0.02 rows=1 width=76) (actual time=0.003..12,266.704 rows=134,644 loops=1)

50. 0.000 673.220 ↓ 0.0 0 134,644

Bitmap Heap Scan on mission_responses (cost=24.19..26.22 rows=1 width=24) (actual time=0.005..0.005 rows=0 loops=134,644)

  • Recheck Cond: ((place_id = published_campaign_availability_1.place_id) AND ((state)::text = 'completed'::text) AND (user_id = published_campaign_availability_1.user_id))
  • Filter: (campaign_id = ANY (published_campaign_availability_1.all_campaign_ids))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=2,092
51. 69.772 673.220 ↓ 0.0 0 134,644

BitmapAnd (cost=24.19..24.19 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=134,644)

52. 403.932 403.932 ↑ 30.4 8 134,644

Bitmap Index Scan on index_mission_responses_on_place_id_and_campaign_id_and_state (cost=0.00..7.00 rows=243 width=0) (actual time=0.003..0.003 rows=8 loops=134,644)

  • Index Cond: ((place_id = published_campaign_availability_1.place_id) AND ((state)::text = 'completed'::text))
53. 199.516 199.516 ↑ 32.8 34 49,879

Bitmap Index Scan on index_mission_responses_on_user_id (cost=0.00..16.94 rows=1,116 width=0) (actual time=0.004..0.004 rows=34 loops=49,879)

  • Index Cond: (user_id = published_campaign_availability_1.user_id)
54. 138,339.742 250,982.827 ↓ 67,324.0 134,648 1

Nested Loop Left Join (cost=0.00..2.20 rows=2 width=1,128) (actual time=13,153.745..250,982.827 rows=134,648 loops=1)

  • Join Filter: ((published_campaign_availability.user_id = qualifying_users.user_id) AND (published_campaign_availability.place_id = user_places.place_id))
  • Rows Removed by Join Filter: 1,770,568,600
55. 31.456 96.827 ↓ 6,575.5 13,151 1

Nested Loop Left Join (cost=0.00..2.07 rows=2 width=560) (actual time=5.387..96.827 rows=13,151 loops=1)

  • Join Filter: (user_places.user_id = qualifying_users.user_id)
  • Rows Removed by Join Filter: 210,400
56. 0.091 5.429 ↓ 8.5 17 1

Nested Loop (cost=0.00..2.02 rows=2 width=40) (actual time=5.281..5.429 rows=17 loops=1)

  • Join Filter: (qualifying_users.user_id = ANY (user_visibility.supervisees))
  • Rows Removed by Join Filter: 199
57. 5.279 5.279 ↑ 1.0 1 1

CTE Scan on user_visibility (cost=0.00..0.02 rows=1 width=36) (actual time=5.278..5.279 rows=1 loops=1)

58. 0.059 0.059 ↓ 4.6 216 1

CTE Scan on qualifying_users (cost=0.00..0.94 rows=47 width=36) (actual time=0.000..0.059 rows=216 loops=1)

59. 59.942 59.942 ↓ 13,150.0 13,150 17

CTE Scan on user_places (cost=0.00..0.02 rows=1 width=524) (actual time=0.006..3.526 rows=13,150 loops=17)

60. 96,336.228 112,546.258 ↓ 134,644.0 134,644 13,151

Materialize (cost=0.00..0.10 rows=1 width=568) (actual time=1.000..8.558 rows=134,644 loops=13,151)

61. 227.331 16,210.030 ↓ 134,644.0 134,644 1

Nested Loop Left Join (cost=0.00..0.09 rows=1 width=568) (actual time=13,148.342..16,210.030 rows=134,644 loops=1)

  • Join Filter: ((most_recent_responses.published_campaign_id = published_campaign_availability.published_campaign_id) AND (most_recent_responses.user_id = published_campaign_availability.user_id) AND (most_recent_responses.place_id = published_campaign_availability.place_id))
  • Rows Removed by Join Filter: 2,558,217
62. 1,138.717 2,652.943 ↓ 134,644.0 134,644 1

Nested Loop Left Join (cost=0.00..0.05 rows=1 width=564) (actual time=15.853..2,652.943 rows=134,644 loops=1)

  • Join Filter: (qualifying_campaign_agg.published_campaign_id = published_campaign_availability.published_campaign_id)
  • Rows Removed by Join Filter: 16,830,500
63. 33.142 33.142 ↓ 134,644.0 134,644 1

CTE Scan on published_campaign_availability (cost=0.00..0.02 rows=1 width=12) (actual time=15.850..33.142 rows=134,644 loops=1)

64. 1,481.084 1,481.084 ↓ 126.0 126 134,644

CTE Scan on qualifying_campaign_agg (cost=0.00..0.02 rows=1 width=552) (actual time=0.000..0.011 rows=126 loops=134,644)

65. 13,329.756 13,329.756 ↓ 19.0 19 134,644

CTE Scan on most_recent_responses (cost=0.00..0.02 rows=1 width=16) (actual time=0.098..0.099 rows=19 loops=134,644)

66. 134.321 134.648 ↓ 19.0 19 134,648

Materialize (cost=2,305.34..2,305.48 rows=1 width=48) (actual time=0.000..0.001 rows=19 loops=134,648)

67. 0.003 0.327 ↓ 19.0 19 1

Subquery Scan on earned_points_and_dollars (cost=2,305.34..2,305.48 rows=1 width=48) (actual time=0.295..0.327 rows=19 loops=1)

68. 0.049 0.324 ↓ 19.0 19 1

GroupAggregate (cost=2,305.34..2,305.47 rows=1 width=48) (actual time=0.294..0.324 rows=19 loops=1)

  • Group Key: most_recent_responses_1.user_id, most_recent_responses_1.place_id, most_recent_responses_1.published_campaign_id
69. 0.016 0.275 ↓ 19.0 19 1

Sort (cost=2,305.34..2,305.34 rows=1 width=270) (actual time=0.273..0.275 rows=19 loops=1)

  • Sort Key: most_recent_responses_1.user_id, most_recent_responses_1.place_id, most_recent_responses_1.published_campaign_id
  • Sort Method: quicksort Memory: 27kB
70. 0.009 0.259 ↓ 19.0 19 1

Nested Loop (cost=1.14..2,305.33 rows=1 width=270) (actual time=0.043..0.259 rows=19 loops=1)

71. 0.019 0.098 ↑ 18.0 19 1

Nested Loop (cost=0.56..730.42 rows=342 width=95) (actual time=0.026..0.098 rows=19 loops=1)

72. 0.003 0.003 ↓ 19.0 19 1

CTE Scan on most_recent_responses most_recent_responses_1 (cost=0.00..0.02 rows=1 width=21) (actual time=0.000..0.003 rows=19 loops=1)

73. 0.076 0.076 ↑ 342.0 1 19

Index Scan using index_tasks_on_campaign_id_catalog_id on tasks (cost=0.56..726.98 rows=342 width=82) (actual time=0.004..0.004 rows=1 loops=19)

  • Index Cond: (campaign_id = most_recent_responses_1.mr_campaign_id)
  • Filter: (exist(metadata, 'points'::text) OR exist(metadata, 'bonus_points'::text) OR exist(metadata, 'payout_dollars'::text))
  • Rows Removed by Filter: 2
74. 0.152 0.152 ↑ 1.0 1 19

Index Scan using task_id_mission_response_id_and_context_unique on task_responses (cost=0.57..4.59 rows=1 width=191) (actual time=0.008..0.008 rows=1 loops=19)

  • Index Cond: ((task_id = tasks.id) AND (mission_response_id = most_recent_responses_1.mr_id))
Planning time : 12.515 ms
Execution time : 251,687.249 ms