explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YUJT

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 43,112.101 ↓ 0.0 0 1

GroupAggregate (cost=351,771.53..352,059.75 rows=14,411 width=12) (actual time=43,112.101..43,112.101 rows=0 loops=1)

  • Output: carts.id, sum(CASE WHEN (favorites.modified_at > user_per_cart_location_notifications.notification_pointer_date) THEN 1 ELSE 0 END)
  • Group Key: carts.id
2. 0.008 43,112.100 ↓ 0.0 0 1

Sort (cost=351,771.53..351,807.55 rows=14,411 width=20) (actual time=43,112.099..43,112.100 rows=0 loops=1)

  • Output: carts.id, favorites.modified_at, user_per_cart_location_notifications.notification_pointer_date
  • Sort Key: carts.id
  • Sort Method: quicksort Memory: 25kB
3. 0.001 43,112.092 ↓ 0.0 0 1

Nested Loop Left Join (cost=17,900.51..350,776.09 rows=14,411 width=20) (actual time=43,112.092..43,112.092 rows=0 loops=1)

  • Output: carts.id, favorites.modified_at, user_per_cart_location_notifications.notification_pointer_date
4. 2,161.294 43,112.091 ↓ 0.0 0 1

Hash Join (cost=17,899.94..255,372.53 rows=14,411 width=16) (actual time=43,112.091..43,112.091 rows=0 loops=1)

  • Output: carts.id, cart_locations.id, favorites.modified_at
  • Inner Unique: true
  • Hash Cond: (cart_locations.cart_id = carts.id)
  • Join Filter: (((carts.user_id = 'dd87a57757289543e2e2d569f5d80f2ecd9dc5d82cd3cc27d7564fc5e7f116ad'::text) OR (alternatives: SubPlan 1 or hashed SubPlan 2)) AND ((alternati
  • Rows Removed by Join Filter: 20977
5. 222.205 32,794.938 ↑ 2.7 22,114 1

Gather (cost=1,000.98..234,169.82 rows=60,093 width=38) (actual time=0.494..32,794.938 rows=22,114 loops=1)

  • Output: cart_locations.cart_id, cart_locations.id, favorites.modified_at, favorites.user_id
  • Workers Planned: 1
  • Workers Launched: 0
6. 31,361.153 32,572.733 ↑ 1.6 22,114 1

Nested Loop (cost=0.97..227,160.52 rows=35,349 width=38) (actual time=0.076..32,572.733 rows=22,114 loops=1)

  • Output: cart_locations.cart_id, cart_locations.id, favorites.modified_at, favorites.user_id
7. 1,210.591 1,210.591 ↓ 1.7 30,618 1

Parallel Index Scan using favorites_not_delete_idx on public.favorites (cost=0.29..2,561.96 rows=18,001 width=96) (actual time=0.012..1,210.591 rows=30,618 loops=1)

  • Output: favorites.id, favorites.user_id, favorites.soft_delete, favorites.created_at, favorites.modified_at, favorites.location_id, favorites.type, favorites.type_id, favorites.liked
8. 0.989 0.989 ↑ 2.0 1 30,618

Index Scan using cart_locations_location_id_idx on public.cart_locations (cost=0.69..12.46 rows=2 width=73) (actual time=0.937..0.989 rows=1 loops=30,618)

  • Output: cart_locations.id, cart_locations.cart_id, cart_locations.location_id, cart_locations.added_by_user_id, cart_locations.added_by_user_source, cart_locations.soft_delete, cart_locations.created_at, cart_locations.modified_at
  • Index Cond: (cart_locations.location_id = favorites.location_id)
9. 2,240.370 8,155.843 ↓ 1.0 273,876 1

Hash (cost=10,274.23..10,274.23 rows=273,499 width=68) (actual time=8,155.843..8,155.843 rows=273,876 loops=1)

  • Output: carts.id, carts.user_id
  • Buckets: 65536 Batches: 8 Memory Usage: 3886kB
10. 5,915.473 5,915.473 ↓ 1.0 273,876 1

Seq Scan on public.carts (cost=0.00..10,274.23 rows=273,499 width=68) (actual time=0.006..5,915.473 rows=273,876 loops=1)

  • Output: carts.id, carts.user_id
  • Filter: (NOT carts.soft_delete)
  • Rows Removed by Filter: 12238
11.          

SubPlan (for Hash Join)

12. 0.000 0.000 ↓ 0.0 0

Index Only Scan using cart_shared_with_republic_users_idx on public.cart_shared_with_users (cost=0.28..8.29 rows=1 width=0) (never executed)

  • Index Cond: ((cart_shared_with_users.user_id = 'dd87a57757289543e2e2d569f5d80f2ecd9dc5d82cd3cc27d7564fc5e7f116ad'::text) AND (cart_shared_with_users.cart_id = cart_locations.cart_id))
  • Heap Fetches: 0
13. 0.016 0.016 ↓ 0.0 0 1

Index Only Scan using cart_shared_with_republic_users_idx on public.cart_shared_with_users cart_shared_with_users_1 (cost=0.28..8.29 rows=1 width=4) (actual time=0.016..0.016 rows=0 loops=1)

  • Output: cart_shared_with_users_1.cart_id
  • Index Cond: (cart_shared_with_users_1.user_id = 'dd87a57757289543e2e2d569f5d80f2ecd9dc5d82cd3cc27d7564fc5e7f116ad'::text)
  • Heap Fetches: 0
14. 0.000 0.000 ↓ 0.0 0

Index Only Scan using cart_shared_with_republic_users_idx on public.cart_shared_with_users cart_shared_with_users_2 (cost=0.28..8.29 rows=1 width=0) (never executed)

  • Index Cond: ((cart_shared_with_users_2.user_id = favorites.user_id) AND (cart_shared_with_users_2.cart_id = cart_locations.cart_id))
  • Heap Fetches: 0
15. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.cart_shared_with_users cart_shared_with_users_3 (cost=0.00..27.44 rows=872 width=27) (never executed)

  • Output: cart_shared_with_users_3.cart_id, cart_shared_with_users_3.user_id
  • Filter: ((NOT cart_shared_with_users_3.soft_delete) AND (cart_shared_with_users_3.user_source = 'republic'::user_source_id))
16. 0.000 0.000 ↓ 0.0 0

Index Scan using user_per_cart_location_notifications_idx on public.user_per_cart_location_notifications (cost=0.56..6.61 rows=1 width=12) (never executed)

  • Output: user_per_cart_location_notifications.user_id, user_per_cart_location_notifications.cart_location_id, user_per_cart_location_notifications.notification_pointer_date
  • Index Cond: (cart_locations.id = user_per_cart_location_notifications.cart_location_id)
Planning time : 2.343 ms
Execution time : 43,112.284 ms