explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8Q1q

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 2,089.451 ↓ 7.0 14 1

Limit (cost=44,843.60..44,843.60 rows=2 width=211) (actual time=2,089.449..2,089.451 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.          

CTE a

3. 14.275 415.385 ↓ 11.0 11 1

Nested Loop Left Join (cost=13,844.17..15,037.62 rows=1 width=37) (actual time=300.033..415.385 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
4. 0.573 0.640 ↓ 14.0 14 1

Bitmap Heap Scan on terminal.shipment ship_1 (cost=3.55..273.37 rows=1 width=56) (actual time=0.243..0.640 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
5. 0.067 0.067 ↓ 1.4 214 1

Bitmap Index Scan on shipment_ibx_idx (cost=0.00..3.55 rows=151 width=0) (actual time=0.067..0.067 rows=214 loops=1)

  • Index Cond: (ship_1.ibx = 'SV16'::text)
  • Buffers: shared hit=4
6. 79.310 400.470 ↓ 44.1 13,509 14

Subquery Scan on a_1 (cost=13,840.62..14,757.75 rows=306 width=23) (actual time=20.078..28.605 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
7. 63.150 321.160 ↑ 1.0 61,069 14

Sort (cost=13,840.62..13,993.47 rows=61,142 width=76) (actual time=20.078..22.940 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
8. 32.317 258.010 ↑ 1.0 61,069 1

WindowAgg (cost=7,604.17..8,979.87 rows=61,142 width=76) (actual time=220.474..258.010 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
9. 208.889 225.693 ↑ 1.0 61,069 1

Sort (cost=7,604.17..7,757.03 rows=61,142 width=68) (actual time=220.463..225.693 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
10. 16.804 16.804 ↑ 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.020..16.804 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
11.          

CTE b

12. 119.169 1,379.826 ↓ 3.0 3 1

Nested Loop Left Join (cost=13,844.17..15,038.38 rows=1 width=37) (actual time=300.861..1,379.826 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
13. 1.139 1.177 ↓ 115.0 115 1

Bitmap Heap Scan on terminal.shipment ship_2 (cost=3.55..273.37 rows=1 width=56) (actual time=0.156..1.177 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
14. 0.038 0.038 ↓ 1.4 214 1

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

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

Subquery Scan on a_2 (cost=13,840.62..14,757.75 rows=306 width=23) (actual time=2.533..10.952 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
16. 349.556 617.665 ↑ 1.0 61,069 115

Sort (cost=13,840.62..13,993.47 rows=61,142 width=76) (actual time=2.533..5.371 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
17. 33.216 268.109 ↑ 1.0 61,069 1

WindowAgg (cost=7,604.17..8,979.87 rows=61,142 width=76) (actual time=229.270..268.109 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
18. 220.780 234.893 ↑ 1.0 61,069 1

Sort (cost=7,604.17..7,757.03 rows=61,142 width=68) (actual time=229.258..234.893 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
19. 14.113 14.113 ↑ 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..14.113 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
20.          

CTE c

21. 0.004 1,795.304 ↓ 7.0 14 1

Unique (cost=0.07..0.08 rows=2 width=32) (actual time=1,795.301..1,795.304 rows=14 loops=1)

  • Output: a_3.shipment_id
  • Buffers: shared hit=4666
22. 0.035 1,795.300 ↓ 7.0 14 1

Sort (cost=0.07..0.08 rows=2 width=32) (actual time=1,795.300..1,795.300 rows=14 loops=1)

  • Output: a_3.shipment_id
  • Sort Key: a_3.shipment_id
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=4666
23. 0.008 1,795.265 ↓ 7.0 14 1

Append (cost=0.00..0.06 rows=2 width=32) (actual time=300.037..1,795.265 rows=14 loops=1)

  • Buffers: shared hit=4666
24. 415.418 415.418 ↓ 11.0 11 1

CTE Scan on a a_3 (cost=0.00..0.02 rows=1 width=32) (actual time=300.036..415.418 rows=11 loops=1)

  • Output: a_3.shipment_id
  • Buffers: shared hit=2333
25. 1,379.839 1,379.839 ↓ 3.0 3 1

CTE Scan on b (cost=0.00..0.02 rows=1 width=32) (actual time=300.863..1,379.839 rows=3 loops=1)

  • Output: b.shipment_id
  • Buffers: shared hit=2333
26. 0.026 2,089.449 ↓ 7.0 14 1

Sort (cost=14,767.52..14,767.52 rows=2 width=211) (actual time=2,089.448..2,089.449 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
27. 0.033 2,089.423 ↓ 7.0 14 1

WindowAgg (cost=13,849.56..14,767.51 rows=2 width=211) (actual time=2,089.414..2,089.423 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
28. 3.437 2,089.390 ↓ 7.0 14 1

Merge Right Join (cost=13,849.56..14,767.47 rows=2 width=192) (actual time=2,088.007..2,089.390 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
29. 5.803 290.458 ↓ 44.0 13,454 1

Subquery Scan on a (cost=13,840.62..14,757.75 rows=306 width=23) (actual time=281.250..290.458 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
30. 27.339 284.655 ↑ 1.0 60,773 1

Sort (cost=13,840.62..13,993.47 rows=61,142 width=76) (actual time=281.247..284.655 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
31. 32.996 257.316 ↑ 1.0 61,069 1

WindowAgg (cost=7,604.17..8,979.87 rows=61,142 width=76) (actual time=218.854..257.316 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
32. 210.006 224.320 ↑ 1.0 61,069 1

Sort (cost=7,604.17..7,757.03 rows=61,142 width=68) (actual time=218.843..224.320 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
33. 14.314 14.314 ↑ 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.005..14.314 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
34. 0.039 1,795.495 ↓ 7.0 14 1

Sort (cost=8.94..8.95 rows=2 width=180) (actual time=1,795.493..1,795.495 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
35. 0.019 1,795.456 ↓ 7.0 14 1

Nested Loop (cost=0.47..8.93 rows=2 width=180) (actual time=1,795.348..1,795.456 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
36. 0.004 1,795.325 ↓ 7.0 14 1

Unique (cost=0.05..0.06 rows=2 width=32) (actual time=1,795.321..1,795.325 rows=14 loops=1)

  • Output: c.shipment_id
  • Buffers: shared hit=4666
37. 0.010 1,795.321 ↓ 7.0 14 1

Sort (cost=0.05..0.06 rows=2 width=32) (actual time=1,795.320..1,795.321 rows=14 loops=1)

  • Output: c.shipment_id
  • Sort Key: c.shipment_id
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=4666
38. 1,795.311 1,795.311 ↓ 7.0 14 1

CTE Scan on c (cost=0.00..0.04 rows=2 width=32) (actual time=1,795.303..1,795.311 rows=14 loops=1)

  • Output: c.shipment_id
  • Buffers: shared hit=4666
39. 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 = c.shipment_id)
  • Buffers: shared hit=56
Planning time : 1.331 ms
Execution time : 2,090.569 ms