explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1PeE

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 85.248 9,212.677 ↑ 1.0 1 1

Aggregate (cost=10,966,568.92..10,966,568.93 rows=1 width=8) (actual time=9,212.677..9,212.677 rows=1 loops=1)

  • Output: count(*)
  • Buffers: shared hit=6687939 read=462084 written=8
2. 348.678 9,127.429 ↓ 1.2 612,831 1

Merge Join (cost=0.85..10,965,297.44 rows=508,590 width=0) (actual time=0.040..9,127.429 rows=612,831 loops=1)

  • Merge Cond: (inbound_shipments.id = inbound_shipments_1.id)
  • Buffers: shared hit=6687939 read=462084 written=8
3. 379.079 379.079 ↑ 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.006..379.079 rows=1,017,173 loops=1)

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

Index Only Scan using inbound_shipments_pkey on public.inbound_shipments inbound_shipments_1 (cost=0.42..10,886,498.76 rows=508,590 width=4) (actual time=0.031..8,399.672 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=6062073 read=462084 written=8
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=2035165 read=20
7. 6,098.142 6,098.142 ↓ 0.0 0 1,016,357

Index Scan using index_transport_contents_on_inbound_shipment_id on public.transport_contents (cost=0.42..8.46 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=1,016,357)

  • Output: transport_contents.inbound_shipment_id
  • Index Cond: (inbound_shipments_1.id = transport_contents.inbound_shipment_id)
  • Filter: (((transport_contents.shipment_type)::text = 'SP'::text) AND ((transport_contents.transport_details)::text ~~ '%""TrackingId"":%'::text) AND ((transport_contents.transport_details)::text ~~ '%""CarrierName"":%'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=3454848 read=408257 written=8
Planning time : 0.431 ms
Execution time : 9,212.721 ms