explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0UW

Settings
# exclusive inclusive rows x rows loops node
1. 374.390 204,858.226 ↑ 1.0 1 1

Aggregate (cost=31,901,996.03..31,901,996.04 rows=1 width=8) (actual time=204,858.225..204,858.226 rows=1 loops=1)

  • Output: count(*)
  • Buffers: shared hit=33562017 read=23927
2. 712.658 204,483.836 ↓ 2.0 1,016,338 1

Nested Loop (cost=0.42..31,900,724.55 rows=508,590 width=0) (actual time=0.483..204,483.836 rows=1,016,338 loops=1)

  • Buffers: shared hit=33562017 read=23927
3. 3,391.732 199,705.826 ↓ 2.0 1,016,338 1

Seq Scan on public.inbound_shipments inbound_shipments_1 (cost=0.00..31,553,259.58 rows=508,590 width=4) (actual time=0.422..199,705.826 rows=1,016,338 loops=1)

  • Output: inbound_shipments_1.id, inbound_shipments_1.shipment_id, inbound_shipments_1.shipment_name, inbound_shipments_1.shipment_status, inbound_shipments_1.destination_fulfillment_center_id, inbound_shipments_1.created_at, inbound_shipments_1.updated_at, inbound_shipments_1.ship_from_city, inbound_shipments_1.ship_from_country_code, inbound_shipments_1.ship_from_postal_code, inbound_shipments_1.ship_from_name, inbound_shipments_1.ship_from_address_one, inbound_shipments_1.ship_from_address_two, inbound_shipments_1.ship_from_state_or_province_code, inbound_shipments_1.shipment_last_updated, inbound_shipments_1.event_date, inbound_shipments_1.mws_credential_id
  • Filter: (NOT CASE WHEN (SubPlan 1) THEN true WHEN (SubPlan 2) THEN true ELSE false END)
  • Rows Removed by Filter: 842
  • Buffers: shared hit=29487047 read=23927
4.          

SubPlan (forSeq Scan)

5. 194,281.380 194,281.380 ↓ 0.0 0 1,017,180

Seq Scan on public.shipment_bols (cost=0.00..53.62 rows=2 width=4) (actual time=0.191..0.191 rows=0 loops=1,017,180)

  • Output: shipment_bols.inbound_shipment_id
  • Filter: (inbound_shipments_1.id = shipment_bols.inbound_shipment_id)
  • Rows Removed by Filter: 2048
  • Buffers: shared hit=28469803
6. 2,032.714 2,032.714 ↓ 0.0 0 1,016,357

Index Scan using index_transport_contents_on_inbound_shipment_id on public.transport_contents (cost=0.15..8.18 rows=1 width=4) (actual time=0.002..0.002 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=1016677
7. 4,065.352 4,065.352 ↑ 1.0 1 1,016,338

Index Only Scan using inbound_shipments_pkey on public.inbound_shipments (cost=0.42..0.67 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1,016,338)

  • Output: inbound_shipments.id
  • Index Cond: (inbound_shipments.id = inbound_shipments_1.id)
  • Heap Fetches: 1016338
  • Buffers: shared hit=4074970
Planning time : 1.252 ms
Execution time : 204,858.354 ms