explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uZeiK

Settings
# exclusive inclusive rows x rows loops node
1. 94.966 50,970.162 ↓ 1,007.8 21,164 1

GroupAggregate (cost=262,340.02..262,340.91 rows=21 width=83) (actual time=50,852.395..50,970.162 rows=21,164 loops=1)

  • Group Key: a."to", a.company, a.street, a.unit, a.city, a.state, a.country, a.phone_number, csm.customer_id, csm.customer_email, cs.ltv
2. 1,240.220 50,875.196 ↓ 4,587.7 96,341 1

Sort (cost=262,340.02..262,340.07 rows=21 width=83) (actual time=50,852.367..50,875.196 rows=96,341 loops=1)

  • Sort Key: a."to", a.company, a.street, a.unit, a.city, a.state, a.country, a.phone_number, csm.customer_id, csm.customer_email, cs.ltv
  • Sort Method: quicksort Memory: 20344kB
3. 79.795 49,634.976 ↓ 4,587.7 96,341 1

Nested Loop (cost=237,499.78..262,339.56 rows=21 width=83) (actual time=905.155..49,634.976 rows=96,341 loops=1)

4. 144.538 40,306.445 ↓ 4,587.7 96,341 1

Nested Loop (cost=237,499.34..262,210.72 rows=21 width=50) (actual time=905.137..40,306.445 rows=96,341 loops=1)

5. 124.310 22,303.471 ↓ 4,945.6 113,748 1

Nested Loop (cost=237,498.78..261,981.18 rows=23 width=50) (actual time=905.094..22,303.471 rows=113,748 loops=1)

6. 68.506 1,012.925 ↓ 56.5 58,149 1

Merge Join (cost=237,498.21..238,082.97 rows=1,030 width=42) (actual time=905.008..1,012.925 rows=58,149 loops=1)

  • Merge Cond: (csm.customer_id = cs.customer_id)
7. 47.745 242.064 ↑ 1.0 58,160 1

Sort (cost=119,498.18..119,647.96 rows=59,914 width=30) (actual time=224.650..242.064 rows=58,160 loops=1)

  • Sort Key: csm.customer_id
  • Sort Method: quicksort Memory: 6081kB
8. 183.782 194.319 ↑ 1.0 58,160 1

Bitmap Heap Scan on customer_store_map csm (cost=1,732.77..114,743.82 rows=59,914 width=30) (actual time=18.621..194.319 rows=58,160 loops=1)

  • Recheck Cond: (store_id = 21503975)
  • Heap Blocks: exact=44043
9. 10.537 10.537 ↑ 1.0 59,181 1

Bitmap Index Scan on customer_store_map_store_id_idx (cost=0.00..1,717.79 rows=59,914 width=0) (actual time=10.537..10.537 rows=59,181 loops=1)

  • Index Cond: (store_id = 21503975)
10. 49.360 702.355 ↓ 1.0 58,151 1

Sort (cost=118,000.02..118,138.55 rows=55,412 width=12) (actual time=680.351..702.355 rows=58,151 loops=1)

  • Sort Key: cs.customer_id
  • Sort Method: quicksort Memory: 4262kB
11. 652.995 652.995 ↓ 1.0 58,151 1

Seq Scan on customer_stats cs (cost=0.00..113,634.14 rows=55,412 width=12) (actual time=0.578..652.995 rows=58,151 loops=1)

  • Filter: (store_id = 21503975)
  • Rows Removed by Filter: 5746334
12. 21,166.236 21,166.236 ↓ 2.0 2 58,149

Index Scan using shipment_customer_idx on shipment sh (cost=0.56..23.19 rows=1 width=24) (actual time=0.224..0.364 rows=2 loops=58,149)

  • Index Cond: (customer_id = csm.customer_id)
  • Filter: ((shipped_at >= '2019-01-01 00:00:00'::timestamp without time zone) AND (store_id = 21503975) AND (status = 2))
  • Rows Removed by Filter: 8
13. 17,858.436 17,858.436 ↑ 4.0 1 113,748

Index Scan using fulfillment_shipment_idx on fulfillment f (cost=0.56..9.94 rows=4 width=8) (actual time=0.156..0.157 rows=1 loops=113,748)

  • Index Cond: (shipment_id = sh.id)
  • Filter: (subscription_id IS NOT NULL)
  • Rows Removed by Filter: 0
14. 9,248.736 9,248.736 ↑ 1.0 1 96,341

Index Scan using address_pkey on address a (cost=0.43..6.12 rows=1 width=49) (actual time=0.094..0.096 rows=1 loops=96,341)

  • Index Cond: (id = sh.ship_address_id)
Planning time : 2.943 ms
Execution time : 50,977.236 ms