explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iwZW

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 33,455.283 ↑ 1.0 1 1

Aggregate (cost=76,634.26..76,634.27 rows=1 width=8) (actual time=33,455.283..33,455.283 rows=1 loops=1)

  • Buffers: shared hit=55553771
2. 0.533 33,455.279 ↑ 4.5 2 1

Nested Loop (cost=0.72..76,634.23 rows=9 width=0) (actual time=2,666.969..33,455.279 rows=2 loops=1)

  • Buffers: shared hit=55553771
3. 1.103 3.051 ↓ 369.0 369 1

Nested Loop (cost=0.44..3,751.10 rows=1 width=8) (actual time=0.021..3.051 rows=369 loops=1)

  • Buffers: shared hit=1505
4. 0.188 0.188 ↑ 1.0 440 1

Seq Scan on asset_truck (cost=0.00..33.40 rows=440 width=8) (actual time=0.011..0.188 rows=440 loops=1)

  • Buffers: shared hit=29
5. 1.760 1.760 ↑ 1.0 1 440

Index Scan using activity_truckposition_pkey on activity_truckposition (cost=0.44..8.44 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=440)

  • Index Cond: (id = asset_truck.current_position_id)
  • Filter: (asset_truck.id = truck_id)
  • Buffers: shared hit=1476
6. 9.767 33,451.695 ↓ 0.0 0 369

Index Scan using asset_assignment_truck_id_f4699b05 on asset_assignment (cost=0.28..72,883.04 rows=9 width=8) (actual time=90.271..90.655 rows=0 loops=369)

  • Index Cond: (truck_id = activity_truckposition.truck_id)
  • Filter: (SubPlan 1)
  • Rows Removed by Filter: 20
  • Buffers: shared hit=55552266
7.          

SubPlan (forIndex Scan)

8. 21.678 33,441.928 ↓ 0.0 0 7,226

Sort (cost=3,835.91..3,835.92 rows=1 width=20) (actual time=4.628..4.628 rows=0 loops=7,226)

  • Sort Key: x0.start_datetime, x0.end_datetime
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=55545598
9. 1.220 33,420.250 ↓ 0.0 0 7,226

Nested Loop (cost=3,790.89..3,835.90 rows=1 width=20) (actual time=4.624..4.625 rows=0 loops=7,226)

  • Join Filter: (x0.allocation_id = x2.allocation_id)
  • Buffers: shared hit=55545595
10. 247.796 33,405.798 ↑ 1.0 1 7,226

Nested Loop (cost=3,790.61..3,835.50 rows=1 width=28) (actual time=4.379..4.623 rows=1 loops=7,226)

  • Join Filter: (x0.allocation_id = x1.id)
  • Rows Removed by Join Filter: 423
  • Buffers: shared hit=55532323
11. 1,199.516 32,899.978 ↑ 1.0 1 7,226

Nested Loop (cost=3,790.61..3,808.47 rows=1 width=24) (actual time=4.309..4.553 rows=1 loops=7,226)

  • Buffers: shared hit=55489985
12. 339.622 29,272.526 ↓ 168.0 336 7,226

Unique (cost=3,790.33..3,790.34 rows=2 width=4) (actual time=3.986..4.051 rows=336 loops=7,226)

  • Buffers: shared hit=48184504
13. 599.758 28,932.904 ↓ 168.0 336 7,226

Sort (cost=3,790.33..3,790.33 rows=2 width=4) (actual time=3.986..4.004 rows=336 loops=7,226)

  • Sort Key: "ANY_subquery".id
  • Sort Method: quicksort Memory: 40kB
  • Buffers: shared hit=48184504
14. 245.684 28,333.146 ↓ 168.0 336 7,226

Subquery Scan on ANY_subquery (cost=3,790.29..3,790.32 rows=2 width=4) (actual time=3.810..3.921 rows=336 loops=7,226)

  • Buffers: shared hit=48184501
15. 419.108 28,087.462 ↓ 168.0 336 7,226

Unique (cost=3,790.29..3,790.30 rows=2 width=24) (actual time=3.810..3.887 rows=336 loops=7,226)

  • Buffers: shared hit=48184501
16. 845.442 27,668.354 ↓ 177.5 355 7,226

Sort (cost=3,790.29..3,790.29 rows=2 width=24) (actual time=3.810..3.829 rows=355 loops=7,226)

  • Sort Key: w0.truck_id, w0.activated_at DESC, w0.start_datetime
  • Sort Method: quicksort Memory: 52kB
  • Buffers: shared hit=48184501
17. 0.000 26,822.912 ↓ 177.5 355 7,226

Nested Loop (cost=3,788.86..3,790.28 rows=2 width=24) (actual time=0.006..3.712 rows=355 loops=7,226)

  • Buffers: shared hit=48184501
18. 479.937 491.368 ↓ 369.0 369 7,226

HashAggregate (cost=3,788.58..3,788.59 rows=1 width=12) (actual time=0.002..0.068 rows=369 loops=7,226)

  • Group Key: v0.id
  • Buffers: shared hit=1533
19. 6.508 11.431 ↓ 369.0 369 1

Nested Loop Semi Join (cost=0.71..3,788.58 rows=1 width=12) (actual time=0.987..11.431 rows=369 loops=1)

  • Join Filter: (v0.id = u0.truck_id)
  • Rows Removed by Join Filter: 94095
  • Buffers: shared hit=1533
20. 0.083 0.083 ↑ 1.0 440 1

Index Only Scan using asset_truck_pkey on asset_truck v0 (cost=0.27..30.87 rows=440 width=4) (actual time=0.029..0.083 rows=440 loops=1)

  • Heap Fetches: 60
  • Buffers: shared hit=28
21. 3.830 4.840 ↓ 215.0 215 440

Materialize (cost=0.44..3,751.11 rows=1 width=8) (actual time=0.000..0.011 rows=215 loops=440)

  • Buffers: shared hit=1505
22. 0.078 1.010 ↓ 369.0 369 1

Nested Loop (cost=0.44..3,751.10 rows=1 width=8) (actual time=0.009..1.010 rows=369 loops=1)

  • Buffers: shared hit=1505
23. 0.052 0.052 ↑ 1.0 440 1

Seq Scan on asset_truck u1 (cost=0.00..33.40 rows=440 width=8) (actual time=0.002..0.052 rows=440 loops=1)

  • Buffers: shared hit=29
24. 0.880 0.880 ↑ 1.0 1 440

Index Scan using activity_truckposition_pkey on activity_truckposition u0 (cost=0.44..8.44 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=440)

  • Index Cond: (id = u1.current_position_id)
  • Filter: (u1.id = truck_id)
  • Buffers: shared hit=1476
25. 26,663.940 26,663.940 ↑ 1.0 1 2,666,394

Index Scan using asset_assignment_truck_id_f4699b05 on asset_assignment w0 (cost=0.28..1.68 rows=1 width=24) (actual time=0.004..0.010 rows=1 loops=2,666,394)

  • Index Cond: (truck_id = u0.truck_id)
  • Filter: ((activated_at IS NOT NULL) AND (NOT deleted) AND (end_datetime IS NULL))
  • Rows Removed by Filter: 19
  • Buffers: shared hit=48182968
26. 2,427.936 2,427.936 ↓ 0.0 0 2,427,936

Index Scan using asset_assignment_pkey on asset_assignment x0 (cost=0.28..8.30 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=2,427,936)

  • Index Cond: (id = "ANY_subquery".id)
  • Filter: (truck_id = activity_truckposition.truck_id)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=7305481
27. 258.024 258.024 ↑ 1.6 463 6,616

Seq Scan on activity_allocation x1 (cost=0.00..17.57 rows=757 width=4) (actual time=0.002..0.039 rows=463 loops=6,616)

  • Buffers: shared hit=42338
28. 13.232 13.232 ↓ 0.0 0 6,616

Index Only Scan using activity_allocation_plan_allocation_id_user_id_1cd6a85a_uniq on activity_allocation_users x2 (cost=0.28..0.39 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=6,616)

  • Index Cond: ((allocation_id = x1.id) AND (user_id = 11))
  • Heap Fetches: 40
  • Buffers: shared hit=13272
Planning time : 2.051 ms
Execution time : 33,455.447 ms