explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9nIw : Optimization for: plan #IppZ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 677.430 179,259.061 ↓ 12.4 52,659 1

Hash Right Join (cost=1,650,254.92..1,687,663.42 rows=4,254 width=106) (actual time=179,228.689..179,259.061 rows=52,659 loops=1)

  • Hash Cond: ((td.tracker_id)::text = (trackers.id)::text)
  • Join Filter: (trackers.created_at < trackers_2.created_at)
  • Rows Removed by Join Filter: 50570
2. 2,586.086 25,658.053 ↓ 2.6 831,138 1

Hash Join (cost=344,525.95..381,522.51 rows=325,519 width=48) (actual time=22,722.640..25,658.053 rows=831,138 loops=1)

  • Hash Cond: ((trackers_2.id)::text = (td.tracker_id)::text)
3. 350.087 350.087 ↑ 1.0 959,821 1

Seq Scan on trackers trackers_2 (cost=0.00..36,492.62 rows=959,873 width=41) (actual time=0.014..350.087 rows=959,821 loops=1)

4. 758.000 22,721.880 ↓ 2.6 831,138 1

Hash (cost=343,386.63..343,386.63 rows=325,519 width=40) (actual time=22,721.879..22,721.880 rows=831,138 loops=1)

  • Buckets: 1048576 (originally 524288) Batches: 2 (originally 1) Memory Usage: 57345kB
5. 243.334 21,963.880 ↓ 2.6 831,138 1

Subquery Scan on td (cost=341,433.52..343,386.63 rows=325,519 width=40) (actual time=21,142.110..21,963.880 rows=831,138 loops=1)

6. 7,968.521 21,720.546 ↓ 2.6 831,138 1

HashAggregate (cost=341,433.52..342,410.08 rows=325,519 width=40) (actual time=21,142.109..21,720.546 rows=831,138 loops=1)

  • Group Key: tracking_details_1.tracker_id
7. 13,752.025 13,752.025 ↓ 1.0 11,287,072 1

Seq Scan on tracking_details tracking_details_1 (cost=0.00..330,164.96 rows=11,268,557 width=40) (actual time=0.008..13,752.025 rows=11,287,072 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 804403
8. 53.998 152,923.578 ↓ 12.4 52,659 1

Hash (cost=1,305,714.08..1,305,714.08 rows=4,254 width=139) (actual time=152,923.578..152,923.578 rows=52,659 loops=1)

  • Buckets: 65536 (originally 8192) Batches: 1 (originally 1) Memory Usage: 9987kB
9. 632.240 152,869.580 ↓ 12.4 52,659 1

Merge Left Join (cost=1,303,952.23..1,305,714.08 rows=4,254 width=139) (actual time=150,342.553..152,869.580 rows=52,659 loops=1)

  • Merge Cond: ((trackers.id)::text = (td_1.tracker_id)::text)
  • Join Filter: (trackers.created_at < (max(td_1.datetime)))
  • Rows Removed by Join Filter: 6046
10. 720.582 46,732.162 ↓ 12.4 52,555 1

Sort (cost=762,913.50..762,915.63 rows=4,254 width=131) (actual time=46,712.741..46,732.162 rows=52,555 loops=1)

  • Sort Key: trackers.id
  • Sort Method: quicksort Memory: 15488kB
11. 642.697 46,011.580 ↓ 12.4 52,555 1

Hash Right Join (cost=394,558.63..762,862.22 rows=4,254 width=131) (actual time=27,512.720..46,011.580 rows=52,555 loops=1)

  • Hash Cond: ((tracking_details.tracker_id)::text = (trackers.id)::text)
  • Join Filter: (trackers.created_at < tracking_details.created_at)
  • Rows Removed by Join Filter: 2154
12. 13,045.571 40,763.633 ↑ 7.3 831,138 1

Hash Semi Join (cost=382,824.58..746,548.51 rows=6,046,660 width=40) (actual time=22,903.868..40,763.633 rows=831,138 loops=1)

  • Hash Cond: (((tracking_details.tracker_id)::text = (trackers_1.id)::text) AND (tracking_details.datetime = (max(tracking_details_2.datetime))))
13. 4,824.798 4,824.798 ↑ 1.0 12,091,475 1

Seq Scan on tracking_details (cost=0.00..330,164.96 rows=12,093,321 width=48) (actual time=0.020..4,824.798 rows=12,091,475 loops=1)

14. 922.248 22,893.264 ↓ 2.6 831,138 1

Hash (cost=381,522.51..381,522.51 rows=325,519 width=73) (actual time=22,893.264..22,893.264 rows=831,138 loops=1)

  • Buckets: 1048576 (originally 524288) Batches: 2 (originally 1) Memory Usage: 57345kB
15. 2,140.106 21,971.016 ↓ 2.6 831,138 1

Hash Join (cost=344,525.95..381,522.51 rows=325,519 width=73) (actual time=19,440.064..21,971.016 rows=831,138 loops=1)

  • Hash Cond: ((trackers_1.id)::text = (tracking_details_2.tracker_id)::text)
16. 393.470 393.470 ↑ 1.0 959,821 1

Seq Scan on trackers trackers_1 (cost=0.00..36,492.62 rows=959,873 width=33) (actual time=0.007..393.470 rows=959,821 loops=1)

17. 986.340 19,437.440 ↓ 2.6 831,138 1

Hash (cost=343,386.63..343,386.63 rows=325,519 width=40) (actual time=19,437.440..19,437.440 rows=831,138 loops=1)

  • Buckets: 1048576 (originally 524288) Batches: 2 (originally 1) Memory Usage: 57345kB
18. 10,296.288 18,451.100 ↓ 2.6 831,138 1

HashAggregate (cost=341,433.52..342,410.08 rows=325,519 width=40) (actual time=17,827.742..18,451.100 rows=831,138 loops=1)

  • Group Key: tracking_details_2.tracker_id
19. 8,154.812 8,154.812 ↓ 1.0 11,287,072 1

Seq Scan on tracking_details tracking_details_2 (cost=0.00..330,164.96 rows=11,268,557 width=40) (actual time=0.009..8,154.812 rows=11,287,072 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 804403
20. 38.433 4,605.250 ↓ 25.9 52,555 1

Hash (cost=11,726.95..11,726.95 rows=2,026 width=131) (actual time=4,605.250..4,605.250 rows=52,555 loops=1)

  • Buckets: 65536 (originally 2048) Batches: 1 (originally 1) Memory Usage: 9798kB
21. 23.678 4,566.817 ↓ 25.9 52,555 1

Gather (cost=3,279.29..11,726.95 rows=2,026 width=131) (actual time=317.062..4,566.817 rows=52,555 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
22. 10.066 4,543.139 ↓ 22.0 26,278 2 / 2

Nested Loop (cost=2,279.29..10,524.35 rows=1,192 width=131) (actual time=300.793..4,543.139 rows=26,278 loops=2)

23. 37.087 2,010.433 ↓ 22.0 26,278 2 / 2

Nested Loop (cost=2,279.20..10,317.59 rows=1,192 width=123) (actual time=300.078..2,010.433 rows=26,278 loops=2)

24. 161.181 318.115 ↓ 27.9 26,274 2 / 2

Parallel Bitmap Heap Scan on trackers (cost=2,279.12..5,072.78 rows=941 width=107) (actual time=299.365..318.115 rows=26,274 loops=2)

  • Recheck Cond: ((deleted_at IS NULL) AND ((updated_at)::date > (CURRENT_DATE - 10)))
  • Heap Blocks: exact=2650
25. 0.904 156.934 ↓ 0.0 0 1 / 2

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

26. 144.696 144.696 ↓ 205.1 984,119 1 / 2

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

27. 11.334 11.334 ↑ 5.8 55,370 1 / 2

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

  • Index Cond: ((updated_at)::date > (CURRENT_DATE - 10))
28. 1,655.231 1,655.231 ↑ 2.0 1 52,547 / 2

Index Scan using shopify_fulfillments_tracking_number_index on shopify_fulfillments (cost=0.09..5.57 rows=2 width=32) (actual time=0.061..0.063 rows=1 loops=52,547)

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

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

  • Index Cond: ((id = shopify_fulfillments.order_id) AND (id IS NOT NULL))
30. 207.619 105,505.178 ↓ 2.4 750,426 1

Materialize (cost=541,038.73..542,632.05 rows=318,664 width=524) (actual time=103,629.792..105,505.178 rows=750,426 loops=1)

31. 548.152 105,297.559 ↓ 2.4 750,390 1

Unique (cost=541,038.73..541,516.73 rows=318,664 width=524) (actual time=103,629.785..105,297.559 rows=750,390 loops=1)

32. 7,897.617 104,749.407 ↓ 4.1 1,297,695 1

Sort (cost=541,038.73..541,198.06 rows=318,664 width=524) (actual time=103,629.783..104,749.407 rows=1,297,695 loops=1)

  • Sort Key: td_1.tracker_id, (max(td_1.datetime))
  • Sort Method: external merge Disk: 73656kB
33. 330.181 96,851.790 ↓ 4.1 1,297,759 1

Append (cost=336,788.47..481,530.52 rows=318,664 width=524) (actual time=7,825.616..96,851.790 rows=1,297,759 loops=1)

34. 1,396.282 8,280.937 ↓ 2.1 573,064 1

HashAggregate (cost=336,788.47..337,606.13 rows=272,552 width=40) (actual time=7,825.615..8,280.937 rows=573,064 loops=1)

  • Group Key: td_1.tracker_id
35. 6,884.655 6,884.655 ↓ 1.0 603,294 1

Seq Scan on tracking_details td_1 (cost=0.00..336,211.62 rows=576,851 width=40) (actual time=0.038..6,884.655 rows=603,294 loops=1)

  • Filter: ((status)::text = 'delivered'::text)
  • Rows Removed by Filter: 11488181
36. 696.319 88,240.672 ↓ 15.7 724,695 1

Finalize GroupAggregate (cost=139,792.06..142,968.40 rows=46,112 width=40) (actual time=74,354.265..88,240.672 rows=724,695 loops=1)

  • Group Key: td_2.tracker_id
37. 5,934.068 87,544.353 ↓ 26.7 724,695 1

Gather Merge (cost=139,792.06..142,802.93 rows=27,125 width=40) (actual time=74,354.253..87,544.353 rows=724,695 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
38. 1,824.621 81,610.285 ↓ 13.4 362,348 2 / 2

Partial GroupAggregate (cost=138,792.06..138,914.12 rows=27,125 width=40) (actual time=74,240.112..81,610.285 rows=362,348 loops=2)

  • Group Key: td_2.tracker_id
39. 29,822.724 79,785.664 ↓ 186.9 5,070,730 2 / 2

Sort (cost=138,792.06..138,805.62 rows=27,125 width=40) (actual time=74,240.101..79,785.664 rows=5,070,730 loops=2)

  • Sort Key: td_2.tracker_id
  • Sort Method: external merge Disk: 288840kB
40. 1,153.926 49,962.940 ↓ 186.9 5,070,730 2 / 2

Nested Loop (cost=58.04..138,392.58 rows=27,125 width=40) (actual time=374.775..49,962.940 rows=5,070,730 loops=2)

41. 354.812 539.191 ↓ 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=374.518..539.191 rows=362,931 loops=2)

  • Recheck Cond: ((deleted_at IS NULL) AND ((status)::text = 'delivered'::text))
  • Heap Blocks: exact=16384
42. 184.379 184.379 ↓ 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=368.757..368.757 rows=725,862 loops=1)

43. 48,269.823 48,269.823 ↑ 2.6 14 725,862 / 2

Index Only Scan using tracking_details_tracker_id_datetime_desc_idx on tracking_details td_2 (cost=0.11..61.43 rows=37 width=40) (actual time=0.103..0.133 rows=14 loops=725,862)

  • Index Cond: (tracker_id = (t.id)::text)
  • Heap Fetches: 4972671
Planning time : 22.880 ms
Execution time : 179,389.981 ms