explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gr8J

Settings
# exclusive inclusive rows x rows loops node
1. 1.757 47.180 ↓ 51.3 3,747 1

Nested Loop Left Join (cost=13.71..5,279.13 rows=73 width=1,708) (actual time=14.515..47.180 rows=3,747 loops=1)

  • Output: sn.trackable_id, sn.version, sn.bill_of_lading, sn.master_leg_id, sn.deleted_at, sn.id, sn.origin_port_id, sn.master_shipment_id, sn.discriminator, sn.inactive_clock, sn.deleted_by, sn.tenant_id, sn.destination_port_id, sn.active_clock, sn.loading_port_id, sn.created_at, sn.created_by, sn.start_time, c.created_at, c.id, c.container_number, cs.line_description, cs.version, cs.site_id, cs.container_id, cs.last_free_day, cs.carrier_identifier, cs.customs_description, cs.id, cs.remote_clock, cs.last_time, cs.other_description, cs.bill_of_lading, cs.demurrage, cs.first_time, cs.names, cs.local_clock, s.name, s.created_at, s.id, s.length, s.width, s.location_id, s.site_key, s.version, s.ship_type, s.discriminator, s.mmsi, s.call_sign, ss.local_clock, ss.version, ss.location_id, ss.site_id, ss.lat, ss.names, ss.eta, ss.remote_clock, ss.lon, ss.discriminator, ss.draught, ss.last_time, ss.first_time, ss.id, sp.latitude, sp.region, sp.country, sp.address_line1, sp.state, sp.id, sp.address_line2, sp.location_name, sp.postal_code, sp.discriminator, sp.port_code, sp.longitude, sp.city, sp.time_zone_id, ssp.latitude, ssp.address_line1, ssp.country, ssp.address_line2, ssp.state, ssp.longitude, ssp.region, ssp.id, ssp.port_code, ssp.location_name, ssp.city, ssp.discriminator, ssp.postal_code, ssp.time_zone_id, dp.postal_code, dp.address_line2, dp.location_name, dp.id, dp.discriminator, dp.region, dp.country, dp.state, dp.port_code, dp.address_line1, dp.latitude, dp.city, dp.longitude, dp.time_zone_id, op.longitude, op.time_zone_id, op.address_line1, op.postal_code, op.region, op.country, op.city, op.latitude, op.state, op.id, op.address_line2, op.location_name, op.discriminator, op.port_code, lp.time_zone_id, lp.port_code, lp.id, lp.address_line1, lp.longitude, lp.city, lp.latitude, lp.state, lp.postal_code, lp.country, lp.address_line2, lp.location_name, lp.discriminator, lp.region
2. 1.703 45.423 ↓ 51.3 3,747 1

Nested Loop Left Join (cost=13.28..4,661.73 rows=73 width=1,511) (actual time=14.513..45.423 rows=3,747 loops=1)

  • Output: sn.trackable_id, sn.version, sn.bill_of_lading, sn.master_leg_id, sn.deleted_at, sn.id, sn.origin_port_id, sn.master_shipment_id, sn.discriminator, sn.inactive_clock, sn.deleted_by, sn.tenant_id, sn.destination_port_id, sn.active_clock, sn.loading_port_id, sn.created_at, sn.created_by, sn.start_time, c.created_at, c.id, c.container_number, cs.line_description, cs.version, cs.site_id, cs.container_id, cs.last_free_day, cs.carrier_identifier, cs.customs_description, cs.id, cs.remote_clock, cs.last_time, cs.other_description, cs.bill_of_lading, cs.demurrage, cs.first_time, cs.names, cs.local_clock, s.name, s.created_at, s.id, s.length, s.width, s.location_id, s.site_key, s.version, s.ship_type, s.discriminator, s.mmsi, s.call_sign, sp.latitude, sp.region, sp.country, sp.address_line1, sp.state, sp.id, sp.address_line2, sp.location_name, sp.postal_code, sp.discriminator, sp.port_code, sp.longitude, sp.city, sp.time_zone_id, ss.local_clock, ss.version, ss.location_id, ss.site_id, ss.lat, ss.names, ss.eta, ss.remote_clock, ss.lon, ss.discriminator, ss.draught, ss.last_time, ss.first_time, ss.id, ssp.latitude, ssp.address_line1, ssp.country, ssp.address_line2, ssp.state, ssp.longitude, ssp.region, ssp.id, ssp.port_code, ssp.location_name, ssp.city, ssp.discriminator, ssp.postal_code, ssp.time_zone_id, dp.postal_code, dp.address_line2, dp.location_name, dp.id, dp.discriminator, dp.region, dp.country, dp.state, dp.port_code, dp.address_line1, dp.latitude, dp.city, dp.longitude, dp.time_zone_id, op.longitude, op.time_zone_id, op.address_line1, op.postal_code, op.region, op.country, op.city, op.latitude, op.state, op.id, op.address_line2, op.location_name, op.discriminator, op.port_code
3. 0.107 43.720 ↓ 51.3 3,747 1

Nested Loop Left Join (cost=12.85..4,044.33 rows=73 width=1,314) (actual time=14.512..43.720 rows=3,747 loops=1)

  • Output: sn.trackable_id, sn.version, sn.bill_of_lading, sn.master_leg_id, sn.deleted_at, sn.id, sn.origin_port_id, sn.master_shipment_id, sn.discriminator, sn.inactive_clock, sn.deleted_by, sn.tenant_id, sn.destination_port_id, sn.active_clock, sn.loading_port_id, sn.created_at, sn.created_by, sn.start_time, c.created_at, c.id, c.container_number, cs.line_description, cs.version, cs.site_id, cs.container_id, cs.last_free_day, cs.carrier_identifier, cs.customs_description, cs.id, cs.remote_clock, cs.last_time, cs.other_description, cs.bill_of_lading, cs.demurrage, cs.first_time, cs.names, cs.local_clock, s.name, s.created_at, s.id, s.length, s.width, s.location_id, s.site_key, s.version, s.ship_type, s.discriminator, s.mmsi, s.call_sign, sp.latitude, sp.region, sp.country, sp.address_line1, sp.state, sp.id, sp.address_line2, sp.location_name, sp.postal_code, sp.discriminator, sp.port_code, sp.longitude, sp.city, sp.time_zone_id, ss.local_clock, ss.version, ss.location_id, ss.site_id, ss.lat, ss.names, ss.eta, ss.remote_clock, ss.lon, ss.discriminator, ss.draught, ss.last_time, ss.first_time, ss.id, ssp.latitude, ssp.address_line1, ssp.country, ssp.address_line2, ssp.state, ssp.longitude, ssp.region, ssp.id, ssp.port_code, ssp.location_name, ssp.city, ssp.discriminator, ssp.postal_code, ssp.time_zone_id, dp.postal_code, dp.address_line2, dp.location_name, dp.id, dp.discriminator, dp.region, dp.country, dp.state, dp.port_code, dp.address_line1, dp.latitude, dp.city, dp.longitude, dp.time_zone_id
4. 0.965 39.866 ↓ 51.3 3,747 1

Nested Loop Left Join (cost=12.42..3,571.02 rows=73 width=1,117) (actual time=14.510..39.866 rows=3,747 loops=1)

  • Output: sn.trackable_id, sn.version, sn.bill_of_lading, sn.master_leg_id, sn.deleted_at, sn.id, sn.origin_port_id, sn.master_shipment_id, sn.discriminator, sn.inactive_clock, sn.deleted_by, sn.tenant_id, sn.destination_port_id, sn.active_clock, sn.loading_port_id, sn.created_at, sn.created_by, sn.start_time, c.created_at, c.id, c.container_number, cs.line_description, cs.version, cs.site_id, cs.container_id, cs.last_free_day, cs.carrier_identifier, cs.customs_description, cs.id, cs.remote_clock, cs.last_time, cs.other_description, cs.bill_of_lading, cs.demurrage, cs.first_time, cs.names, cs.local_clock, s.name, s.created_at, s.id, s.length, s.width, s.location_id, s.site_key, s.version, s.ship_type, s.discriminator, s.mmsi, s.call_sign, sp.latitude, sp.region, sp.country, sp.address_line1, sp.state, sp.id, sp.address_line2, sp.location_name, sp.postal_code, sp.discriminator, sp.port_code, sp.longitude, sp.city, sp.time_zone_id, ss.local_clock, ss.version, ss.location_id, ss.site_id, ss.lat, ss.names, ss.eta, ss.remote_clock, ss.lon, ss.discriminator, ss.draught, ss.last_time, ss.first_time, ss.id, dp.postal_code, dp.address_line2, dp.location_name, dp.id, dp.discriminator, dp.region, dp.country, dp.state, dp.port_code, dp.address_line1, dp.latitude, dp.city, dp.longitude, dp.time_zone_id
5. 0.003 20.773 ↓ 11.0 11 1

Nested Loop Left Join (cost=1.85..2,911.82 rows=1 width=960) (actual time=14.180..20.773 rows=11 loops=1)

  • Output: sn.trackable_id, sn.version, sn.bill_of_lading, sn.master_leg_id, sn.deleted_at, sn.id, sn.origin_port_id, sn.master_shipment_id, sn.discriminator, sn.inactive_clock, sn.deleted_by, sn.tenant_id, sn.destination_port_id, sn.active_clock, sn.loading_port_id, sn.created_at, sn.created_by, sn.start_time, c.created_at, c.id, c.container_number, cs.line_description, cs.version, cs.site_id, cs.container_id, cs.last_free_day, cs.carrier_identifier, cs.customs_description, cs.id, cs.remote_clock, cs.last_time, cs.other_description, cs.bill_of_lading, cs.demurrage, cs.first_time, cs.names, cs.local_clock, s.name, s.created_at, s.id, s.length, s.width, s.location_id, s.site_key, s.version, s.ship_type, s.discriminator, s.mmsi, s.call_sign, sp.latitude, sp.region, sp.country, sp.address_line1, sp.state, sp.id, sp.address_line2, sp.location_name, sp.postal_code, sp.discriminator, sp.port_code, sp.longitude, sp.city, sp.time_zone_id, dp.postal_code, dp.address_line2, dp.location_name, dp.id, dp.discriminator, dp.region, dp.country, dp.state, dp.port_code, dp.address_line1, dp.latitude, dp.city, dp.longitude, dp.time_zone_id
6. 0.007 19.714 ↓ 11.0 11 1

Nested Loop Left Join (cost=1.42..2,903.36 rows=1 width=763) (actual time=13.146..19.714 rows=11 loops=1)

  • Output: sn.trackable_id, sn.version, sn.bill_of_lading, sn.master_leg_id, sn.deleted_at, sn.id, sn.origin_port_id, sn.master_shipment_id, sn.discriminator, sn.inactive_clock, sn.deleted_by, sn.tenant_id, sn.destination_port_id, sn.active_clock, sn.loading_port_id, sn.created_at, sn.created_by, sn.start_time, c.created_at, c.id, c.container_number, cs.line_description, cs.version, cs.site_id, cs.container_id, cs.last_free_day, cs.carrier_identifier, cs.customs_description, cs.id, cs.remote_clock, cs.last_time, cs.other_description, cs.bill_of_lading, cs.demurrage, cs.first_time, cs.names, cs.local_clock, s.name, s.created_at, s.id, s.length, s.width, s.location_id, s.site_key, s.version, s.ship_type, s.discriminator, s.mmsi, s.call_sign, sp.latitude, sp.region, sp.country, sp.address_line1, sp.state, sp.id, sp.address_line2, sp.location_name, sp.postal_code, sp.discriminator, sp.port_code, sp.longitude, sp.city, sp.time_zone_id
7. 0.003 17.595 ↓ 11.0 11 1

Nested Loop Left Join (cost=0.99..2,895.19 rows=1 width=566) (actual time=12.131..17.595 rows=11 loops=1)

  • Output: sn.trackable_id, sn.version, sn.bill_of_lading, sn.master_leg_id, sn.deleted_at, sn.id, sn.origin_port_id, sn.master_shipment_id, sn.discriminator, sn.inactive_clock, sn.deleted_by, sn.tenant_id, sn.destination_port_id, sn.active_clock, sn.loading_port_id, sn.created_at, sn.created_by, sn.start_time, c.created_at, c.id, c.container_number, cs.line_description, cs.version, cs.site_id, cs.container_id, cs.last_free_day, cs.carrier_identifier, cs.customs_description, cs.id, cs.remote_clock, cs.last_time, cs.other_description, cs.bill_of_lading, cs.demurrage, cs.first_time, cs.names, cs.local_clock, s.name, s.created_at, s.id, s.length, s.width, s.location_id, s.site_key, s.version, s.ship_type, s.discriminator, s.mmsi, s.call_sign
8. 0.012 17.526 ↓ 11.0 11 1

Nested Loop Left Join (cost=0.71..2,889.04 rows=1 width=466) (actual time=12.114..17.526 rows=11 loops=1)

  • Output: sn.trackable_id, sn.version, sn.bill_of_lading, sn.master_leg_id, sn.deleted_at, sn.id, sn.origin_port_id, sn.master_shipment_id, sn.discriminator, sn.inactive_clock, sn.deleted_by, sn.tenant_id, sn.destination_port_id, sn.active_clock, sn.loading_port_id, sn.created_at, sn.created_by, sn.start_time, c.created_at, c.id, c.container_number, cs.line_description, cs.version, cs.site_id, cs.container_id, cs.last_free_day, cs.carrier_identifier, cs.customs_description, cs.id, cs.remote_clock, cs.last_time, cs.other_description, cs.bill_of_lading, cs.demurrage, cs.first_time, cs.names, cs.local_clock
9. 0.006 17.132 ↑ 1.0 1 1

Nested Loop (cost=0.29..2,880.59 rows=1 width=237) (actual time=11.739..17.132 rows=1 loops=1)

  • Output: sn.trackable_id, sn.version, sn.bill_of_lading, sn.master_leg_id, sn.deleted_at, sn.id, sn.origin_port_id, sn.master_shipment_id, sn.discriminator, sn.inactive_clock, sn.deleted_by, sn.tenant_id, sn.destination_port_id, sn.active_clock, sn.loading_port_id, sn.created_at, sn.created_by, sn.start_time, c.created_at, c.id, c.container_number
10. 16.778 16.778 ↑ 1.0 1 1

Seq Scan on public.tracking_session sn (cost=0.00..2,872.27 rows=1 width=201) (actual time=11.388..16.778 rows=1 loops=1)

  • Output: sn.id, sn.discriminator, sn.version, sn.active_clock, sn.inactive_clock, sn.master_leg_id, sn.master_shipment_id, sn.trackable_id, sn.tenant_id, sn.created_by, sn.created_at, sn.deleted_by, sn.deleted_at, sn.bill_of_lading, sn.destination_port_id, sn.loading_port_id, sn.origin_port_id, sn.start_time
  • Filter: (((sn.discriminator)::text = 'container'::text) AND (sn.master_shipment_id = '9d27113c-881d-4d8c-9cbe-75613d3f05cd'::uuid))
  • Rows Removed by Filter: 67017
11. 0.348 0.348 ↑ 1.0 1 1

Index Scan using pk_container on public.container c (cost=0.29..8.30 rows=1 width=36) (actual time=0.347..0.348 rows=1 loops=1)

  • Output: c.created_at, c.id, c.container_number
  • Index Cond: (c.id = sn.trackable_id)
12. 0.382 0.382 ↓ 11.0 11 1

Index Scan using container_status_container_id_first_time_ix on public.container_status cs (cost=0.42..8.45 rows=1 width=229) (actual time=0.367..0.382 rows=11 loops=1)

  • Output: cs.line_description, cs.version, cs.site_id, cs.container_id, cs.last_free_day, cs.carrier_identifier, cs.customs_description, cs.id, cs.remote_clock, cs.last_time, cs.other_description, cs.bill_of_lading, cs.demurrage, cs.first_time, cs.names, cs.local_clock
  • Index Cond: ((cs.container_id = c.id) AND (COALESCE(sn.start_time, sn.created_at) <= cs.first_time) AND (cs.first_time <= COALESCE((sn.deleted_at)::timestamp with time zone, now())))
13. 0.066 0.066 ↑ 1.0 1 11

Index Scan using site_pkey on public.site s (cost=0.28..6.14 rows=1 width=100) (actual time=0.006..0.006 rows=1 loops=11)

  • Output: s.name, s.created_at, s.id, s.length, s.width, s.location_id, s.site_key, s.version, s.ship_type, s.discriminator, s.mmsi, s.call_sign
  • Index Cond: (s.id = cs.site_id)
14. 2.112 2.112 ↑ 1.0 1 11

Index Scan using pk_location on public.location sp (cost=0.43..8.16 rows=1 width=197) (actual time=0.192..0.192 rows=1 loops=11)

  • Output: sp.latitude, sp.region, sp.country, sp.address_line1, sp.state, sp.id, sp.address_line2, sp.location_name, sp.postal_code, sp.discriminator, sp.port_code, sp.longitude, sp.city, sp.time_zone_id
  • Index Cond: (s.location_id = sp.id)
15. 1.056 1.056 ↑ 1.0 1 11

Index Scan using pk_location on public.location dp (cost=0.43..8.45 rows=1 width=197) (actual time=0.095..0.096 rows=1 loops=11)

  • Output: dp.postal_code, dp.address_line2, dp.location_name, dp.id, dp.discriminator, dp.region, dp.country, dp.state, dp.port_code, dp.address_line1, dp.latitude, dp.city, dp.longitude, dp.time_zone_id
  • Index Cond: (sn.destination_port_id = dp.id)
16. 9.735 18.128 ↓ 2.0 340 11

Bitmap Heap Scan on public.site_status ss (cost=10.57..657.52 rows=168 width=157) (actual time=0.803..1.648 rows=340 loops=11)

  • Output: ss.local_clock, ss.version, ss.location_id, ss.site_id, ss.lat, ss.names, ss.eta, ss.remote_clock, ss.lon, ss.discriminator, ss.draught, ss.last_time, ss.first_time, ss.id
  • Recheck Cond: ((ss.site_id = s.id) AND (COALESCE(sn.start_time, sn.created_at) <= ss.first_time) AND (ss.first_time <= COALESCE((sn.deleted_at)::timestamp with time zone, now())))
  • Heap Blocks: exact=3650
17. 8.393 8.393 ↓ 2.0 340 11

Bitmap Index Scan on site_status_site_id_first_time_ix (cost=0.00..10.53 rows=168 width=0) (actual time=0.763..0.763 rows=340 loops=11)

  • Index Cond: ((ss.site_id = s.id) AND (COALESCE(sn.start_time, sn.created_at) <= ss.first_time) AND (ss.first_time <= COALESCE((sn.deleted_at)::timestamp with time zone, now())))
18. 3.747 3.747 ↓ 0.0 0 3,747

Index Scan using pk_location on public.location ssp (cost=0.43..6.47 rows=1 width=197) (actual time=0.001..0.001 rows=0 loops=3,747)

  • Output: ssp.latitude, ssp.address_line1, ssp.country, ssp.address_line2, ssp.state, ssp.longitude, ssp.region, ssp.id, ssp.port_code, ssp.location_name, ssp.city, ssp.discriminator, ssp.postal_code, ssp.time_zone_id
  • Index Cond: (ss.location_id = ssp.id)
19. 0.000 0.000 ↓ 0.0 0 3,747

Index Scan using pk_location on public.location op (cost=0.43..8.45 rows=1 width=197) (actual time=0.000..0.000 rows=0 loops=3,747)

  • Output: op.longitude, op.time_zone_id, op.address_line1, op.postal_code, op.region, op.country, op.city, op.latitude, op.state, op.id, op.address_line2, op.location_name, op.discriminator, op.port_code
  • Index Cond: (sn.origin_port_id = op.id)
20. 0.000 0.000 ↓ 0.0 0 3,747

Index Scan using pk_location on public.location lp (cost=0.43..8.45 rows=1 width=197) (actual time=0.000..0.000 rows=0 loops=3,747)

  • Output: lp.time_zone_id, lp.port_code, lp.id, lp.address_line1, lp.longitude, lp.city, lp.latitude, lp.state, lp.postal_code, lp.country, lp.address_line2, lp.location_name, lp.discriminator, lp.region
  • Index Cond: (sn.loading_port_id = lp.id)
Planning time : 27.338 ms
Execution time : 47.832 ms