explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nOgk

Settings
# exclusive inclusive rows x rows loops node
1. 0.115 538.450 ↑ 219.4 422 1

Unique (cost=342,593.65..345,602.24 rows=92,572 width=972) (actual time=538.313..538.450 rows=422 loops=1)

  • Buffers: shared hit=65646 read=55104 dirtied=7517
  • I/O Timings: read=1306.705
2. 1.098 538.335 ↑ 219.4 422 1

Sort (cost=342,593.65..342,825.08 rows=92,572 width=972) (actual time=538.312..538.335 rows=422 loops=1)

  • Sort Key: trk.id, trk.carrier_service, trk.tracking_number, trk.carrier_description, trk.ship_date, trk.carrier_estimated_delivery_date, trk.chewy_estimated_delivery_date, trk.current_status, trk.order_id, trk.release_number, trk.creation_date, trk.modification_date
  • Sort Method: quicksort Memory: 84kB
  • Buffers: shared hit=65646 read=55104 dirtied=7517
  • I/O Timings: read=1306.705
3. 5.107 537.237 ↑ 219.4 422 1

Gather (cost=1,001.42..255,850.25 rows=92,572 width=972) (actual time=11.493..537.237 rows=422 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=65634 read=55104 dirtied=7517
  • I/O Timings: read=1306.705
4. 0.005 532.130 ↑ 656.5 141 3

Parallel Append (cost=1.42..245,593.05 rows=92,572 width=972) (actual time=33.381..532.130 rows=141 loops=3)

  • Buffers: shared hit=65634 read=55104 dirtied=7517
  • I/O Timings: read=1306.705
5. 33.287 532.084 ↑ 273.3 141 3

Nested Loop (cost=1.42..243,996.28 rows=38,537 width=98) (actual time=33.366..532.084 rows=141 loops=3)

  • Buffers: shared hit=65632 read=55099 dirtied=7517
  • I/O Timings: read=1306.682
6. 401.328 498.742 ↑ 2.5 602 3

Nested Loop Anti Join (cost=0.99..228,853.42 rows=1,475 width=98) (actual time=0.228..498.742 rows=602 loops=3)

  • Buffers: shared hit=53566 read=52651 dirtied=7517
  • I/O Timings: read=1247.043
7. 0.111 96.771 ↑ 4.7 625 3

Parallel Append (cost=0.42..18,462.26 rows=2,951 width=98) (actual time=0.039..96.771 rows=625 loops=3)

  • Buffers: shared hit=33695 read=15329 dirtied=5612
  • I/O Timings: read=177.027
8. 96.659 96.659 ↑ 4.7 625 3

Parallel Index Scan using trk_tracking_p_y2019_m11_chewy_estimated_delivery_date_idx on trk_tracking_p_y2019_m11 trk (cost=0.43..18,430.90 rows=2,949 width=98) (actual time=0.038..96.659 rows=625 loops=3)

  • Index Cond: (chewy_estimated_delivery_date = '2019-11-25'::date)
  • Filter: ((current_status)::text <> ALL ('{DELIVERED,DAMAGED,INCORRECT_ADDRESS,EXCEPTION}'::text[]))
  • Rows Removed by Filter: 15484
  • Buffers: shared hit=33691 read=15329 dirtied=5612
  • I/O Timings: read=177.027
9. 0.001 0.001 ↓ 0.0 0 1

Parallel Index Scan using trk_tracking_p_y9999_chewy_estimated_delivery_date_idx on trk_tracking_p_y9999 trk_2 (cost=0.42..8.44 rows=1 width=95) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (chewy_estimated_delivery_date = '2019-11-25'::date)
  • Filter: ((current_status)::text <> ALL ('{DELIVERED,DAMAGED,INCORRECT_ADDRESS,EXCEPTION}'::text[]))
  • Buffers: shared hit=3
10. 0.000 0.000 ↓ 0.0 0 1

Parallel Index Scan using trk_tracking_p_y2019_m12_chewy_estimated_delivery_date_idx on trk_tracking_p_y2019_m12 trk_1 (cost=0.14..8.16 rows=1 width=972) (actual time=0.000..0.000 rows=0 loops=1)

  • Index Cond: (chewy_estimated_delivery_date = '2019-11-25'::date)
  • Filter: ((current_status)::text <> ALL ('{DELIVERED,DAMAGED,INCORRECT_ADDRESS,EXCEPTION}'::text[]))
  • Buffers: shared hit=1
11. 0.001 0.643 ↓ 0.0 0 1,874

Append (cost=0.57..142.03 rows=3 width=13) (actual time=0.643..0.643 rows=0 loops=1,874)

  • Buffers: shared hit=19871 read=37322 dirtied=1905
  • I/O Timings: read=1070.016
12. 0.639 0.639 ↓ 0.0 0 1,874

Index Scan using wizmo_event_p_y2019_m11_tracking_number_idx on wizmo_event_p_y2019_m11 wiz (cost=0.57..141.68 rows=1 width=13) (actual time=0.639..0.639 rows=0 loops=1,874)

  • Index Cond: ((tracking_number)::text = (trk.tracking_number)::text)
  • Filter: ((event_code)::text = ANY ('{DELIVERED,DAMAGED,INCORRECT_ADDRESS,EXCEPTION}'::text[]))
  • Rows Removed by Filter: 25
  • Buffers: shared hit=16256 read=37321 dirtied=1901
  • I/O Timings: read=1070.012
13. 0.001 0.001 ↓ 0.0 0 1,805

Index Scan using wizmo_event_p_y2019_m12_tracking_number_idx on wizmo_event_p_y2019_m12 wiz_1 (cost=0.14..0.17 rows=1 width=146) (actual time=0.001..0.001 rows=0 loops=1,805)

  • Index Cond: ((tracking_number)::text = (trk.tracking_number)::text)
  • Filter: ((event_code)::text = ANY ('{DELIVERED,DAMAGED,INCORRECT_ADDRESS,EXCEPTION}'::text[]))
  • Buffers: shared hit=1805
14. 0.002 0.002 ↓ 0.0 0 1,805

Index Scan using wizmo_event_p_y9999_tracking_number_idx on wizmo_event_p_y9999 wiz_2 (cost=0.14..0.17 rows=1 width=22) (actual time=0.002..0.002 rows=0 loops=1,805)

  • Index Cond: ((tracking_number)::text = (trk.tracking_number)::text)
  • Filter: ((event_code)::text = ANY ('{DELIVERED,DAMAGED,INCORRECT_ADDRESS,EXCEPTION}'::text[]))
  • Buffers: shared hit=1806 read=1
  • I/O Timings: read=0.003
15. 0.001 0.055 ↓ 0.0 0 1,805

Append (cost=0.43..10.24 rows=3 width=22) (actual time=0.053..0.055 rows=0 loops=1,805)

  • Buffers: shared hit=12066 read=2448
  • I/O Timings: read=59.639
16. 0.046 0.046 ↓ 0.0 0 1,805

Index Scan using pkg_package_p_y2019_m11_tracking_number_idx on pkg_package_p_y2019_m11 pkg (cost=0.43..7.76 rows=1 width=22) (actual time=0.046..0.046 rows=0 loops=1,805)

  • Index Cond: ((tracking_number)::text = (trk.tracking_number)::text)
  • Filter: ((tnt > 1) AND ((trk.order_id)::text = (order_id)::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4879 read=2358
  • I/O Timings: read=57.396
17. 0.001 0.001 ↓ 0.0 0 1,805

Index Scan using pkg_package_p_y2019_m12_order_id_idx on pkg_package_p_y2019_m12 pkg_1 (cost=0.14..0.17 rows=1 width=292) (actual time=0.001..0.001 rows=0 loops=1,805)

  • Index Cond: ((order_id)::text = (trk.order_id)::text)
  • Filter: ((tnt > 1) AND ((trk.tracking_number)::text = (tracking_number)::text))
  • Buffers: shared hit=1805
18. 0.007 0.007 ↓ 0.0 0 1,805

Index Scan using pkg_package_p_y9999_order_id_idx on pkg_package_p_y9999 pkg_2 (cost=0.42..2.29 rows=1 width=22) (actual time=0.007..0.007 rows=0 loops=1,805)

  • Index Cond: ((order_id)::text = (trk.order_id)::text)
  • Filter: ((tnt > 1) AND ((trk.tracking_number)::text = (tracking_number)::text))
  • Buffers: shared hit=5382 read=90
  • I/O Timings: read=2.243
19. 0.000 0.041 ↓ 0.0 0 1

Nested Loop (cost=1.42..208.20 rows=35 width=389) (actual time=0.041..0.041 rows=0 loops=1)

  • Buffers: shared hit=2 read=5
  • I/O Timings: read=0.023
20. 0.000 0.041 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.99..185.76 rows=1 width=389) (actual time=0.041..0.041 rows=0 loops=1)

  • Buffers: shared hit=2 read=5
  • I/O Timings: read=0.023
21. 0.001 0.041 ↓ 0.0 0 1

Parallel Append (cost=0.42..21.52 rows=3 width=388) (actual time=0.040..0.041 rows=0 loops=1)

  • Buffers: shared hit=2 read=5
  • I/O Timings: read=0.023
22. 0.015 0.015 ↓ 0.0 0 1

Parallel Index Scan using trk_tracking_p_y2019_m11_chewy_estimated_delivery_date_idx on trk_tracking_p_y2019_m11 trk_3 (cost=0.43..4.90 rows=1 width=98) (actual time=0.015..0.015 rows=0 loops=1)

  • Index Cond: (chewy_estimated_delivery_date = '2019-11-24'::date)
  • Filter: ((current_status)::text <> ALL ('{DELIVERED,DAMAGED,INCORRECT_ADDRESS,EXCEPTION}'::text[]))
  • Buffers: shared hit=1 read=2
  • I/O Timings: read=0.008
23. 0.022 0.022 ↓ 0.0 0 1

Parallel Index Scan using trk_tracking_p_y9999_chewy_estimated_delivery_date_idx on trk_tracking_p_y9999 trk_5 (cost=0.42..8.44 rows=1 width=95) (actual time=0.022..0.022 rows=0 loops=1)

  • Index Cond: (chewy_estimated_delivery_date = '2019-11-24'::date)
  • Filter: ((current_status)::text <> ALL ('{DELIVERED,DAMAGED,INCORRECT_ADDRESS,EXCEPTION}'::text[]))
  • Buffers: shared read=3
  • I/O Timings: read=0.015
24. 0.003 0.003 ↓ 0.0 0 1

Parallel Index Scan using trk_tracking_p_y2019_m12_chewy_estimated_delivery_date_idx on trk_tracking_p_y2019_m12 trk_4 (cost=0.14..8.16 rows=1 width=972) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (chewy_estimated_delivery_date = '2019-11-24'::date)
  • Filter: ((current_status)::text <> ALL ('{DELIVERED,DAMAGED,INCORRECT_ADDRESS,EXCEPTION}'::text[]))
  • Buffers: shared hit=1
25. 0.000 0.000 ↓ 0.0 0

Append (cost=0.57..163.06 rows=3 width=13) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Index Scan using wizmo_event_p_y2019_m11_tracking_number_idx on wizmo_event_p_y2019_m11 wiz_3 (cost=0.57..150.72 rows=1 width=13) (never executed)

  • Index Cond: ((tracking_number)::text = (trk_3.tracking_number)::text)
  • Filter: ((event_code)::text = ANY ('{DELIVERED,DAMAGED,INCORRECT_ADDRESS,EXCEPTION}'::text[]))
27. 0.000 0.000 ↓ 0.0 0

Index Scan using wizmo_event_p_y2019_m12_tracking_number_idx on wizmo_event_p_y2019_m12 wiz_4 (cost=0.14..5.49 rows=1 width=146) (never executed)

  • Index Cond: ((tracking_number)::text = (trk_3.tracking_number)::text)
  • Filter: ((event_code)::text = ANY ('{DELIVERED,DAMAGED,INCORRECT_ADDRESS,EXCEPTION}'::text[]))
28. 0.000 0.000 ↓ 0.0 0

Index Scan using wizmo_event_p_y9999_tracking_number_idx on wizmo_event_p_y9999 wiz_5 (cost=0.14..6.83 rows=1 width=22) (never executed)

  • Index Cond: ((tracking_number)::text = (trk_3.tracking_number)::text)
  • Filter: ((event_code)::text = ANY ('{DELIVERED,DAMAGED,INCORRECT_ADDRESS,EXCEPTION}'::text[]))
29. 0.000 0.000 ↓ 0.0 0

Append (cost=0.43..22.41 rows=3 width=22) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Index Scan using pkg_package_p_y2019_m11_tracking_number_idx on pkg_package_p_y2019_m11 pkg_3 (cost=0.43..8.45 rows=1 width=22) (never executed)

  • Index Cond: ((tracking_number)::text = (trk_3.tracking_number)::text)
  • Filter: ((tnt = 1) AND ((trk_3.order_id)::text = (order_id)::text))
31. 0.000 0.000 ↓ 0.0 0

Index Scan using pkg_package_p_y2019_m12_order_id_idx on pkg_package_p_y2019_m12 pkg_4 (cost=0.14..5.50 rows=1 width=292) (never executed)

  • Index Cond: ((order_id)::text = (trk_3.order_id)::text)
  • Filter: ((tnt = 1) AND ((trk_3.tracking_number)::text = (tracking_number)::text))
32. 0.000 0.000 ↓ 0.0 0

Index Scan using pkg_package_p_y9999_order_id_idx on pkg_package_p_y9999 pkg_5 (cost=0.42..8.44 rows=1 width=22) (never executed)

  • Index Cond: ((order_id)::text = (trk_3.order_id)::text)
  • Filter: ((tnt = 1) AND ((trk_3.tracking_number)::text = (tracking_number)::text))