explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xi3j

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 5,704.414 ↑ 26.9 210 1

Finalize GroupAggregate (cost=133,216.35..133,935.18 rows=5,658 width=24) (actual time=5,697.440..5,704.414 rows=210 loops=1)

  • Group Key: s.delivery_service_id, d.delivery_service_id, d.type, s.date
  • Buffers: shared hit=974157 read=82854 dirtied=202
  • I/O Timings: read=3708.960
2. 0.000 5,706.416 ↑ 8.0 591 1

Gather Merge (cost=133,216.35..133,819.65 rows=4,716 width=24) (actual time=5,697.402..5,706.416 rows=591 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=2872486 read=244747 dirtied=667
  • I/O Timings: read=11173.122
3. 13.707 17,097.078 ↑ 12.0 197 3

Partial GroupAggregate (cost=132,216.33..132,275.28 rows=2,358 width=24) (actual time=5,692.863..5,699.026 rows=197 loops=3)

  • Group Key: s.delivery_service_id, d.delivery_service_id, d.type, s.date
  • Buffers: shared hit=2872486 read=244747 dirtied=667
  • I/O Timings: read=11173.122
4. 63.249 17,083.371 ↓ 9.8 23,095 3

Sort (cost=132,216.33..132,222.22 rows=2,358 width=16) (actual time=5,692.824..5,694.457 rows=23,095 loops=3)

  • Sort Key: s.delivery_service_id, d.delivery_service_id, d.type, s.date
  • Sort Method: quicksort Memory: 1865kB
  • Buffers: shared hit=2872486 read=244747 dirtied=667
  • I/O Timings: read=11173.122
5. 29.148 17,020.122 ↓ 9.8 23,095 3

Nested Loop (cost=529.03..132,084.24 rows=2,358 width=16) (actual time=6.929..5,673.374 rows=23,095 loops=3)

  • Join Filter: (o.delivery_id = d.id)
  • Buffers: shared hit=2872454 read=244747 dirtied=667
  • I/O Timings: read=11173.122
6. 103.638 16,575.264 ↓ 9.8 23,095 3

Nested Loop Left Join (cost=528.59..130,322.15 rows=2,358 width=24) (actual time=6.893..5,525.088 rows=23,095 loops=3)

  • Filter: ((o.status <> ALL ('{1,2,7}'::integer[])) OR ((o.status = 7) AND (poh.status = ANY ('{4,5}'::integer[]))))
  • Rows Removed by Filter: 3694
  • Buffers: shared hit=2596950 read=242944 dirtied=650
  • I/O Timings: read=11151.208
7. 60.042 16,230.525 ↓ 7.0 26,789 3

Nested Loop Left Join (cost=528.02..124,732.52 rows=3,850 width=36) (actual time=4.072..5,410.175 rows=26,789 loops=3)

  • Join Filter: (o.status = 7)
  • Buffers: shared hit=2546424 read=234644 dirtied=645
  • I/O Timings: read=10990.508
8. 34.987 2,829.561 ↓ 7.0 26,789 3

Nested Loop (cost=527.46..101,586.04 rows=3,850 width=36) (actual time=3.213..943.187 rows=26,789 loops=3)

  • Buffers: shared hit=926597 read=60896 dirtied=292
  • I/O Timings: read=582.611
9. 529.431 1,266.510 ↓ 1.0 63,669 3

Hash Join (cost=526.90..57,062.63 rows=62,265 width=16) (actual time=2.848..422.170 rows=63,669 loops=3)

  • Hash Cond: (p.shop_shipment_id = s.id)
  • Buffers: shared hit=4995 read=38503 dirtied=280
  • I/O Timings: read=191.555
10. 729.012 729.012 ↑ 1.2 904,029 3

Parallel Seq Scan on parcel p (cost=0.00..53,647.27 rows=1,100,127 width=16) (actual time=0.021..243.004 rows=904,029 loops=3)

  • Buffers: shared hit=4143 read=38503 dirtied=280
  • I/O Timings: read=191.555
11. 0.708 8.067 ↓ 1.0 1,161 3

Hash (cost=512.94..512.94 rows=1,117 width=16) (actual time=2.689..2.689 rows=1,161 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 71kB
  • Buffers: shared hit=825
12. 7.359 7.359 ↓ 1.0 1,161 3

Seq Scan on shipment s (cost=0.00..512.94 rows=1,117 width=16) (actual time=2.162..2.453 rows=1,161 loops=3)

  • Filter: (date >= '2019-04-03'::date)
  • Rows Removed by Filter: 17853
  • Buffers: shared hit=825
13. 1,528.064 1,528.064 ↓ 0.0 0 191,008

Index Scan using i_orders_delivery_id on orders o (cost=0.56..0.71 rows=1 width=20) (actual time=0.008..0.008 rows=0 loops=191,008)

  • Index Cond: (delivery_id = p.delivery_id)
  • Filter: (fulfilment AND (user_id <> '2308324861409815965'::bigint) AND (rgb = 1) AND ((status <> ALL ('{1,2,7}'::integer[])) OR (status = 7)))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=921602 read=22393 dirtied=12
  • I/O Timings: read=391.056
14. 13,340.922 13,340.922 ↓ 0.0 0 80,367

Index Scan using i_order_history_oid on order_history oh (cost=0.57..5.99 rows=2 width=16) (actual time=0.150..0.166 rows=0 loops=80,367)

  • Index Cond: (order_id = o.id)
  • Filter: ((event_type = 1) AND (status = 7))
  • Rows Removed by Filter: 22
  • Buffers: shared hit=1619827 read=173748 dirtied=353
  • I/O Timings: read=10407.897
15. 241.101 241.101 ↓ 0.0 0 80,367

Index Scan using order_history_pkey on order_history poh (cost=0.57..1.43 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=80,367)

  • Index Cond: (id = oh.prev_history_id)
  • Buffers: shared hit=50526 read=8300 dirtied=5
  • I/O Timings: read=160.700
16. 415.710 415.710 ↑ 1.0 1 69,285

Index Scan using order_delivery_pkey on order_delivery d (cost=0.44..0.73 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=69,285)

  • Index Cond: (id = p.delivery_id)
  • Buffers: shared hit=275504 read=1803 dirtied=17
  • I/O Timings: read=21.914