explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7hTB

Settings
# exclusive inclusive rows x rows loops node
1. 1,498.458 155,117.863 ↓ 203.5 1,235,454 1

Hash Right Join (cost=1,358,813.20..1,681,477.88 rows=6,070 width=125) (actual time=145,030.758..155,117.863 rows=1,235,454 loops=1)

  • Hash Cond: ((tracking_details.tracker_id)::text = (trackers.id)::text)
2. 1,151.663 22,428.272 ↓ 11,772.4 871,154 1

Gather (cost=346,724.78..669,383.34 rows=74 width=51) (actual time=13,839.419..22,428.272 rows=871,154 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
3. 5,179.922 21,276.609 ↓ 9,899.5 435,577 2 / 2

Hash Join (cost=345,724.78..668,375.94 rows=44 width=51) (actual time=13,844.572..21,276.609 rows=435,577 loops=2)

  • Hash Cond: (((tracking_details.tracker_id)::text = (tracking_details_1.tracker_id)::text) AND (tracking_details.datetime = (max(tracking_details_1.datetime))))
4. 2,255.035 2,255.035 ↑ 1.2 6,038,563 2 / 2

Parallel Seq Scan on tracking_details (cost=0.00..315,084.11 rows=7,206,705 width=51) (actual time=0.018..2,255.035 rows=6,038,563 loops=2)

5. 505.636 13,841.652 ↓ 2.7 871,154 2 / 2

Hash (cost=344,422.71..344,422.71 rows=325,519 width=40) (actual time=13,841.652..13,841.652 rows=871,154 loops=2)

  • Buckets: 1048576 (originally 524288) Batches: 2 (originally 1) Memory Usage: 57345kB
6. 6,754.386 13,336.016 ↓ 2.7 871,154 2 / 2

HashAggregate (cost=342,469.59..343,446.15 rows=325,519 width=40) (actual time=12,925.273..13,336.016 rows=871,154 loops=2)

  • Group Key: tracking_details_1.tracker_id
7. 6,581.630 6,581.630 ↑ 1.0 12,077,126 2 / 2

Seq Scan on tracking_details tracking_details_1 (cost=0.00..330,218.19 rows=12,251,398 width=40) (actual time=0.015..6,581.630 rows=12,077,126 loops=2)

8. 661.477 131,191.133 ↓ 203.5 1,235,454 1

Hash (cost=1,012,067.17..1,012,067.17 rows=6,070 width=131) (actual time=131,191.133..131,191.133 rows=1,235,454 loops=1)

  • Buckets: 524288 (originally 8192) Batches: 4 (originally 1) Memory Usage: 61441kB
9. 841.599 130,529.656 ↓ 203.5 1,235,454 1

Merge Left Join (cost=1,010,292.30..1,012,067.17 rows=6,070 width=131) (actual time=127,839.837..130,529.656 rows=1,235,454 loops=1)

  • Merge Cond: ((trackers.id)::text = (td.tracker_id)::text)
10. 4,485.739 27,554.195 ↓ 200.2 1,215,025 1

Sort (cost=462,404.63..462,407.66 rows=6,070 width=123) (actual time=26,489.421..27,554.195 rows=1,215,025 loops=1)

  • Sort Key: trackers.id
  • Sort Method: external merge Disk: 141776kB
11. 1,581.124 23,068.456 ↓ 200.2 1,215,025 1

Gather (cost=400,902.06..462,328.34 rows=6,070 width=123) (actual time=14,243.734..23,068.456 rows=1,215,025 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
12. 706.198 21,487.332 ↓ 170.1 607,512 2 / 2

Hash Left Join (cost=399,902.06..460,721.34 rows=3,571 width=123) (actual time=14,296.735..21,487.332 rows=607,512 loops=2)

  • Hash Cond: ((trackers.id)::text = (first_detail.tracker_id)::text)
13. 612.383 7,791.809 ↓ 170.1 607,512 2 / 2

Nested Loop (cost=55,175.58..115,992.99 rows=3,571 width=115) (actual time=1,303.607..7,791.809 rows=607,512 loops=2)

14. 1,066.056 2,926.839 ↓ 170.1 607,512 2 / 2

Hash Left Join (cost=55,175.49..115,375.05 rows=3,571 width=107) (actual time=1,303.556..2,926.839 rows=607,512 loops=2)

  • Hash Cond: ((shopify_fulfillments.tracking_number)::text = (trackers.tracking_code)::text)
  • Filter: (trackers.deleted_at IS NULL)
  • Rows Removed by Filter: 96
15. 560.019 560.019 ↑ 1.2 607,514 2 / 2

Parallel Seq Scan on shopify_fulfillments (cost=0.00..32,494.44 rows=714,146 width=32) (actual time=0.015..560.019 rows=607,514 loops=2)

16. 710.733 1,300.764 ↑ 1.0 958,928 2 / 2

Hash (cost=36,492.54..36,492.54 rows=976,845 width=99) (actual time=1,300.764..1,300.764 rows=958,928 loops=2)

  • Buckets: 524288 Batches: 4 Memory Usage: 32404kB
17. 590.031 590.031 ↑ 1.0 958,928 2 / 2

Seq Scan on trackers (cost=0.00..36,492.54 rows=976,845 width=99) (actual time=0.016..590.031 rows=958,928 loops=2)

18. 4,252.587 4,252.587 ↑ 1.0 1 1,215,025 / 2

Index Scan using shopify_orders_pkey on shopify_orders (cost=0.09..0.17 rows=1 width=20) (actual time=0.007..0.007 rows=1 loops=1,215,025)

  • Index Cond: (id = shopify_fulfillments.order_id)
19. 557.549 12,989.325 ↓ 2.5 829,567 2 / 2

Hash (cost=343,587.16..343,587.16 rows=325,519 width=40) (actual time=12,989.325..12,989.325 rows=829,567 loops=2)

  • Buckets: 1048576 (originally 524288) Batches: 2 (originally 1) Memory Usage: 57345kB
20. 119.080 12,431.776 ↓ 2.5 829,567 2 / 2

Subquery Scan on first_detail (cost=341,634.05..343,587.16 rows=325,519 width=40) (actual time=11,923.503..12,431.776 rows=829,567 loops=2)

21. 6,013.667 12,312.696 ↓ 2.5 829,567 2 / 2

HashAggregate (cost=341,634.05..342,610.60 rows=325,519 width=40) (actual time=11,923.502..12,312.696 rows=829,567 loops=2)

  • Group Key: tracking_details_2.tracker_id
22. 6,299.029 6,299.029 ↑ 1.0 11,272,723 2 / 2

Seq Scan on tracking_details tracking_details_2 (cost=0.00..330,218.19 rows=11,415,853 width=40) (actual time=1.147..6,299.029 rows=11,272,723 loops=2)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 804403
23. 84.552 102,133.862 ↓ 2.4 754,381 1

Materialize (cost=547,887.68..549,490.24 rows=320,513 width=524) (actual time=101,350.406..102,133.862 rows=754,381 loops=1)

24. 226.104 102,049.310 ↓ 2.3 750,390 1

Unique (cost=547,887.68..548,368.45 rows=320,513 width=524) (actual time=101,350.400..102,049.310 rows=750,390 loops=1)

25. 2,917.071 101,823.206 ↓ 4.0 1,297,685 1

Sort (cost=547,887.68..548,047.94 rows=320,513 width=524) (actual time=101,350.399..101,823.206 rows=1,297,685 loops=1)

  • Sort Key: td.tracker_id, (max(td.datetime))
  • Sort Method: external merge Disk: 73656kB
26. 143.354 98,906.135 ↓ 4.0 1,297,685 1

Append (cost=336,928.28..488,030.49 rows=320,513 width=524) (actual time=3,581.591..98,906.135 rows=1,297,685 loops=1)

27. 519.392 3,738.056 ↓ 2.1 573,028 1

HashAggregate (cost=336,928.28..337,749.67 rows=273,795 width=40) (actual time=3,581.590..3,738.056 rows=573,028 loops=1)

  • Group Key: td.tracker_id
28. 3,218.664 3,218.664 ↓ 1.0 603,242 1

Seq Scan on tracking_details td (cost=0.00..336,343.89 rows=584,392 width=40) (actual time=0.023..3,218.664 rows=603,242 loops=1)

  • Filter: ((status)::text = 'delivered'::text)
  • Rows Removed by Filter: 11473884
29. 418.039 95,024.725 ↓ 15.5 724,657 1

Finalize GroupAggregate (cost=146,101.25..149,319.28 rows=46,718 width=40) (actual time=87,113.316..95,024.725 rows=724,657 loops=1)

  • Group Key: td_1.tracker_id
30. 2,481.108 94,606.686 ↓ 26.4 724,657 1

Gather Merge (cost=146,101.25..149,151.65 rows=27,481 width=40) (actual time=87,113.311..94,606.686 rows=724,657 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
31. 1,236.712 92,125.578 ↓ 13.2 362,328 2 / 2

Partial GroupAggregate (cost=145,101.25..145,224.92 rows=27,481 width=40) (actual time=87,078.746..92,125.578 rows=362,328 loops=2)

  • Group Key: td_1.tracker_id
32. 14,172.174 90,888.866 ↓ 184.5 5,070,306 2 / 2

Sort (cost=145,101.25..145,114.99 rows=27,481 width=40) (actual time=87,078.732..90,888.866 rows=5,070,306 loops=2)

  • Sort Key: td_1.tracker_id
  • Sort Method: external merge Disk: 288640kB
33. 702.546 76,716.692 ↓ 184.5 5,070,306 2 / 2

Nested Loop (cost=33.27..144,696.01 rows=27,481 width=40) (actual time=66.893..76,716.692 rows=5,070,306 loops=2)

34. 1,221.467 1,254.274 ↓ 165.6 362,912 2 / 2

Parallel Bitmap Heap Scan on trackers t (cost=33.16..7,472.25 rows=2,191 width=33) (actual time=65.751..1,254.274 rows=362,912 loops=2)

  • Recheck Cond: (deleted_at IS NULL)
  • Filter: ((status)::text = 'delivered'::text)
  • Rows Removed by Filter: 116456
  • Heap Blocks: exact=16570
35. 32.806 32.806 ↓ 200.6 979,971 1 / 2

Bitmap Index Scan on trackers_deleted_at_is_null_idx (cost=0.00..32.97 rows=4,884 width=0) (actual time=65.613..65.613 rows=979,971 loops=1)

36. 74,759.872 74,759.872 ↑ 2.7 14 725,824 / 2

Index Only Scan using tracking_details_pkey on tracking_details td_1 (cost=0.11..62.52 rows=38 width=40) (actual time=0.117..0.206 rows=14 loops=725,824)

  • Index Cond: (tracker_id = (t.id)::text)
  • Heap Fetches: 4986819
Planning time : 3.505 ms
Execution time : 155,287.158 ms