explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 73tB : Optimization for: plan #1PeE

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 65.272 4,954.657 ↑ 1.0 1 1

Aggregate (cost=10,955,125.64..10,955,125.65 rows=1 width=8) (actual time=4,954.657..4,954.657 rows=1 loops=1)

  • Output: count(*)
  • Buffers: shared hit=6442457 read=299380 written=4
2. 286.881 4,889.385 ↓ 1.2 612,831 1

Merge Join (cost=0.85..10,953,854.17 rows=508,590 width=0) (actual time=0.048..4,889.385 rows=612,831 loops=1)

  • Merge Cond: (inbound_shipments.id = inbound_shipments_1.id)
  • Buffers: shared hit=6442457 read=299380 written=4
3. 312.156 312.156 ↑ 1.0 1,017,173 1

Index Only Scan using inbound_shipments_pkey on public.inbound_shipments (cost=0.42..69,898.35 rows=1,017,180 width=4) (actual time=0.010..312.156 rows=1,017,173 loops=1)

  • Output: inbound_shipments.id
  • Heap Fetches: 1017173
  • Buffers: shared hit=625866
4. 1,240.454 4,290.348 ↓ 1.2 612,831 1

Index Only Scan using inbound_shipments_pkey on public.inbound_shipments inbound_shipments_1 (cost=0.42..10,875,055.49 rows=508,590 width=4) (actual time=0.034..4,290.348 rows=612,831 loops=1)

  • Output: inbound_shipments_1.id
  • Filter: (NOT CASE WHEN (SubPlan 1) THEN true WHEN (SubPlan 2) THEN true ELSE false END)
  • Rows Removed by Filter: 404349
  • Heap Fetches: 1017180
  • Buffers: shared hit=5816591 read=299380 written=4
5.          

SubPlan (for Index Only Scan)

6. 1,017.180 1,017.180 ↓ 0.0 0 1,017,180

Index Only Scan using index_shipment_bols_on_inbound_shipment_id on public.shipment_bols (cost=0.28..12.09 rows=2 width=4) (actual time=0.001..0.001 rows=0 loops=1,017,180)

  • Output: shipment_bols.inbound_shipment_id
  • Index Cond: (shipment_bols.inbound_shipment_id = inbound_shipments_1.id)
  • Heap Fetches: 823
  • Buffers: shared hit=2035132 read=53
7. 2,032.714 2,032.714 ↓ 0.0 0 1,016,357

Index Only Scan using ""(transport_contents_have_tracking_url"" on public.transport_contents (cost=0.42..8.44 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1,016,357)

  • Output: transport_contents.inbound_shipment_id
  • Index Cond: (transport_contents.inbound_shipment_id = inbound_shipments_1.id)
  • Heap Fetches: 403526
  • Buffers: shared hit=3203152 read=251767 written=4
Planning time : 9.262 ms
Execution time : 4,954.710 ms