explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C1Ml : Optimization for: plan #gZmw

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.001 198.951 ↓ 0.0 0 1

GroupAggregate (cost=129,915.27..129,996.01 rows=4,037 width=12) (actual time=198.951..198.951 rows=0 loops=1)

  • Group Key: carts.id
  • Buffers: shared hit=45379, temp read=2371 written=2371
2. 0.005 198.950 ↓ 0.0 0 1

Sort (cost=129,915.27..129,925.36 rows=4,037 width=20) (actual time=198.950..198.950 rows=0 loops=1)

  • Sort Key: carts.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=45379, temp read=2371 written=2371
3. 0.001 198.945 ↓ 0.0 0 1

Nested Loop Left Join (cost=14,925.92..129,673.47 rows=4,037 width=20) (actual time=198.945..198.945 rows=0 loops=1)

  • Buffers: shared hit=45379, temp read=2371 written=2371
4. 23.751 198.944 ↓ 0.0 0 1

Hash Join (cost=14,925.49..123,831.36 rows=4,037 width=16) (actual time=198.944..198.944 rows=0 loops=1)

  • Hash Cond: (cart_locations.cart_id = carts.id)
  • Join Filter: (((carts.user_id = '49c4cb3523c268d2b26c50c095fb057b25c07274e493d66f7e2cd0151fd92232'::text) OR (alternatives: SubPlan 1 or hashed SubPlan 2)) AND ((alternatives: SubPlan 3 or hashed SubPlan 4) OR (favorites.user_id = carts.user_id)))
  • Rows Removed by Join Filter: 4394
  • Buffers: shared hit=45379, temp read=2371 written=2371
5. 3.872 81.855 ↑ 3.6 4,499 1

Nested Loop (cost=0.56..105,703.76 rows=16,260 width=38) (actual time=0.046..81.855 rows=4,499 loops=1)

  • Buffers: shared hit=38949
6. 1.267 1.267 ↑ 1.0 8,524 1

Seq Scan on favorites (cost=0.00..304.35 rows=8,574 width=96) (actual time=0.007..1.267 rows=8,524 loops=1)

  • Filter: (NOT soft_delete)
  • Rows Removed by Filter: 60
  • Buffers: shared hit=218
7. 76.716 76.716 ↑ 2.0 1 8,524

Index Scan using cart_locations_location_id_idx on cart_locations (cost=0.56..12.27 rows=2 width=73) (actual time=0.009..0.009 rows=1 loops=8,524)

  • Index Cond: (location_id = favorites.location_id)
  • Buffers: shared hit=38731
8. 52.736 93.324 ↓ 1.0 247,808 1

Hash (cost=8,923.45..8,923.45 rows=247,798 width=68) (actual time=93.324..93.324 rows=247,808 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3565kB
  • Buffers: shared hit=6428, temp written=2333
9. 40.588 40.588 ↓ 1.0 247,808 1

Seq Scan on carts (cost=0.00..8,923.45 rows=247,798 width=68) (actual time=0.005..40.588 rows=247,808 loops=1)

  • Filter: (NOT soft_delete)
  • Rows Removed by Filter: 1741
  • Buffers: shared hit=6428
10.          

SubPlan (forHash Join)

11. 0.000 0.000 ↓ 0.0 0

Seq Scan on cart_shared_with_users (cost=0.00..3.29 rows=1 width=0) (never executed)

  • Filter: ((NOT soft_delete) AND (cart_id = cart_locations.cart_id) AND (user_id = '49c4cb3523c268d2b26c50c095fb057b25c07274e493d66f7e2cd0151fd92232'::text) AND (user_source = 'republic'::user_source_id))
12. 0.014 0.014 ↓ 0.0 0 1

Seq Scan on cart_shared_with_users cart_shared_with_users_1 (cost=0.00..3.11 rows=1 width=4) (actual time=0.014..0.014 rows=0 loops=1)

  • Filter: ((NOT soft_delete) AND (user_id = '49c4cb3523c268d2b26c50c095fb057b25c07274e493d66f7e2cd0151fd92232'::text) AND (user_source = 'republic'::user_source_id))
  • Rows Removed by Filter: 76
  • Buffers: shared hit=2
13. 0.000 0.000 ↓ 0.0 0

Seq Scan on cart_shared_with_users cart_shared_with_users_2 (cost=0.00..3.29 rows=1 width=0) (never executed)

  • Filter: ((NOT soft_delete) AND (cart_id = cart_locations.cart_id) AND (favorites.user_id = user_id) AND (user_source = 'republic'::user_source_id))
14. 0.000 0.000 ↓ 0.0 0

Seq Scan on cart_shared_with_users cart_shared_with_users_3 (cost=0.00..2.92 rows=66 width=27) (never executed)

  • Filter: ((NOT soft_delete) AND (user_source = 'republic'::user_source_id))
15. 0.000 0.000 ↓ 0.0 0

Index Scan using user_per_cart_location_notifications_idx on user_per_cart_location_notifications (cost=0.43..1.44 rows=1 width=12) (never executed)

  • Index Cond: (cart_locations.id = cart_location_id)
Planning time : 1.389 ms
Execution time : 199.038 ms