explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O02u : Optimization for: Optimization for: slow; plan #rl5b; plan #UBJI

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.002 51.285 ↑ 1.0 1 1

Limit (cost=10,773.88..10,773.88 rows=1 width=211) (actual time=51.284..51.285 rows=1 loops=1)

  • Output: ship.account_name, ship.account_number, ship.account_ucid, ship.created_date, (CASE WHEN (ship.shipment_status = ANY ('{Resolved,Cancelled,Closed,Invalid}'::text[])) THEN ship.shipment_status ELSE COALESCE(a.shipment_status, ship.shipment_status) END), ship.tracking_number, ship.carrier_accepted_expense_flag, ship.carrier, ship.customer_contact_name, ship.expiration_date, ship.ibx, ship.notes, ship.sender_id, ship.crm_shipment_id, ship.offered_packages, (count(*) OVER (?))
  • Buffers: shared hit=8596
2. 0.019 51.283 ↑ 1.0 1 1

Sort (cost=10,773.88..10,773.88 rows=1 width=211) (actual time=51.283..51.283 rows=1 loops=1)

  • Output: ship.account_name, ship.account_number, ship.account_ucid, ship.created_date, (CASE WHEN (ship.shipment_status = ANY ('{Resolved,Cancelled,Closed,Invalid}'::text[])) THEN ship.shipment_status ELSE COALESCE(a.shipment_status, ship.shipment_status) END), ship.tracking_number, ship.carrier_accepted_expense_flag, ship.carrier, ship.customer_contact_name, ship.expiration_date, ship.ibx, ship.notes, ship.sender_id, ship.crm_shipment_id, ship.offered_packages, (count(*) OVER (?))
  • Sort Key: (CASE WHEN (ship.shipment_status = ANY ('{Resolved,Cancelled,Closed,Invalid}'::text[])) THEN ship.shipment_status ELSE COALESCE(a.shipment_status, ship.shipment_status) END) DESC, ship.expiration_date
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=8596
3. 0.017 51.264 ↑ 1.0 1 1

WindowAgg (cost=10,765.03..10,773.87 rows=1 width=211) (actual time=51.263..51.264 rows=1 loops=1)

  • Output: ship.account_name, ship.account_number, ship.account_ucid, ship.created_date, CASE WHEN (ship.shipment_status = ANY ('{Resolved,Cancelled,Closed,Invalid}'::text[])) THEN ship.shipment_status ELSE COALESCE(a.shipment_status, ship.shipment_status) END, ship.tracking_number, ship.carrier_accepted_expense_flag, ship.carrier, ship.customer_contact_name, ship.expiration_date, ship.ibx, ship.notes, ship.sender_id, ship.crm_shipment_id, ship.offered_packages, count(*) OVER (?)
  • Buffers: shared hit=8596
4. 0.002 51.247 ↑ 1.0 1 1

Nested Loop Left Join (cost=10,765.03..10,773.85 rows=1 width=192) (actual time=51.247..51.247 rows=1 loops=1)

  • Output: ship.account_name, ship.account_number, ship.account_ucid, ship.created_date, ship.shipment_status, a.shipment_status, ship.tracking_number, ship.carrier_accepted_expense_flag, ship.carrier, ship.customer_contact_name, ship.expiration_date, ship.ibx, ship.notes, ship.sender_id, ship.crm_shipment_id, ship.offered_packages
  • Buffers: shared hit=8596
5. 0.004 51.208 ↑ 1.0 1 1

Nested Loop (cost=10,759.33..10,768.05 rows=1 width=180) (actual time=51.208..51.208 rows=1 loops=1)

  • Output: ship.account_name, ship.account_number, ship.account_ucid, ship.created_date, ship.shipment_status, ship.tracking_number, ship.carrier_accepted_expense_flag, ship.carrier, ship.customer_contact_name, ship.expiration_date, ship.ibx, ship.notes, ship.sender_id, ship.crm_shipment_id, ship.offered_packages
  • Inner Unique: true
  • Buffers: shared hit=8593
6. 0.000 51.178 ↑ 2.0 1 1

Unique (cost=10,758.91..10,758.92 rows=2 width=37) (actual time=51.178..51.178 rows=1 loops=1)

  • Output: ship_1.shipment_id
  • Buffers: shared hit=8589
7. 0.015 51.178 ↑ 2.0 1 1

Sort (cost=10,758.91..10,758.92 rows=2 width=37) (actual time=51.178..51.178 rows=1 loops=1)

  • Output: ship_1.shipment_id
  • Sort Key: ship_1.shipment_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=8589
8. 0.002 51.163 ↑ 2.0 1 1

Append (cost=5.70..10,758.90 rows=2 width=37) (actual time=40.364..51.163 rows=1 loops=1)

  • Buffers: shared hit=8589
9. 0.001 23.872 ↓ 0.0 0 1

Nested Loop Left Join (cost=5.70..5,379.44 rows=1 width=37) (actual time=23.872..23.872 rows=0 loops=1)

  • Output: ship_1.shipment_id
  • Filter: (CASE WHEN (ship_1.shipment_status = ANY ('{Resolved,Cancelled,Closed,Invalid}'::text[])) THEN ship_1.shipment_status ELSE COALESCE(a_1.shipment_status, ship_1.shipment_status) END = ANY ('{Resolved,"Resolved – Shipment Returned"}'::text[]))
  • Buffers: shared hit=3612
10. 23.871 23.871 ↓ 0.0 0 1

Seq Scan on terminal.shipment ship_1 (cost=0.00..5,356.20 rows=4 width=56) (actual time=23.871..23.871 rows=0 loops=1)

  • Output: ship_1.shipment_id, ship_1.crm_shipment_id, ship_1.shipment_status, ship_1.severity, ship_1.tracking_number, ship_1.offered_packages, ship_1.carrier, ship_1.carrier_accepted_expense_flag, ship_1.expiration_date, ship_1.owned_by, ship_1.sender_id, ship_1.notes, ship_1.account_name, ship_1.account_number, ship_1.account_ucid, ship_1.customer_contact_name, ship_1.service_order_number, ship_1.activity_number, ship_1.ibx, ship_1.region, ship_1.created_by, ship_1.created_date, ship_1.last_updated_by, ship_1.last_updated_date, ship_1.deleted_flag, ship_1.ignore_updates_flag, ship_1.etl_created_by, ship_1.etl_created_date, ship_1.etl_data_source, ship_1.etl_last_updated_by, ship_1.etl_last_updated_date, ship_1.row_modification_counter
  • Filter: ((ship_1.account_ucid = '5E755D15-F8DE-4cd9-9722-DD1A86062C38'::text) AND ((ship_1.created_date)::date >= '2019-07-09'::date) AND ((ship_1.created_date)::date <= '2019-08-08'::date))
  • Rows Removed by Filter: 77520
  • Buffers: shared hit=3612
11. 0.000 0.000 ↓ 0.0 0

Subquery Scan on a_1 (cost=5.70..5.79 rows=1 width=23) (never executed)

  • Output: a_1.crm_shipment_id, a_1.shipment_status, a_1.created_date, a_1.rnk, shipment_response.response_id
  • Filter: ((a_1.rnk = 1) AND (ship_1.crm_shipment_id = a_1.crm_shipment_id))
12. 0.000 0.000 ↓ 0.0 0

Sort (cost=5.70..5.72 rows=5 width=76) (never executed)

  • Output: shipment_response.crm_shipment_id, shipment_response.shipment_status, shipment_response.created_date, (rank() OVER (?)), shipment_response.response_id
  • Sort Key: (rank() OVER (?))
13. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=5.53..5.65 rows=5 width=76) (never executed)

  • Output: shipment_response.crm_shipment_id, shipment_response.shipment_status, shipment_response.created_date, rank() OVER (?), shipment_response.response_id
14. 0.000 0.000 ↓ 0.0 0

Sort (cost=5.53..5.55 rows=5 width=68) (never executed)

  • Output: shipment_response.crm_shipment_id, shipment_response.created_date, shipment_response.response_id, shipment_response.shipment_status
  • Sort Key: shipment_response.created_date DESC, shipment_response.response_id DESC
15. 0.000 0.000 ↓ 0.0 0

Index Scan using shipment_response_crm_shipment_id_idx on terminal.shipment_response (cost=0.41..5.48 rows=5 width=68) (never executed)

  • Output: shipment_response.crm_shipment_id, shipment_response.created_date, shipment_response.response_id, shipment_response.shipment_status
  • Index Cond: (shipment_response.crm_shipment_id = ship_1.crm_shipment_id)
16. 0.230 27.289 ↑ 1.0 1 1

Nested Loop Left Join (cost=5.70..5,379.45 rows=1 width=37) (actual time=16.490..27.289 rows=1 loops=1)

  • Output: ship_2.shipment_id
  • Filter: (CASE WHEN (ship_2.shipment_status = ANY ('{Resolved,Cancelled,Closed,Invalid}'::text[])) THEN ship_2.shipment_status ELSE COALESCE(a_2.shipment_status, ship_2.shipment_status) END = ANY ('{Invalid,Unresolved,Waiting,In-Progress}'::text[]))
  • Rows Removed by Filter: 408
  • Buffers: shared hit=4977
17. 23.378 23.378 ↓ 102.2 409 1

Seq Scan on terminal.shipment ship_2 (cost=0.00..5,356.20 rows=4 width=56) (actual time=11.864..23.378 rows=409 loops=1)

  • Output: ship_2.shipment_id, ship_2.crm_shipment_id, ship_2.shipment_status, ship_2.severity, ship_2.tracking_number, ship_2.offered_packages, ship_2.carrier, ship_2.carrier_accepted_expense_flag, ship_2.expiration_date, ship_2.owned_by, ship_2.sender_id, ship_2.notes, ship_2.account_name, ship_2.account_number, ship_2.account_ucid, ship_2.customer_contact_name, ship_2.service_order_number, ship_2.activity_number, ship_2.ibx, ship_2.region, ship_2.created_by, ship_2.created_date, ship_2.last_updated_by, ship_2.last_updated_date, ship_2.deleted_flag, ship_2.ignore_updates_flag, ship_2.etl_created_by, ship_2.etl_created_date, ship_2.etl_data_source, ship_2.etl_last_updated_by, ship_2.etl_last_updated_date, ship_2.row_modification_counter
  • Filter: ((ship_2.account_ucid = '5E755D15-F8DE-4cd9-9722-DD1A86062C38'::text) AND ((ship_2.created_date)::date >= '2018-08-08'::date) AND ((ship_2.created_date)::date <= '2019-08-08'::date))
  • Rows Removed by Filter: 77111
  • Buffers: shared hit=3612
18. 0.000 3.681 ↓ 0.0 0 409

Subquery Scan on a_2 (cost=5.70..5.79 rows=1 width=23) (actual time=0.009..0.009 rows=0 loops=409)

  • Output: a_2.crm_shipment_id, a_2.shipment_status, a_2.created_date, a_2.rnk, shipment_response_1.response_id
  • Filter: ((a_2.rnk = 1) AND (ship_2.crm_shipment_id = a_2.crm_shipment_id))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1365
19. 0.409 3.681 ↓ 0.0 0 409

Sort (cost=5.70..5.72 rows=5 width=76) (actual time=0.009..0.009 rows=0 loops=409)

  • Output: shipment_response_1.crm_shipment_id, shipment_response_1.shipment_status, shipment_response_1.created_date, (rank() OVER (?)), shipment_response_1.response_id
  • Sort Key: (rank() OVER (?))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1365
20. 0.409 3.272 ↓ 0.0 0 409

WindowAgg (cost=5.53..5.65 rows=5 width=76) (actual time=0.008..0.008 rows=0 loops=409)

  • Output: shipment_response_1.crm_shipment_id, shipment_response_1.shipment_status, shipment_response_1.created_date, rank() OVER (?), shipment_response_1.response_id
  • Buffers: shared hit=1365
21. 0.409 2.863 ↓ 0.0 0 409

Sort (cost=5.53..5.55 rows=5 width=68) (actual time=0.007..0.007 rows=0 loops=409)

  • Output: shipment_response_1.crm_shipment_id, shipment_response_1.created_date, shipment_response_1.response_id, shipment_response_1.shipment_status
  • Sort Key: shipment_response_1.created_date DESC, shipment_response_1.response_id DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1365
22. 2.454 2.454 ↓ 0.0 0 409

Index Scan using shipment_response_crm_shipment_id_idx on terminal.shipment_response shipment_response_1 (cost=0.41..5.48 rows=5 width=68) (actual time=0.006..0.006 rows=0 loops=409)

  • Output: shipment_response_1.crm_shipment_id, shipment_response_1.created_date, shipment_response_1.response_id, shipment_response_1.shipment_status
  • Index Cond: (shipment_response_1.crm_shipment_id = ship_2.crm_shipment_id)
  • Buffers: shared hit=1365
23. 0.026 0.026 ↑ 1.0 1 1

Index Scan using shipment_pk on terminal.shipment ship (cost=0.42..4.44 rows=1 width=217) (actual time=0.026..0.026 rows=1 loops=1)

  • Output: ship.shipment_id, ship.crm_shipment_id, ship.shipment_status, ship.severity, ship.tracking_number, ship.offered_packages, ship.carrier, ship.carrier_accepted_expense_flag, ship.expiration_date, ship.owned_by, ship.sender_id, ship.notes, ship.account_name, ship.account_number, ship.account_ucid, ship.customer_contact_name, ship.service_order_number, ship.activity_number, ship.ibx, ship.region, ship.created_by, ship.created_date, ship.last_updated_by, ship.last_updated_date, ship.deleted_flag, ship.ignore_updates_flag, ship.etl_created_by, ship.etl_created_date, ship.etl_data_source, ship.etl_last_updated_by, ship.etl_last_updated_date, ship.row_modification_counter
  • Index Cond: (ship.shipment_id = ship_1.shipment_id)
  • Filter: ((ship.account_ucid = '5E755D15-F8DE-4cd9-9722-DD1A86062C38'::text) AND (ship.ibx = 'LD5'::text))
  • Buffers: shared hit=4
24. 0.001 0.037 ↓ 0.0 0 1

Subquery Scan on a (cost=5.70..5.79 rows=1 width=23) (actual time=0.037..0.037 rows=0 loops=1)

  • Output: a.crm_shipment_id, a.shipment_status, a.created_date, a.rnk, shipment_response_2.response_id
  • Filter: ((a.rnk = 1) AND (ship.crm_shipment_id = a.crm_shipment_id))
  • Buffers: shared hit=3
25. 0.007 0.036 ↓ 0.0 0 1

Sort (cost=5.70..5.72 rows=5 width=76) (actual time=0.036..0.036 rows=0 loops=1)

  • Output: shipment_response_2.crm_shipment_id, shipment_response_2.shipment_status, shipment_response_2.created_date, (rank() OVER (?)), shipment_response_2.response_id
  • Sort Key: (rank() OVER (?))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3
26. 0.001 0.029 ↓ 0.0 0 1

WindowAgg (cost=5.53..5.65 rows=5 width=76) (actual time=0.029..0.029 rows=0 loops=1)

  • Output: shipment_response_2.crm_shipment_id, shipment_response_2.shipment_status, shipment_response_2.created_date, rank() OVER (?), shipment_response_2.response_id
  • Buffers: shared hit=3
27. 0.014 0.028 ↓ 0.0 0 1

Sort (cost=5.53..5.55 rows=5 width=68) (actual time=0.028..0.028 rows=0 loops=1)

  • Output: shipment_response_2.crm_shipment_id, shipment_response_2.created_date, shipment_response_2.response_id, shipment_response_2.shipment_status
  • Sort Key: shipment_response_2.created_date DESC, shipment_response_2.response_id DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3
28. 0.014 0.014 ↓ 0.0 0 1

Index Scan using shipment_response_crm_shipment_id_idx on terminal.shipment_response shipment_response_2 (cost=0.41..5.48 rows=5 width=68) (actual time=0.014..0.014 rows=0 loops=1)

  • Output: shipment_response_2.crm_shipment_id, shipment_response_2.created_date, shipment_response_2.response_id, shipment_response_2.shipment_status
  • Index Cond: (shipment_response_2.crm_shipment_id = ship.crm_shipment_id)
  • Buffers: shared hit=3
Planning time : 1.394 ms
Execution time : 51.517 ms