explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.003 50.965 ↓ 14.0 14 1

Limit (cost=10,742.79..10,742.80 rows=1 width=211) (actual time=50.963..50.965 rows=14 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=7882
2. 0.026 50.962 ↓ 14.0 14 1

Sort (cost=10,742.79..10,742.80 rows=1 width=211) (actual time=50.961..50.962 rows=14 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: 28kB
  • Buffers: shared hit=7882
3. 0.032 50.936 ↓ 14.0 14 1

WindowAgg (cost=10,730.17..10,742.78 rows=1 width=211) (actual time=50.928..50.936 rows=14 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=7882
4. 0.001 50.904 ↓ 14.0 14 1

Nested Loop Left Join (cost=10,730.17..10,742.76 rows=1 width=192) (actual time=50.541..50.904 rows=14 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=7882
5. 0.017 50.609 ↓ 14.0 14 1

Nested Loop (cost=10,724.47..10,736.96 rows=1 width=180) (actual time=50.502..50.609 rows=14 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=7827
6. 0.004 50.480 ↓ 7.0 14 1

Unique (cost=10,724.05..10,724.06 rows=2 width=37) (actual time=50.476..50.480 rows=14 loops=1)

  • Output: ship_1.shipment_id
  • Buffers: shared hit=7771
7. 0.025 50.476 ↓ 7.0 14 1

Sort (cost=10,724.05..10,724.06 rows=2 width=37) (actual time=50.475..50.476 rows=14 loops=1)

  • Output: ship_1.shipment_id
  • Sort Key: ship_1.shipment_id
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=7771
8. 0.006 50.451 ↓ 7.0 14 1

Append (cost=5.70..10,724.04 rows=2 width=37) (actual time=12.305..50.451 rows=14 loops=1)

  • Buffers: shared hit=7771
9. 0.024 24.753 ↓ 11.0 11 1

Nested Loop Left Join (cost=5.70..5,362.01 rows=1 width=37) (actual time=12.305..24.753 rows=11 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[]))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=3667
10. 24.267 24.267 ↓ 14.0 14 1

Seq Scan on terminal.shipment ship_1 (cost=0.00..5,356.20 rows=1 width=56) (actual time=11.754..24.267 rows=14 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 = 'A296E975-0C86-4f24-AC3A-D80D746628A7'::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: 77506
  • Buffers: shared hit=3612
11. 0.028 0.462 ↑ 1.0 1 14

Subquery Scan on a_1 (cost=5.70..5.79 rows=1 width=23) (actual time=0.032..0.033 rows=1 loops=14)

  • 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))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=55
12. 0.084 0.434 ↑ 1.2 4 14

Sort (cost=5.70..5.72 rows=5 width=76) (actual time=0.031..0.031 rows=4 loops=14)

  • Output: shipment_response.crm_shipment_id, shipment_response.shipment_status, shipment_response.created_date, (rank() OVER (?)), shipment_response.response_id
  • Sort Key: (rank() OVER (?))
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=55
13. 0.056 0.350 ↑ 1.2 4 14

WindowAgg (cost=5.53..5.65 rows=5 width=76) (actual time=0.023..0.025 rows=4 loops=14)

  • Output: shipment_response.crm_shipment_id, shipment_response.shipment_status, shipment_response.created_date, rank() OVER (?), shipment_response.response_id
  • Buffers: shared hit=55
14. 0.112 0.294 ↑ 1.2 4 14

Sort (cost=5.53..5.55 rows=5 width=68) (actual time=0.020..0.021 rows=4 loops=14)

  • 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
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=55
15. 0.182 0.182 ↑ 1.2 4 14

Index Scan using shipment_response_crm_shipment_id_idx on terminal.shipment_response (cost=0.41..5.48 rows=5 width=68) (actual time=0.012..0.013 rows=4 loops=14)

  • 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)
  • Buffers: shared hit=55
16. 0.027 25.692 ↓ 3.0 3 1

Nested Loop Left Join (cost=5.70..5,362.01 rows=1 width=37) (actual time=11.339..25.692 rows=3 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: 112
  • Buffers: shared hit=4104
17. 23.595 23.595 ↓ 115.0 115 1

Seq Scan on terminal.shipment ship_2 (cost=0.00..5,356.20 rows=1 width=56) (actual time=11.283..23.595 rows=115 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 = 'A296E975-0C86-4f24-AC3A-D80D746628A7'::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: 77405
  • Buffers: shared hit=3612
18. 0.115 2.070 ↑ 1.0 1 115

Subquery Scan on a_2 (cost=5.70..5.79 rows=1 width=23) (actual time=0.017..0.018 rows=1 loops=115)

  • 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: 3
  • Buffers: shared hit=492
19. 0.230 1.955 ↑ 1.2 4 115

Sort (cost=5.70..5.72 rows=5 width=76) (actual time=0.017..0.017 rows=4 loops=115)

  • 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=492
20. 0.345 1.725 ↑ 1.2 4 115

WindowAgg (cost=5.53..5.65 rows=5 width=76) (actual time=0.013..0.015 rows=4 loops=115)

  • 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=492
21. 0.345 1.380 ↑ 1.2 4 115

Sort (cost=5.53..5.55 rows=5 width=68) (actual time=0.012..0.012 rows=4 loops=115)

  • 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=492
22. 1.035 1.035 ↑ 1.2 4 115

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.008..0.009 rows=4 loops=115)

  • 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=492
23. 0.112 0.112 ↑ 1.0 1 14

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

  • 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 = 'A296E975-0C86-4f24-AC3A-D80D746628A7'::text) AND (ship.ibx = 'SV16'::text))
  • Buffers: shared hit=56
24. 0.014 0.294 ↑ 1.0 1 14

Subquery Scan on a (cost=5.70..5.79 rows=1 width=23) (actual time=0.020..0.021 rows=1 loops=14)

  • 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))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=55
25. 0.042 0.280 ↑ 1.2 4 14

Sort (cost=5.70..5.72 rows=5 width=76) (actual time=0.020..0.020 rows=4 loops=14)

  • 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=55
26. 0.042 0.238 ↑ 1.2 4 14

WindowAgg (cost=5.53..5.65 rows=5 width=76) (actual time=0.015..0.017 rows=4 loops=14)

  • 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=55
27. 0.070 0.196 ↑ 1.2 4 14

Sort (cost=5.53..5.55 rows=5 width=68) (actual time=0.014..0.014 rows=4 loops=14)

  • 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=55
28. 0.126 0.126 ↑ 1.2 4 14

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.008..0.009 rows=4 loops=14)

  • 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=55
Planning time : 1.367 ms
Execution time : 51.190 ms