explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WmHj

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.001 40,205.294 ↑ 1.0 1 1

Limit (cost=1,230,773.92..1,233,637.67 rows=1 width=133) (actual time=40,205.294..40,205.294 rows=1 loops=1)

2. 0.005 40,205.293 ↑ 6,068.0 1 1

Nested Loop (cost=1,230,773.92..18,608,004.92 rows=6,068 width=133) (actual time=40,205.293..40,205.293 rows=1 loops=1)

3. 0.004 40,205.259 ↑ 6,068.0 1 1

Nested Loop (cost=1,230,773.84..18,606,945.75 rows=6,068 width=117) (actual time=40,205.258..40,205.259 rows=1 loops=1)

4. 0.008 40,205.193 ↑ 4,798.0 1 1

Merge Right Join (cost=1,230,773.75..18,583,097.26 rows=4,798 width=101) (actual time=40,205.193..40,205.193 rows=1 loops=1)

  • Merge Cond: ((td.tracker_id)::text = (trackers.id)::text)
5. 0.002 26,878.722 ↑ 320,618.0 1 1

Unique (cost=546,087.47..546,568.39 rows=320,618 width=524) (actual time=26,878.722..26,878.722 rows=1 loops=1)

6. 2,297.500 26,878.720 ↑ 320,618.0 1 1

Sort (cost=546,087.47..546,247.77 rows=320,618 width=524) (actual time=26,878.720..26,878.720 rows=1 loops=1)

  • Sort Key: td.tracker_id, (max(td.datetime))
  • Sort Method: external merge Disk: 73656kB
7. 113.544 24,581.220 ↓ 4.0 1,297,743 1

Append (cost=337,185.46..486,210.70 rows=320,618 width=524) (actual time=2,561.802..24,581.220 rows=1,297,743 loops=1)

8. 500.651 2,718.067 ↓ 2.1 573,056 1

HashAggregate (cost=337,185.46..338,007.06 rows=273,867 width=40) (actual time=2,561.802..2,718.067 rows=573,056 loops=1)

  • Group Key: td.tracker_id
9. 2,217.416 2,217.416 ↓ 1.0 603,284 1

Seq Scan on tracking_details td (cost=0.00..336,600.62 rows=584,838 width=40) (actual time=0.044..2,217.416 rows=603,284 loops=1)

  • Filter: ((status)::text = 'delivered'::text)
  • Rows Removed by Filter: 11483580
10. 248.501 21,749.609 ↓ 15.5 724,687 1

Finalize GroupAggregate (cost=144,021.42..147,241.79 rows=46,751 width=40) (actual time=17,837.942..21,749.609 rows=724,687 loops=1)

  • Group Key: td_1.tracker_id
11. 482.850 21,501.108 ↓ 26.4 724,687 1

Gather Merge (cost=144,021.42..147,074.03 rows=27,501 width=40) (actual time=17,837.937..21,501.108 rows=724,687 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
12. 837.486 21,018.258 ↓ 13.2 362,344 2 / 2

Partial GroupAggregate (cost=143,021.42..143,145.18 rows=27,501 width=40) (actual time=17,780.705..21,018.258 rows=362,344 loops=2)

  • Group Key: td_1.tracker_id
13. 10,082.734 20,180.772 ↓ 184.4 5,070,650 2 / 2

Sort (cost=143,021.42..143,035.17 rows=27,501 width=40) (actual time=17,780.691..20,180.772 rows=5,070,650 loops=2)

  • Sort Key: td_1.tracker_id
  • Sort Method: external merge Disk: 292720kB
14. 650.596 10,098.038 ↓ 184.4 5,070,650 2 / 2

Nested Loop (cost=33.18..142,615.86 rows=27,501 width=40) (actual time=53.748..10,098.038 rows=5,070,650 loops=2)

15. 348.923 374.267 ↓ 168.6 362,927 2 / 2

Parallel Bitmap Heap Scan on trackers t (cost=33.07..7,365.65 rows=2,152 width=33) (actual time=53.662..374.267 rows=362,927 loops=2)

  • Recheck Cond: (deleted_at IS NULL)
  • Filter: ((status)::text = 'delivered'::text)
  • Rows Removed by Filter: 116722
  • Heap Blocks: exact=17406
16. 25.343 25.343 ↓ 204.6 981,810 1 / 2

Bitmap Index Scan on trackers_deleted_at_is_null_idx (cost=0.00..32.88 rows=4,798 width=0) (actual time=50.687..50.687 rows=981,810 loops=1)

17. 9,073.175 9,073.175 ↑ 2.7 14 725,854 / 2

Index Only Scan using tracking_details_pkey on tracking_details td_1 (cost=0.11..62.73 rows=38 width=40) (actual time=0.012..0.025 rows=14 loops=725,854)

  • Index Cond: (tracker_id = (t.id)::text)
  • Heap Fetches: 5043365
18. 0.003 13,326.463 ↑ 4,798.0 1 1

Materialize (cost=684,686.28..18,035,399.50 rows=4,798 width=126) (actual time=13,326.463..13,326.463 rows=1 loops=1)

19. 76.732 13,326.460 ↑ 4,798.0 1 1

Nested Loop Left Join (cost=684,686.28..18,035,397.10 rows=4,798 width=126) (actual time=13,326.460..13,326.460 rows=1 loops=1)

  • Join Filter: ((trackers.id)::text = (first_detail.tracker_id)::text)
  • Rows Removed by Join Filter: 745180
20. 0.004 6,560.325 ↑ 4,798.0 1 1

Nested Loop Left Join (cost=342,791.47..11,444,025.77 rows=4,798 width=118) (actual time=6,560.325..6,560.325 rows=1 loops=1)

21. 0.039 0.039 ↑ 4,798.0 1 1

Index Scan using trackers_pkey on trackers (cost=0.08..90,246.84 rows=4,798 width=99) (actual time=0.039..0.039 rows=1 loops=1)

  • Filter: (deleted_at IS NULL)
22. 97.017 6,560.282 ↑ 1.0 1 1

Hash Join (cost=342,791.39..345,086.30 rows=1 width=51) (actual time=6,560.282..6,560.282 rows=1 loops=1)

  • Hash Cond: (((tracking_details_1.tracker_id)::text = (tracking_details.tracker_id)::text) AND ((max(tracking_details_1.datetime)) = tracking_details.datetime))
23. 4,482.501 6,462.375 ↓ 2.4 782,503 1

HashAggregate (cost=342,731.00..343,707.55 rows=325,519 width=40) (actual time=6,250.051..6,462.375 rows=782,503 loops=1)

  • Group Key: tracking_details_1.tracker_id
24. 1,979.874 1,979.874 ↑ 1.0 12,086,864 1

Seq Scan on tracking_details tracking_details_1 (cost=0.00..330,470.25 rows=12,260,749 width=40) (actual time=0.012..1,979.874 rows=12,086,864 loops=1)

25. 0.008 0.890 ↑ 4.2 9 1

Hash (cost=60.24..60.24 rows=38 width=51) (actual time=0.890..0.890 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.882 0.882 ↑ 4.2 9 1

Index Scan using tracking_details_tracker_id_index on tracking_details (cost=0.11..60.24 rows=38 width=51) (actual time=0.865..0.882 rows=9 loops=1)

  • Index Cond: ((trackers.id)::text = (tracker_id)::text)
27. 136.436 6,689.403 ↓ 2.3 745,181 1

Materialize (cost=341,894.81..344,173.45 rows=325,519 width=40) (actual time=6,278.180..6,689.403 rows=745,181 loops=1)

28. 63.904 6,552.967 ↓ 2.3 745,181 1

Subquery Scan on first_detail (cost=341,894.81..343,847.93 rows=325,519 width=40) (actual time=6,278.174..6,552.967 rows=745,181 loops=1)

29. 3,624.123 6,489.063 ↓ 2.3 745,181 1

HashAggregate (cost=341,894.81..342,871.37 rows=325,519 width=40) (actual time=6,278.173..6,489.063 rows=745,181 loops=1)

  • Group Key: tracking_details_2.tracker_id
30. 2,864.940 2,864.940 ↑ 1.0 11,282,461 1

Seq Scan on tracking_details tracking_details_2 (cost=0.00..330,470.25 rows=11,424,566 width=40) (actual time=0.024..2,864.940 rows=11,282,461 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 804403
31. 0.062 0.062 ↑ 2.0 1 1

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

  • Index Cond: ((tracking_number)::text = (trackers.tracking_code)::text)
  • Filter: (id IS NOT NULL)
32. 0.029 0.029 ↑ 1.0 1 1

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

  • Index Cond: ((id = shopify_fulfillments.order_id) AND (id IS NOT NULL))
Planning time : 3.401 ms
Execution time : 40,297.829 ms