explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rl5b : slow

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.003 188,121.977 ↓ 14.0 14 1

Limit (cost=55,277.84..55,277.84 rows=1 width=211) (actual time=188,121.975..188,121.977 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=1105978
2. 0.042 188,121.974 ↓ 14.0 14 1

Sort (cost=55,277.84..55,277.84 rows=1 width=211) (actual time=188,121.973..188,121.974 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=1105978
3. 0.124 188,121.932 ↓ 14.0 14 1

WindowAgg (cost=27,684.80..55,277.83 rows=1 width=211) (actual time=188,121.921..188,121.932 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=1105978
4. 3.254 188,121.808 ↓ 14.0 14 1

Nested Loop Semi Join (cost=27,684.80..55,277.81 rows=1 width=192) (actual time=47,127.314..188,121.808 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
  • Join Filter: (ship.shipment_id = ship_1.shipment_id)
  • Rows Removed by Join Filter: 2135
  • Buffers: shared hit=1105978
5. 166.157 1,827.034 ↓ 160.0 160 1

Nested Loop Left Join (cost=13,844.18..15,036.86 rows=1 width=229) (actual time=287.673..1,827.034 rows=160 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, ship.shipment_id, a.shipment_status
  • Join Filter: (ship.crm_shipment_id = a.crm_shipment_id)
  • Rows Removed by Join Filter: 2161353
  • Buffers: shared hit=2333
6. 1.166 1.197 ↓ 160.0 160 1

Bitmap Heap Scan on terminal.shipment ship (cost=3.57..275.29 rows=1 width=217) (actual time=0.064..1.197 rows=160 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
  • Recheck Cond: (ship.ibx = 'SV16'::text)
  • Filter: (ship.account_ucid = 'A296E975-0C86-4f24-AC3A-D80D746628A7'::text)
  • Rows Removed by Filter: 53
  • Heap Blocks: exact=197
  • Buffers: shared hit=201
7. 0.031 0.031 ↓ 1.4 214 1

Bitmap Index Scan on shipment_ibx_idx (cost=0.00..3.56 rows=153 width=0) (actual time=0.031..0.031 rows=214 loops=1)

  • Index Cond: (ship.ibx = 'SV16'::text)
  • Buffers: shared hit=4
8. 900.000 1,659.680 ↓ 44.1 13,509 160

Subquery Scan on a (cost=13,840.62..14,757.75 rows=306 width=23) (actual time=1.738..10.373 rows=13,509 loops=160)

  • Output: a.crm_shipment_id, a.shipment_status, a.created_date, a.rnk, shipment_response.response_id
  • Filter: (a.rnk = 1)
  • Rows Removed by Filter: 47560
  • Buffers: shared hit=2132
9. 506.240 759.680 ↑ 1.0 61,069 160

Sort (cost=13,840.62..13,993.47 rows=61,142 width=76) (actual time=1.738..4.748 rows=61,069 loops=160)

  • Output: shipment_response.crm_shipment_id, shipment_response.shipment_status, shipment_response.created_date, (rank() OVER (?)), shipment_response.response_id
  • Sort Key: shipment_response.crm_shipment_id, (rank() OVER (?))
  • Sort Method: quicksort Memory: 10124kB
  • Buffers: shared hit=2132
10. 35.784 253.440 ↑ 1.0 61,069 1

WindowAgg (cost=7,604.17..8,979.87 rows=61,142 width=76) (actual time=212.613..253.440 rows=61,069 loops=1)

  • Output: shipment_response.crm_shipment_id, shipment_response.shipment_status, shipment_response.created_date, rank() OVER (?), shipment_response.response_id
  • Buffers: shared hit=2132
11. 203.396 217.656 ↑ 1.0 61,069 1

Sort (cost=7,604.17..7,757.03 rows=61,142 width=68) (actual time=212.603..217.656 rows=61,069 loops=1)

  • Output: shipment_response.crm_shipment_id, shipment_response.created_date, shipment_response.response_id, shipment_response.shipment_status
  • Sort Key: shipment_response.crm_shipment_id, shipment_response.created_date DESC, shipment_response.response_id DESC
  • Sort Method: quicksort Memory: 10124kB
  • Buffers: shared hit=2132
12. 14.260 14.260 ↑ 1.0 61,069 1

Seq Scan on terminal.shipment_response (cost=0.00..2,743.42 rows=61,142 width=68) (actual time=0.004..14.260 rows=61,069 loops=1)

  • Output: shipment_response.crm_shipment_id, shipment_response.created_date, shipment_response.response_id, shipment_response.shipment_status
  • Buffers: shared hit=2132
13. 1.189 186,291.520 ↓ 6.5 13 160

Append (cost=13,840.62..40,240.92 rows=2 width=37) (actual time=32.514..1,164.322 rows=13 loops=160)

  • Buffers: shared hit=1103645
14. 2,218.698 24,619.520 ↓ 11.0 11 160

Nested Loop Left Join (cost=13,840.62..20,120.07 rows=1 width=37) (actual time=32.513..153.872 rows=11 loops=160)

  • Output: ship_1.shipment_id
  • Join Filter: (ship_1.crm_shipment_id = a_1.crm_shipment_id)
  • Rows Removed by Join Filter: 183825
  • 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=567838
15. 3,802.880 3,802.880 ↓ 14.0 14 160

Seq Scan on terminal.shipment ship_1 (cost=0.00..5,356.20 rows=1 width=56) (actual time=11.665..23.768 rows=14 loops=160)

  • 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: 75878
  • Buffers: shared hit=565706
16. 12,137.994 18,597.942 ↓ 44.1 13,505 2,178

Subquery Scan on a_1 (cost=13,840.62..14,757.75 rows=306 width=23) (actual time=0.125..8.539 rows=13,505 loops=2,178)

  • Output: a_1.crm_shipment_id, a_1.shipment_status, a_1.created_date, a_1.rnk, shipment_response_1.response_id
  • Filter: (a_1.rnk = 1)
  • Rows Removed by Filter: 47543
  • Buffers: shared hit=2132
17. 6,212.448 6,459.948 ↑ 1.0 61,048 2,178

Sort (cost=13,840.62..13,993.47 rows=61,142 width=76) (actual time=0.125..2.966 rows=61,048 loops=2,178)

  • 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: shipment_response_1.crm_shipment_id, (rank() OVER (?))
  • Sort Method: quicksort Memory: 10124kB
  • Buffers: shared hit=2132
18. 33.305 247.500 ↑ 1.0 61,069 1

WindowAgg (cost=7,604.17..8,979.87 rows=61,142 width=76) (actual time=208.610..247.500 rows=61,069 loops=1)

  • 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=2132
19. 200.264 214.195 ↑ 1.0 61,069 1

Sort (cost=7,604.17..7,757.03 rows=61,142 width=68) (actual time=208.600..214.195 rows=61,069 loops=1)

  • 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.crm_shipment_id, shipment_response_1.created_date DESC, shipment_response_1.response_id DESC
  • Sort Method: quicksort Memory: 10124kB
  • Buffers: shared hit=2132
20. 13.931 13.931 ↑ 1.0 61,069 1

Seq Scan on terminal.shipment_response shipment_response_1 (cost=0.00..2,743.42 rows=61,142 width=68) (actual time=0.008..13.931 rows=61,069 loops=1)

  • Output: shipment_response_1.crm_shipment_id, shipment_response_1.created_date, shipment_response_1.response_id, shipment_response_1.shipment_status
  • Buffers: shared hit=2132
21. 17,015.018 161,670.811 ↓ 3.0 3 149

Nested Loop Left Join (cost=13,840.62..20,120.83 rows=1 width=37) (actual time=23.012..1,085.039 rows=3 loops=149)

  • Output: ship_2.shipment_id
  • Join Filter: (ship_2.crm_shipment_id = a_2.crm_shipment_id)
  • Rows Removed by Join Filter: 1526069
  • 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: 110
  • Buffers: shared hit=535807
22. 3,729.917 3,729.917 ↓ 113.0 113 149

Seq Scan on terminal.shipment ship_2 (cost=0.00..5,356.20 rows=1 width=56) (actual time=11.477..25.033 rows=113 loops=149)

  • 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: 76763
  • Buffers: shared hit=533675
23. 92,631.999 140,925.876 ↓ 44.1 13,509 16,833

Subquery Scan on a_2 (cost=13,840.62..14,757.75 rows=306 width=23) (actual time=0.017..8.372 rows=13,509 loops=16,833)

  • Output: a_2.crm_shipment_id, a_2.shipment_status, a_2.created_date, a_2.rnk, shipment_response_2.response_id
  • Filter: (a_2.rnk = 1)
  • Rows Removed by Filter: 47560
  • Buffers: shared hit=2132
24. 48,032.275 48,293.877 ↑ 1.0 61,069 16,833

Sort (cost=13,840.62..13,993.47 rows=61,142 width=76) (actual time=0.017..2.869 rows=61,069 loops=16,833)

  • 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: shipment_response_2.crm_shipment_id, (rank() OVER (?))
  • Sort Method: quicksort Memory: 10124kB
  • Buffers: shared hit=2132
25. 33.205 261.602 ↑ 1.0 61,069 1

WindowAgg (cost=7,604.17..8,979.87 rows=61,142 width=76) (actual time=222.692..261.602 rows=61,069 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=2132
26. 214.247 228.397 ↑ 1.0 61,069 1

Sort (cost=7,604.17..7,757.03 rows=61,142 width=68) (actual time=222.681..228.397 rows=61,069 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.crm_shipment_id, shipment_response_2.created_date DESC, shipment_response_2.response_id DESC
  • Sort Method: quicksort Memory: 10124kB
  • Buffers: shared hit=2132
27. 14.150 14.150 ↑ 1.0 61,069 1

Seq Scan on terminal.shipment_response shipment_response_2 (cost=0.00..2,743.42 rows=61,142 width=68) (actual time=0.008..14.150 rows=61,069 loops=1)

  • Output: shipment_response_2.crm_shipment_id, shipment_response_2.created_date, shipment_response_2.response_id, shipment_response_2.shipment_status
  • Buffers: shared hit=2132
Planning time : 1.313 ms
Execution time : 188,124.076 ms