explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wE3R : faster

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 2,031.225 ↓ 14.0 14 1

Limit (cost=44,850.49..44,850.50 rows=1 width=211) (actual time=2,031.223..2,031.225 rows=14 loops=1)

  • Output: ship.account_name, ship.account_number, ship.account_ucid, ship.created_date, (CASE WHEN ((ship.shipment_status = ANY ('{Cancelled,Closed,Invalid}'::text[])) OR (ship.shipment_status ~~ 'Resolved%'::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=6854
2. 0.028 2,031.222 ↓ 14.0 14 1

Sort (cost=44,850.49..44,850.50 rows=1 width=211) (actual time=2,031.221..2,031.222 rows=14 loops=1)

  • Output: ship.account_name, ship.account_number, ship.account_ucid, ship.created_date, (CASE WHEN ((ship.shipment_status = ANY ('{Cancelled,Closed,Invalid}'::text[])) OR (ship.shipment_status ~~ 'Resolved%'::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 ('{Cancelled,Closed,Invalid}'::text[])) OR (ship.shipment_status ~~ 'Resolved%'::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=6854
3. 0.033 2,031.194 ↓ 14.0 14 1

WindowAgg (cost=43,932.55..44,850.48 rows=1 width=211) (actual time=2,031.184..2,031.194 rows=14 loops=1)

  • Output: ship.account_name, ship.account_number, ship.account_ucid, ship.created_date, CASE WHEN ((ship.shipment_status = ANY ('{Cancelled,Closed,Invalid}'::text[])) OR (ship.shipment_status ~~ 'Resolved%'::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=6854
4. 3.311 2,031.161 ↓ 14.0 14 1

Merge Right Join (cost=43,932.55..44,850.46 rows=1 width=192) (actual time=2,029.741..2,031.161 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
  • Merge Cond: (a.crm_shipment_id = ship.crm_shipment_id)
  • Buffers: shared hit=6854
5. 5.663 279.878 ↓ 44.0 13,454 1

Subquery Scan on a (cost=13,840.62..14,757.75 rows=306 width=23) (actual time=271.105..279.878 rows=13,454 loops=1)

  • 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: 47319
  • Buffers: shared hit=2132
6. 25.371 274.215 ↑ 1.0 60,773 1

Sort (cost=13,840.62..13,993.47 rows=61,142 width=76) (actual time=271.103..274.215 rows=60,773 loops=1)

  • 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
7. 32.252 248.844 ↑ 1.0 61,069 1

WindowAgg (cost=7,604.17..8,979.87 rows=61,142 width=76) (actual time=211.406..248.844 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
8. 200.633 216.592 ↑ 1.0 61,069 1

Sort (cost=7,604.17..7,757.03 rows=61,142 width=68) (actual time=211.396..216.592 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
9. 15.959 15.959 ↑ 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.011..15.959 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
10. 0.046 1,747.972 ↓ 14.0 14 1

Sort (cost=30,091.93..30,091.94 rows=1 width=180) (actual time=1,747.970..1,747.972 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
  • Sort Key: ship.crm_shipment_id
  • Sort Method: quicksort Memory: 28kB
  • Buffers: shared hit=4722
11. 0.019 1,747.926 ↓ 14.0 14 1

Nested Loop (cost=30,083.36..30,091.92 rows=1 width=180) (actual time=1,747.810..1,747.926 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=4722
12. 0.004 1,747.781 ↓ 7.0 14 1

Unique (cost=30,082.94..30,082.95 rows=2 width=37) (actual time=1,747.777..1,747.781 rows=14 loops=1)

  • Output: ship_1.shipment_id
  • Buffers: shared hit=4666
13. 0.050 1,747.777 ↓ 7.0 14 1

Sort (cost=30,082.94..30,082.94 rows=2 width=37) (actual time=1,747.776..1,747.777 rows=14 loops=1)

  • Output: ship_1.shipment_id
  • Sort Key: ship_1.shipment_id
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=4666
14. 0.007 1,747.727 ↓ 7.0 14 1

Append (cost=13,844.18..30,082.93 rows=2 width=37) (actual time=286.227..1,747.727 rows=14 loops=1)

  • Buffers: shared hit=4666
15. 14.101 397.877 ↓ 11.0 11 1

Nested Loop Left Join (cost=13,844.18..15,041.07 rows=1 width=37) (actual time=286.226..397.877 rows=11 loops=1)

  • Output: ship_1.shipment_id
  • Join Filter: (ship_1.crm_shipment_id = a_1.crm_shipment_id)
  • Rows Removed by Join Filter: 189116
  • Filter: (CASE WHEN ((ship_1.shipment_status = ANY ('{Cancelled,Closed,Invalid}'::text[])) OR (ship_1.shipment_status ~~ 'Resolved%'::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=2333
16. 0.544 0.582 ↓ 14.0 14 1

Bitmap Heap Scan on terminal.shipment ship_1 (cost=3.57..276.82 rows=1 width=56) (actual time=0.175..0.582 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
  • Recheck Cond: (ship_1.ibx = 'SV16'::text)
  • 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: 199
  • Heap Blocks: exact=197
  • Buffers: shared hit=201
17. 0.038 0.038 ↓ 1.4 214 1

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

  • Index Cond: (ship_1.ibx = 'SV16'::text)
  • Buffers: shared hit=4
18. 76.230 383.194 ↓ 44.1 13,509 14

Subquery Scan on a_1 (cost=13,840.62..14,757.75 rows=306 width=23) (actual time=19.136..27.371 rows=13,509 loops=14)

  • 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: 47560
  • Buffers: shared hit=2132
19. 61.524 306.964 ↑ 1.0 61,069 14

Sort (cost=13,840.62..13,993.47 rows=61,142 width=76) (actual time=19.136..21.926 rows=61,069 loops=14)

  • 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
20. 32.935 245.440 ↑ 1.0 61,069 1

WindowAgg (cost=7,604.17..8,979.87 rows=61,142 width=76) (actual time=207.270..245.440 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
21. 198.684 212.505 ↑ 1.0 61,069 1

Sort (cost=7,604.17..7,757.03 rows=61,142 width=68) (actual time=207.260..212.505 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
22. 13.821 13.821 ↑ 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.009..13.821 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
23. 117.545 1,349.843 ↓ 3.0 3 1

Nested Loop Left Join (cost=13,844.18..15,041.84 rows=1 width=37) (actual time=274.473..1,349.843 rows=3 loops=1)

  • Output: ship_2.shipment_id
  • Join Filter: (ship_2.crm_shipment_id = a_2.crm_shipment_id)
  • Rows Removed by Join Filter: 1553448
  • Filter: (CASE WHEN ((ship_2.shipment_status = ANY ('{Cancelled,Closed,Invalid}'::text[])) OR (ship_2.shipment_status ~~ 'Resolved%'::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=2333
24. 1.071 1.108 ↓ 115.0 115 1

Bitmap Heap Scan on terminal.shipment ship_2 (cost=3.57..276.82 rows=1 width=56) (actual time=0.160..1.108 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
  • Recheck Cond: (ship_2.ibx = 'SV16'::text)
  • 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: 98
  • Heap Blocks: exact=197
  • Buffers: shared hit=201
25. 0.037 0.037 ↓ 1.4 214 1

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

  • Index Cond: (ship_2.ibx = 'SV16'::text)
  • Buffers: shared hit=4
26. 639.975 1,231.190 ↓ 44.1 13,509 115

Subquery Scan on a_2 (cost=13,840.62..14,757.75 rows=306 width=23) (actual time=2.304..10.706 rows=13,509 loops=115)

  • 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
27. 348.172 591.215 ↑ 1.0 61,069 115

Sort (cost=13,840.62..13,993.47 rows=61,142 width=76) (actual time=2.304..5.141 rows=61,069 loops=115)

  • 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
28. 32.310 243.043 ↑ 1.0 61,069 1

WindowAgg (cost=7,604.17..8,979.87 rows=61,142 width=76) (actual time=205.859..243.043 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
29. 196.903 210.733 ↑ 1.0 61,069 1

Sort (cost=7,604.17..7,757.03 rows=61,142 width=68) (actual time=205.849..210.733 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
30. 13.830 13.830 ↑ 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.009..13.830 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
31. 0.126 0.126 ↑ 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.009..0.009 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)
  • Buffers: shared hit=56
Planning time : 3.310 ms
Execution time : 2,032.298 ms