explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PtLE

Settings
# exclusive inclusive rows x rows loops node
1. 5.126 206.638 ↑ 273.2 422 1

Gather (cost=1,009.86..301,634.38 rows=115,286 width=98) (actual time=15.908..206.638 rows=422 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=31828 read=55122
  • I/O Timings: read=386.516
2. 21.309 201.512 ↑ 340.7 141 3

Nested Loop (cost=9.86..289,105.78 rows=48,036 width=98) (actual time=11.607..201.512 rows=141 loops=3)

  • Buffers: shared hit=31828 read=55122
  • I/O Timings: read=386.516
3. 117.621 180.168 ↑ 2.5 602 3

Nested Loop Anti Join (cost=9.43..273,907.86 rows=1,475 width=98) (actual time=9.319..180.168 rows=602 loops=3)

  • Buffers: shared hit=19819 read=52617
  • I/O Timings: read=359.087
4. 0.000 62.359 ↑ 4.7 625 3

Parallel Append (cost=8.87..63,517.27 rows=2,951 width=98) (actual time=9.070..62.359 rows=625 loops=3)

  • Buffers: shared hit=40 read=15299
  • I/O Timings: read=111.557
5. 53.311 62.216 ↑ 4.7 625 3

Parallel Bitmap Heap Scan on trk_tracking_p_y2019_m11 trk (cost=2,648.91..63,478.70 rows=2,949 width=98) (actual time=9.015..62.216 rows=625 loops=3)

  • Recheck Cond: ((chewy_estimated_delivery_date = '2019-11-24'::date) OR (chewy_estimated_delivery_date = '2019-11-25'::date))
  • Filter: ((current_status)::text <> ALL ('{DELIVERED,DAMAGED,INCORRECT_ADDRESS,EXCEPTION}'::text[]))
  • Rows Removed by Filter: 15484
  • Heap Blocks: exact=4964
  • Buffers: shared hit=37 read=15296
  • I/O Timings: read=111.542
6. 0.004 8.905 ↓ 0.0 0 1

BitmapOr (cost=2,648.91..2,648.91 rows=43,800 width=0) (actual time=8.905..8.905 rows=0 loops=1)

  • Buffers: shared hit=4 read=418
  • I/O Timings: read=2.019
7. 0.014 0.014 ↓ 0.0 0 1

Bitmap Index Scan on trk_tracking_p_y2019_m11_chewy_estimated_delivery_date_idx (cost=0.00..4.44 rows=1 width=0) (actual time=0.014..0.014 rows=0 loops=1)

  • Index Cond: (chewy_estimated_delivery_date = '2019-11-24'::date)
  • Buffers: shared hit=1 read=2
  • I/O Timings: read=0.008
8. 8.887 8.887 ↓ 1.1 48,327 1

Bitmap Index Scan on trk_tracking_p_y2019_m11_chewy_estimated_delivery_date_idx (cost=0.00..2,640.93 rows=43,800 width=0) (actual time=8.887..8.887 rows=48,327 loops=1)

  • Index Cond: (chewy_estimated_delivery_date = '2019-11-25'::date)
  • Buffers: shared hit=3 read=416
  • I/O Timings: read=2.010
9. 0.132 0.159 ↓ 0.0 0 1

Parallel Bitmap Heap Scan on trk_tracking_p_y9999 trk_2 (cost=8.87..12.88 rows=1 width=95) (actual time=0.159..0.159 rows=0 loops=1)

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

BitmapOr (cost=8.87..8.87 rows=1 width=0) (actual time=0.027..0.027 rows=0 loops=1)

  • Buffers: shared hit=3 read=3
  • I/O Timings: read=0.015
11. 0.025 0.025 ↓ 0.0 0 1

Bitmap Index Scan on trk_tracking_p_y9999_chewy_estimated_delivery_date_idx (cost=0.00..4.43 rows=1 width=0) (actual time=0.025..0.025 rows=0 loops=1)

  • Index Cond: (chewy_estimated_delivery_date = '2019-11-24'::date)
  • Buffers: shared read=3
  • I/O Timings: read=0.015
12. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on trk_tracking_p_y9999_chewy_estimated_delivery_date_idx (cost=0.00..4.43 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (chewy_estimated_delivery_date = '2019-11-25'::date)
  • Buffers: shared hit=3
13. 0.001 0.001 ↓ 0.0 0 1

Parallel Seq Scan on trk_tracking_p_y2019_m12 trk_1 (cost=0.00..10.94 rows=1 width=972) (actual time=0.000..0.001 rows=0 loops=1)

  • Filter: (((current_status)::text <> ALL ('{DELIVERED,DAMAGED,INCORRECT_ADDRESS,EXCEPTION}'::text[])) AND ((chewy_estimated_delivery_date = '2019-11-24'::date) OR (chewy_estimated_delivery_date = '2019-11-25'::date)))
14. 0.000 0.188 ↓ 0.0 0 1,874

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

  • Buffers: shared hit=19779 read=37318
  • I/O Timings: read=247.530
15. 0.185 0.185 ↓ 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.185..0.185 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=16168 read=37317
  • I/O Timings: read=247.524
16. 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
17. 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.006
18. 0.001 0.035 ↓ 0.0 0 1,805

Append (cost=0.43..10.27 rows=3 width=26) (actual time=0.033..0.035 rows=0 loops=1,805)

  • Buffers: shared hit=12009 read=2505
  • I/O Timings: read=27.429
19. 0.028 0.028 ↓ 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.78 rows=1 width=26) (actual time=0.028..0.028 rows=0 loops=1,805)

  • Index Cond: ((tracking_number)::text = (trk.tracking_number)::text)
  • Filter: (((trk.order_id)::text = (order_id)::text) AND ((tnt = 1) OR (tnt > 1)) AND (((trk.chewy_estimated_delivery_date = '2019-11-24'::date) AND (tnt = 1)) OR ((trk.chewy_estimated_delivery_date = '2019-11-25'::date) AND (tnt > 1))))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4824 read=2413
  • I/O Timings: read=26.340
20. 0.000 0.000 ↓ 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.18 rows=1 width=296) (actual time=0.000..0.000 rows=0 loops=1,805)

  • Index Cond: ((order_id)::text = (trk.order_id)::text)
  • Filter: (((trk.tracking_number)::text = (tracking_number)::text) AND ((tnt = 1) OR (tnt > 1)) AND (((trk.chewy_estimated_delivery_date = '2019-11-24'::date) AND (tnt = 1)) OR ((trk.chewy_estimated_delivery_date = '2019-11-25'::date) AND (tnt > 1))))
  • Buffers: shared hit=1805
21. 0.006 0.006 ↓ 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.30 rows=1 width=26) (actual time=0.006..0.006 rows=0 loops=1,805)

  • Index Cond: ((order_id)::text = (trk.order_id)::text)
  • Filter: (((trk.tracking_number)::text = (tracking_number)::text) AND ((tnt = 1) OR (tnt > 1)) AND (((trk.chewy_estimated_delivery_date = '2019-11-24'::date) AND (tnt = 1)) OR ((trk.chewy_estimated_delivery_date = '2019-11-25'::date) AND (tnt > 1))))
  • Buffers: shared hit=5380 read=92
  • I/O Timings: read=1.088
Planning time : 3.662 ms
Execution time : 206.920 ms