explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CTsC

Settings
# exclusive inclusive rows x rows loops node
1. 501.443 118,977.103 ↓ 161.1 977,558 1

Nested Loop (cost=894,749.24..956,899.15 rows=6,067 width=106) (actual time=76,453.278..118,977.103 rows=977,558 loops=1)

2. 770.330 95,991.826 ↓ 161.1 977,558 1

Nested Loop (cost=894,749.16..955,846.14 rows=6,067 width=98) (actual time=76,451.794..95,991.826 rows=977,558 loops=1)

3. 1,793.984 79,542.024 ↓ 204.2 979,967 1

Hash Right Join (cost=894,749.07..931,993.55 rows=4,799 width=82) (actual time=76,442.296..79,542.024 rows=979,967 loops=1)

  • Hash Cond: ((td.tracker_id)::text = (trackers.id)::text)
4. 1,134.305 12,158.938 ↓ 2.6 831,104 1

Hash Join (cost=344,513.90..381,509.35 rows=325,519 width=40) (actual time=10,853.139..12,158.938 rows=831,104 loops=1)

  • Hash Cond: ((trackers_1.id)::text = (td.tracker_id)::text)
5. 172.260 172.260 ↑ 1.0 959,776 1

Seq Scan on trackers trackers_1 (cost=0.00..36,491.54 rows=959,845 width=33) (actual time=0.012..172.260 rows=959,776 loops=1)

6. 421.875 10,852.373 ↓ 2.6 831,104 1

Hash (cost=343,374.58..343,374.58 rows=325,519 width=40) (actual time=10,852.373..10,852.373 rows=831,104 loops=1)

  • Buckets: 1048576 (originally 524288) Batches: 2 (originally 1) Memory Usage: 57345kB
7. 121.097 10,430.498 ↓ 2.6 831,104 1

Subquery Scan on td (cost=341,421.46..343,374.58 rows=325,519 width=40) (actual time=10,027.575..10,430.498 rows=831,104 loops=1)

8. 5,716.840 10,309.401 ↓ 2.6 831,104 1

HashAggregate (cost=341,421.46..342,398.02 rows=325,519 width=40) (actual time=10,027.573..10,309.401 rows=831,104 loops=1)

  • Group Key: tracking_details.tracker_id
9. 4,592.561 4,592.561 ↓ 1.0 11,286,800 1

Seq Scan on tracking_details (cost=0.00..330,152.92 rows=11,268,544 width=40) (actual time=0.009..4,592.561 rows=11,286,800 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 804403
10. 848.169 65,589.102 ↓ 204.2 979,967 1

Hash (cost=550,218.38..550,218.38 rows=4,799 width=107) (actual time=65,589.102..65,589.102 rows=979,967 loops=1)

  • Buckets: 524288 (originally 8192) Batches: 4 (originally 1) Memory Usage: 61441kB
11. 1,235.363 64,740.933 ↓ 204.2 979,967 1

Merge Left Join (cost=548,456.15..550,218.38 rows=4,799 width=107) (actual time=61,378.365..64,740.933 rows=979,967 loops=1)

  • Merge Cond: ((trackers.id)::text = (td_1.tracker_id)::text)
12. 5,243.247 5,777.688 ↓ 200.0 959,575 1

Sort (cost=7,430.63..7,433.03 rows=4,799 width=99) (actual time=4,892.580..5,777.688 rows=959,575 loops=1)

  • Sort Key: trackers.id
  • Sort Method: external merge Disk: 104040kB
13. 426.654 534.441 ↓ 200.0 959,575 1

Bitmap Heap Scan on trackers (cost=33.12..7,371.95 rows=4,799 width=99) (actual time=118.433..534.441 rows=959,575 loops=1)

  • Recheck Cond: (deleted_at IS NULL)
  • Heap Blocks: exact=33558
14. 107.787 107.787 ↓ 205.0 984,002 1

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

15. 122.372 57,727.882 ↓ 2.4 750,427 1

Materialize (cost=541,025.51..542,618.84 rows=318,665 width=524) (actual time=56,485.774..57,727.882 rows=750,427 loops=1)

16. 362.581 57,605.510 ↓ 2.4 750,427 1

Unique (cost=541,025.51..541,503.51 rows=318,665 width=524) (actual time=56,485.770..57,605.510 rows=750,427 loops=1)

17. 4,153.725 57,242.929 ↓ 4.1 1,297,759 1

Sort (cost=541,025.51..541,184.85 rows=318,665 width=524) (actual time=56,485.769..57,242.929 rows=1,297,759 loops=1)

  • Sort Key: td_1.tracker_id, (max(td_1.datetime))
  • Sort Method: external merge Disk: 73656kB
18. 170.953 53,089.204 ↓ 4.1 1,297,759 1

Append (cost=336,776.43..481,517.28 rows=318,665 width=524) (actual time=5,908.491..53,089.204 rows=1,297,759 loops=1)

19. 708.985 6,120.605 ↓ 2.1 573,064 1

HashAggregate (cost=336,776.43..337,594.08 rows=272,552 width=40) (actual time=5,908.490..6,120.605 rows=573,064 loops=1)

  • Group Key: td_1.tracker_id
20. 5,411.620 5,411.620 ↓ 1.0 603,294 1

Seq Scan on tracking_details td_1 (cost=0.00..336,199.57 rows=576,851 width=40) (actual time=0.089..5,411.620 rows=603,294 loops=1)

  • Filter: ((status)::text = 'delivered'::text)
  • Rows Removed by Filter: 11487909
21. 440.318 46,797.646 ↓ 15.7 724,695 1

Finalize GroupAggregate (cost=139,790.87..142,967.21 rows=46,113 width=40) (actual time=37,738.688..46,797.646 rows=724,695 loops=1)

  • Group Key: td_2.tracker_id
22. 3,770.571 46,357.328 ↓ 26.7 724,695 1

Gather Merge (cost=139,790.87..142,801.74 rows=27,125 width=40) (actual time=37,738.671..46,357.328 rows=724,695 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
23. 1,301.593 42,586.757 ↓ 13.4 362,348 2 / 2

Partial GroupAggregate (cost=138,790.86..138,912.93 rows=27,125 width=40) (actual time=37,534.147..42,586.757 rows=362,348 loops=2)

  • Group Key: td_2.tracker_id
24. 19,706.647 41,285.164 ↓ 186.9 5,070,730 2 / 2

Sort (cost=138,790.86..138,804.43 rows=27,125 width=40) (actual time=37,534.133..41,285.164 rows=5,070,730 loops=2)

  • Sort Key: td_2.tracker_id
  • Sort Method: external merge Disk: 292664kB
25. 1,185.249 21,578.517 ↓ 186.9 5,070,730 2 / 2

Nested Loop (cost=58.04..138,391.39 rows=27,125 width=40) (actual time=272.204..21,578.517 rows=5,070,730 loops=2)

26. 297.356 432.063 ↓ 168.6 362,931 2 / 2

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

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

28. 19,961.205 19,961.205 ↑ 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.027..0.055 rows=14 loops=725,862)

  • Index Cond: (tracker_id = (t.id)::text)
  • Heap Fetches: 5041432
29. 15,679.472 15,679.472 ↑ 2.0 1 979,967

Index Scan using shopify_fulfillments_tracking_number_index on shopify_fulfillments (cost=0.09..4.96 rows=2 width=32) (actual time=0.016..0.016 rows=1 loops=979,967)

  • Index Cond: ((tracking_number)::text = (trackers.tracking_code)::text)
  • Filter: (id IS NOT NULL)
30. 22,483.834 22,483.834 ↑ 1.0 1 977,558

Index Scan using shopify_orders_pkey on shopify_orders (cost=0.09..0.17 rows=1 width=20) (actual time=0.023..0.023 rows=1 loops=977,558)

  • Index Cond: ((id = shopify_fulfillments.order_id) AND (id IS NOT NULL))
Planning time : 8.915 ms
Execution time : 119,168.575 ms