explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IppZ

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 74.351 63,538.850 ↓ 15.0 52,654 1

Hash Left Join (cost=1,102,838.26..1,221,611.00 rows=3,502 width=106) (actual time=62,126.726..63,538.850 rows=52,654 loops=1)

  • Hash Cond: ((trackers.id)::text = (td.tracker_id)::text)
2. 153.512 54,876.128 ↓ 15.0 52,654 1

Hash Left Join (cost=730,454.69..841,403.85 rows=3,502 width=131) (actual time=53,537.673..54,876.128 rows=52,654 loops=1)

  • Hash Cond: ((trackers.id)::text = (delivered.tracker_id)::text)
3. 97.208 13,401.847 ↓ 15.0 52,550 1

Hash Right Join (cost=383,894.39..494,341.83 rows=3,502 width=123) (actual time=12,216.845..13,401.847 rows=52,550 loops=1)

  • Hash Cond: ((tracking_details.tracker_id)::text = (trackers.id)::text)
4. 24.860 11,849.376 ↑ 40.2 41,282 1

Nested Loop (cost=372,160.35..481,352.72 rows=1,659,412 width=32) (actual time=10,761.565..11,849.376 rows=41,282 loops=1)

  • Join Filter: (tracking_details.datetime = (max(tracking_details_1.datetime)))
5. 80.313 10,792.466 ↑ 2.2 41,282 1

HashAggregate (cost=372,160.24..372,428.24 rows=89,334 width=73) (actual time=10,751.508..10,792.466 rows=41,282 loops=1)

  • Group Key: (trackers_1.id)::text, (max(tracking_details_1.datetime))
6. 494.338 10,712.153 ↑ 2.2 41,282 1

Hash Join (cost=335,074.35..372,070.90 rows=89,334 width=73) (actual time=9,822.634..10,712.153 rows=41,282 loops=1)

  • Hash Cond: ((trackers_1.id)::text = (tracking_details_1.tracker_id)::text)
7. 442.537 442.537 ↑ 1.0 959,816 1

Seq Scan on trackers trackers_1 (cost=0.00..36,492.62 rows=959,873 width=33) (actual time=0.014..442.537 rows=959,816 loops=1)

8. 45.496 9,775.278 ↑ 2.2 41,282 1

Hash (cost=334,761.68..334,761.68 rows=89,334 width=40) (actual time=9,775.278..9,775.278 rows=41,282 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 4250kB
9. 99.854 9,729.782 ↑ 2.2 41,282 1

GroupAggregate (cost=334,069.71..334,493.68 rows=89,334 width=40) (actual time=9,558.746..9,729.782 rows=41,282 loops=1)

  • Group Key: tracking_details_1.tracker_id
10. 1,117.218 9,629.928 ↓ 2.3 238,572 1

Sort (cost=334,069.71..334,121.70 rows=103,978 width=40) (actual time=9,558.737..9,629.928 rows=238,572 loops=1)

  • Sort Key: tracking_details_1.tracker_id
  • Sort Method: quicksort Memory: 24783kB
11. 3,148.138 8,512.710 ↓ 2.3 238,572 1

Bitmap Heap Scan on tracking_details tracking_details_1 (cost=203,612.07..332,336.82 rows=103,978 width=40) (actual time=5,366.288..8,512.710 rows=238,572 loops=1)

  • Recheck Cond: ((datetime > (CURRENT_DATE - 10)) AND (deleted_at IS NULL))
  • Heap Blocks: exact=11305
12. 5,364.572 5,364.572 ↓ 2.3 240,992 1

Bitmap Index Scan on tracking_details_tracker_id_datetime_desc_deleted_at_null_idx (cost=0.00..203,606.87 rows=103,978 width=0) (actual time=5,364.572..5,364.572 rows=240,992 loops=1)

  • Index Cond: (datetime > (CURRENT_DATE - 10))
13. 1,032.050 1,032.050 ↑ 37.0 1 41,282

Index Only Scan using tracking_details_tracker_id_datetime_desc_idx on tracking_details (cost=0.11..1.09 rows=37 width=40) (actual time=0.025..0.025 rows=1 loops=41,282)

  • Index Cond: (tracker_id = (trackers_1.id)::text)
  • Heap Fetches: 41281
14. 59.410 1,455.263 ↓ 25.9 52,550 1

Hash (cost=11,726.95..11,726.95 rows=2,026 width=123) (actual time=1,455.263..1,455.263 rows=52,550 loops=1)

  • Buckets: 65536 (originally 2048) Batches: 1 (originally 1) Memory Usage: 9387kB
15. 83.164 1,395.853 ↓ 25.9 52,550 1

Gather (cost=3,279.29..11,726.95 rows=2,026 width=123) (actual time=184.876..1,395.853 rows=52,550 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
16. 38.087 1,312.689 ↓ 22.0 26,275 2 / 2

Nested Loop (cost=2,279.29..10,524.35 rows=1,192 width=123) (actual time=164.052..1,312.689 rows=26,275 loops=2)

17. 50.670 959.302 ↓ 22.0 26,275 2 / 2

Nested Loop (cost=2,279.20..10,317.59 rows=1,192 width=115) (actual time=164.008..959.302 rows=26,275 loops=2)

18. 142.124 225.586 ↓ 27.9 26,271 2 / 2

Parallel Bitmap Heap Scan on trackers (cost=2,279.12..5,072.78 rows=941 width=99) (actual time=163.949..225.586 rows=26,271 loops=2)

  • Recheck Cond: ((deleted_at IS NULL) AND ((updated_at)::date > (CURRENT_DATE - 10)))
  • Heap Blocks: exact=2408
19. 0.898 83.462 ↓ 0.0 0 1 / 2

BitmapAnd (cost=2,279.12..2,279.12 rows=1,600 width=0) (actual time=166.924..166.924 rows=0 loops=1)

20. 80.433 80.433 ↓ 205.1 984,111 1 / 2

Bitmap Index Scan on trackers_deleted_at_is_null_idx (cost=0.00..32.88 rows=4,799 width=0) (actual time=160.865..160.865 rows=984,111 loops=1)

21. 2.131 2.131 ↑ 5.8 55,362 1 / 2

Bitmap Index Scan on trackers_updated_at_date_idx (cost=0.00..2,246.02 rows=319,958 width=0) (actual time=4.262..4.263 rows=55,362 loops=1)

  • Index Cond: ((updated_at)::date > (CURRENT_DATE - 10))
22. 683.046 683.046 ↑ 2.0 1 52,542 / 2

Index Scan using shopify_fulfillments_tracking_number_index on shopify_fulfillments (cost=0.09..5.57 rows=2 width=32) (actual time=0.025..0.026 rows=1 loops=52,542)

  • Index Cond: ((tracking_number)::text = (trackers.tracking_code)::text)
  • Filter: (id IS NOT NULL)
23. 315.300 315.300 ↑ 1.0 1 52,550 / 2

Index Scan using shopify_orders_pkey on shopify_orders (cost=0.09..0.17 rows=1 width=20) (actual time=0.012..0.012 rows=1 loops=52,550)

  • Index Cond: ((id = shopify_fulfillments.order_id) AND (id IS NOT NULL))
24. 36.215 41,320.769 ↓ 3.0 16,838 1

Hash (cost=346,540.32..346,540.32 rows=5,706 width=524) (actual time=41,320.769..41,320.769 rows=16,838 loops=1)

  • Buckets: 32768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1457kB
25. 1.756 41,284.554 ↓ 3.0 16,838 1

Subquery Scan on delivered (cost=346,506.09..346,540.32 rows=5,706 width=524) (actual time=41,279.172..41,284.554 rows=16,838 loops=1)

26. 21.159 41,282.798 ↓ 3.0 16,838 1

HashAggregate (cost=346,506.09..346,523.21 rows=5,706 width=524) (actual time=41,279.170..41,282.798 rows=16,838 loops=1)

  • Group Key: td_1.tracker_id, (max(td_1.datetime))
27. 2.222 41,261.639 ↓ 5.8 32,984 1

Append (cost=330,488.80..346,500.38 rows=5,706 width=524) (actual time=7,792.298..41,261.639 rows=32,984 loops=1)

28. 5.439 7,883.152 ↓ 3.1 16,263 1

Finalize GroupAggregate (cost=330,488.80..330,855.31 rows=5,281 width=40) (actual time=7,792.297..7,883.152 rows=16,263 loops=1)

  • Group Key: td_1.tracker_id
29. 94.836 7,877.713 ↓ 5.2 16,304 1

Gather Merge (cost=330,488.80..330,836.34 rows=3,131 width=40) (actual time=7,792.293..7,877.713 rows=16,304 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
30. 9.301 7,782.877 ↓ 2.6 8,152 2 / 2

Partial GroupAggregate (cost=329,488.79..329,502.88 rows=3,131 width=40) (actual time=7,772.270..7,782.877 rows=8,152 loops=2)

  • Group Key: td_1.tracker_id
31. 40.006 7,773.576 ↓ 2.8 8,674 2 / 2

Sort (cost=329,488.79..329,490.36 rows=3,131 width=40) (actual time=7,772.265..7,773.576 rows=8,674 loops=2)

  • Sort Key: td_1.tracker_id
  • Sort Method: quicksort Memory: 1125kB
32. 7,733.570 7,733.570 ↓ 2.8 8,674 2 / 2

Parallel Seq Scan on tracking_details td_1 (cost=0.00..329,452.44 rows=3,131 width=40) (actual time=2,112.893..7,733.570 rows=8,674 loops=2)

  • Filter: (((status)::text = 'delivered'::text) AND (datetime > (CURRENT_DATE - 10)))
  • Rows Removed by Filter: 6037051
33. 18.066 33,376.265 ↓ 39.3 16,721 1

Finalize GroupAggregate (cost=15,598.68..15,627.95 rows=425 width=40) (actual time=33,253.473..33,376.265 rows=16,721 loops=1)

  • Group Key: td_2.tracker_id
34. 85.977 33,358.199 ↓ 66.9 16,721 1

Gather Merge (cost=15,598.68..15,626.43 rows=250 width=40) (actual time=33,253.469..33,358.199 rows=16,721 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
35. 31.404 33,272.222 ↓ 33.4 8,360 2 / 2

Partial GroupAggregate (cost=14,598.68..14,599.80 rows=250 width=40) (actual time=33,233.685..33,272.222 rows=8,360 loops=2)

  • Group Key: td_2.tracker_id
36. 238.839 33,240.818 ↓ 258.1 64,524 2 / 2

Sort (cost=14,598.68..14,598.80 rows=250 width=40) (actual time=33,233.679..33,240.818 rows=64,524 loops=2)

  • Sort Key: td_2.tracker_id
  • Sort Method: quicksort Memory: 6419kB
37. 208.966 33,001.979 ↓ 258.1 64,524 2 / 2

Nested Loop (cost=58.04..14,596.68 rows=250 width=40) (actual time=28,699.524..33,001.979 rows=64,524 loops=2)

38. 347.853 492.154 ↓ 168.6 362,931 2 / 2

Parallel Bitmap Heap Scan on trackers t (cost=57.93..5,893.59 rows=2,153 width=33) (actual time=297.073..492.154 rows=362,931 loops=2)

  • Recheck Cond: ((deleted_at IS NULL) AND ((status)::text = 'delivered'::text))
  • Heap Blocks: exact=16618
39. 144.301 144.301 ↓ 198.3 725,862 1 / 2

Bitmap Index Scan on trackers_delivered_idx (cost=0.00..57.74 rows=3,660 width=0) (actual time=288.602..288.602 rows=725,862 loops=1)

40. 32,300.859 32,300.859 ↓ 0.0 0 725,862 / 2

Index Only Scan using tracking_details_tracker_id_datetime_desc_idx on tracking_details td_2 (cost=0.11..4.04 rows=1 width=40) (actual time=0.088..0.089 rows=0 loops=725,862)

  • Index Cond: ((tracker_id = (t.id)::text) AND (datetime > (CURRENT_DATE - 10)))
  • Heap Fetches: 62494
41. 25.115 8,588.371 ↑ 2.2 41,282 1

Hash (cost=372,070.90..372,070.90 rows=89,334 width=40) (actual time=8,588.371..8,588.371 rows=41,282 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3967kB
42. 503.612 8,563.256 ↑ 2.2 41,282 1

Hash Join (cost=335,074.35..372,070.90 rows=89,334 width=40) (actual time=7,775.621..8,563.256 rows=41,282 loops=1)

  • Hash Cond: ((trackers_2.id)::text = (td.tracker_id)::text)
43. 324.997 324.997 ↑ 1.0 959,816 1

Seq Scan on trackers trackers_2 (cost=0.00..36,492.62 rows=959,873 width=33) (actual time=0.014..324.997 rows=959,816 loops=1)

44. 12.583 7,734.647 ↑ 2.2 41,282 1

Hash (cost=334,761.68..334,761.68 rows=89,334 width=40) (actual time=7,734.647..7,734.647 rows=41,282 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3967kB
45. 12.754 7,722.064 ↑ 2.2 41,282 1

Subquery Scan on td (cost=334,069.71..334,761.68 rows=89,334 width=40) (actual time=7,485.123..7,722.064 rows=41,282 loops=1)

46. 145.802 7,709.310 ↑ 2.2 41,282 1

GroupAggregate (cost=334,069.71..334,493.68 rows=89,334 width=40) (actual time=7,485.122..7,709.310 rows=41,282 loops=1)

  • Group Key: tracking_details_2.tracker_id
47. 1,078.368 7,563.508 ↓ 2.3 238,572 1

Sort (cost=334,069.71..334,121.70 rows=103,978 width=40) (actual time=7,485.107..7,563.508 rows=238,572 loops=1)

  • Sort Key: tracking_details_2.tracker_id
  • Sort Method: quicksort Memory: 24783kB
48. 1,490.879 6,485.140 ↓ 2.3 238,572 1

Bitmap Heap Scan on tracking_details tracking_details_2 (cost=203,612.07..332,336.82 rows=103,978 width=40) (actual time=4,995.968..6,485.140 rows=238,572 loops=1)

  • Recheck Cond: ((datetime > (CURRENT_DATE - 10)) AND (deleted_at IS NULL))
  • Heap Blocks: exact=11305
49. 4,994.261 4,994.261 ↓ 2.3 240,998 1

Bitmap Index Scan on tracking_details_tracker_id_datetime_desc_deleted_at_null_idx (cost=0.00..203,606.87 rows=103,978 width=0) (actual time=4,994.261..4,994.261 rows=240,998 loops=1)

  • Index Cond: (datetime > (CURRENT_DATE - 10))
Planning time : 12.528 ms
Execution time : 63,558.389 ms