explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hlWe : Optimization for: plan #WmHj - added created_at filter

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.001 46,421.963 ↑ 1.0 1 1

Limit (cost=1,209,300.87..1,212,164.49 rows=1 width=133) (actual time=46,421.963..46,421.963 rows=1 loops=1)

2. 0.002 46,421.962 ↑ 6,068.0 1 1

Nested Loop (cost=1,209,300.87..18,585,746.09 rows=6,068 width=133) (actual time=46,421.962..46,421.962 rows=1 loops=1)

3. 0.006 46,421.930 ↑ 6,068.0 1 1

Nested Loop (cost=1,209,300.78..18,584,686.92 rows=6,068 width=117) (actual time=46,421.930..46,421.930 rows=1 loops=1)

4. 0.008 46,421.881 ↑ 4,798.0 1 1

Merge Right Join (cost=1,209,300.70..18,560,838.43 rows=4,798 width=101) (actual time=46,421.881..46,421.881 rows=1 loops=1)

  • Merge Cond: ((td.tracker_id)::text = (trackers.id)::text)
5. 0.001 42,628.452 ↑ 163,112.0 1 1

Unique (cost=519,965.40..520,210.07 rows=163,112 width=524) (actual time=42,628.452..42,628.452 rows=1 loops=1)

6. 609.882 42,628.451 ↑ 163,112.0 1 1

Sort (cost=519,965.40..520,046.96 rows=163,112 width=524) (actual time=42,628.451..42,628.451 rows=1 loops=1)

  • Sort Key: td.tracker_id, (max(td.datetime))
  • Sort Method: quicksort Memory: 26247kB
7. 21.309 42,018.569 ↓ 1.6 257,310 1

Append (cost=329,072.51..489,663.54 rows=163,112 width=524) (actual time=1,545.105..42,018.569 rows=257,310 loops=1)

8. 40.842 1,695.643 ↑ 1.2 120,645 1

Finalize GroupAggregate (cost=329,072.51..342,358.70 rows=147,529 width=40) (actual time=1,545.104..1,695.643 rows=120,645 loops=1)

  • Group Key: td.tracker_id
9. 87.472 1,654.801 ↓ 1.1 121,696 1

Gather Merge (cost=329,072.51..341,801.44 rows=114,675 width=40) (actual time=1,545.099..1,654.801 rows=121,696 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
10. 24.704 1,567.329 ↑ 1.9 60,848 2 / 2

Partial GroupAggregate (cost=328,072.51..328,588.55 rows=114,675 width=40) (actual time=1,532.869..1,567.329 rows=60,848 loops=2)

  • Group Key: td.tracker_id
11. 141.140 1,542.625 ↑ 1.8 64,378 2 / 2

Sort (cost=328,072.51..328,129.85 rows=114,675 width=40) (actual time=1,532.862..1,542.625 rows=64,378 loops=2)

  • Sort Key: td.tracker_id
  • Sort Method: quicksort Memory: 8201kB
12. 1,401.485 1,401.485 ↑ 1.8 64,378 2 / 2

Parallel Seq Scan on tracking_details td (cost=0.00..326,145.15 rows=114,675 width=40) (actual time=448.165..1,401.485 rows=64,378 loops=2)

  • Filter: (((status)::text = 'delivered'::text) AND ((created_at)::date > '2020-01-01'::date))
  • Rows Removed by Filter: 5979091
13. 49.961 40,301.617 ↓ 8.8 136,665 1

Finalize GroupAggregate (cost=145,742.16..146,815.50 rows=15,583 width=40) (actual time=39,818.269..40,301.617 rows=136,665 loops=1)

  • Group Key: td_1.tracker_id
14. 140.895 40,251.656 ↓ 14.9 136,665 1

Gather Merge (cost=145,742.16..146,759.59 rows=9,166 width=40) (actual time=39,818.232..40,251.656 rows=136,665 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
15. 156.123 40,110.761 ↓ 7.5 68,332 2 / 2

Partial GroupAggregate (cost=144,742.16..144,783.41 rows=9,166 width=40) (actual time=39,786.934..40,110.761 rows=68,332 loops=2)

  • Group Key: td_1.tracker_id
16. 1,599.429 39,954.638 ↓ 99.3 910,114 2 / 2

Sort (cost=144,742.16..144,746.74 rows=9,166 width=40) (actual time=39,786.918..39,954.638 rows=910,114 loops=2)

  • Sort Key: td_1.tracker_id
  • Sort Method: external merge Disk: 51664kB
17. 306.269 38,355.209 ↓ 99.3 910,114 2 / 2

Nested Loop (cost=33.18..144,621.52 rows=9,166 width=40) (actual time=35,458.062..38,355.209 rows=910,114 loops=2)

18. 279.743 304.480 ↓ 168.6 362,928 2 / 2

Parallel Bitmap Heap Scan on trackers t (cost=33.07..7,365.68 rows=2,152 width=33) (actual time=49.984..304.480 rows=362,928 loops=2)

  • Recheck Cond: (deleted_at IS NULL)
  • Filter: ((status)::text = 'delivered'::text)
  • Rows Removed by Filter: 116740
  • Heap Blocks: exact=16810
19. 24.737 24.737 ↓ 204.6 981,869 1 / 2

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

20. 37,744.460 37,744.460 ↑ 4.3 3 725,855 / 2

Index Scan using tracking_details_tracker_id_index on tracking_details td_1 (cost=0.11..63.74 rows=13 width=40) (actual time=0.102..0.104 rows=3 loops=725,855)

  • Index Cond: ((tracker_id)::text = (t.id)::text)
  • Filter: ((created_at)::date > '2020-01-01'::date)
  • Rows Removed by Filter: 11
21. 0.004 3,793.421 ↑ 4,798.0 1 1

Materialize (cost=689,335.29..18,040,052.62 rows=4,798 width=126) (actual time=3,793.421..3,793.421 rows=1 loops=1)

22. 15.921 3,793.417 ↑ 4,798.0 1 1

Nested Loop Left Join (cost=689,335.29..18,040,050.22 rows=4,798 width=126) (actual time=3,793.417..3,793.417 rows=1 loops=1)

  • Join Filter: ((trackers.id)::text = (first_detail.tracker_id)::text)
  • Rows Removed by Join Filter: 191692
23. 0.005 0.108 ↑ 4,798.0 1 1

Nested Loop Left Join (cost=342,793.75..11,444,032.16 rows=4,798 width=118) (actual time=0.108..0.108 rows=1 loops=1)

24. 0.047 0.047 ↑ 4,798.0 1 1

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

  • Filter: (deleted_at IS NULL)
25. 0.015 0.056 ↓ 0.0 0 1

Hash Join (cost=342,793.66..345,088.57 rows=1 width=51) (actual time=0.056..0.056 rows=0 loops=1)

  • Hash Cond: (((tracking_details_1.tracker_id)::text = (tracking_details.tracker_id)::text) AND ((max(tracking_details_1.datetime)) = tracking_details.datetime))
26. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=342,733.33..343,709.89 rows=325,519 width=40) (never executed)

  • Group Key: tracking_details_1.tracker_id
27. 0.000 0.000 ↓ 0.0 0

Seq Scan on tracking_details tracking_details_1 (cost=0.00..330,472.50 rows=12,260,833 width=40) (never executed)

28. 0.002 0.041 ↓ 0.0 0 1

Hash (cost=60.28..60.28 rows=13 width=51) (actual time=0.041..0.041 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
29. 0.039 0.039 ↓ 0.0 0 1

Index Scan using tracking_details_tracker_id_index on tracking_details (cost=0.11..60.28 rows=13 width=51) (actual time=0.039..0.039 rows=0 loops=1)

  • Index Cond: ((trackers.id)::text = (tracker_id)::text)
  • Filter: ((created_at)::date > '2020-01-01'::date)
  • Rows Removed by Filter: 9
30. 36.282 3,777.388 ↑ 1.7 191,692 1

Materialize (cost=346,541.55..348,820.18 rows=325,519 width=40) (actual time=3,665.347..3,777.388 rows=191,692 loops=1)

31. 15.858 3,741.106 ↑ 1.7 191,692 1

Subquery Scan on first_detail (cost=346,541.55..348,494.66 rows=325,519 width=40) (actual time=3,665.344..3,741.106 rows=191,692 loops=1)

32. 721.465 3,725.248 ↑ 1.7 191,692 1

HashAggregate (cost=346,541.55..347,518.10 rows=325,519 width=40) (actual time=3,665.343..3,725.248 rows=191,692 loops=1)

  • Group Key: tracking_details_2.tracker_id
33. 3,003.783 3,003.783 ↑ 1.6 2,412,408 1

Seq Scan on tracking_details tracking_details_2 (cost=0.00..342,733.33 rows=3,808,215 width=40) (actual time=928.808..3,003.783 rows=2,412,408 loops=1)

  • Filter: ((deleted_at IS NULL) AND ((created_at)::date > '2020-01-01'::date))
  • Rows Removed by Filter: 9674529
34. 0.043 0.043 ↑ 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.043..0.043 rows=1 loops=1)

  • Index Cond: ((tracking_number)::text = (trackers.tracking_code)::text)
  • Filter: (id IS NOT NULL)
35. 0.030 0.030 ↑ 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.030..0.030 rows=1 loops=1)

  • Index Cond: ((id = shopify_fulfillments.order_id) AND (id IS NOT NULL))
Planning time : 2.453 ms
Execution time : 46,453.015 ms