explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AbdS

Settings
# exclusive inclusive rows x rows loops node
1. 4.698 105,784.039 ↑ 1.0 1,000 1

Limit (cost=1,267.63..1,097,839.61 rows=1,000 width=113) (actual time=101,809.727..105,784.039 rows=1,000 loops=1)

2. 1.936 105,779.341 ↑ 2,140.3 1,000 1

Merge Semi Join (cost=1,267.63..2,346,953,716.14 rows=2,140,263 width=113) (actual time=101,809.725..105,779.341 rows=1,000 loops=1)

  • Merge Cond: (activity_truckposition.truck_id = u0.id)
3. 1,100.103 105,708.153 ↑ 2,140.3 1,000 1

Nested Loop (cost=1,267.08..2,346,276,543.32 rows=2,140,263 width=121) (actual time=101,740.428..105,708.153 rows=1,000 loops=1)

  • Join Filter: (asset_assignment.id = u0_2.id)
  • Rows Removed by Join Filter: 13,474,000
4. 1,114.185 103,952.050 ↑ 5,434.3 41,000 1

Gather Merge (cost=1,001.99..2,018,783,337.10 rows=222,805,710 width=125) (actual time=101,724.630..103,952.050 rows=41,000 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 0.000 102,837.865 ↑ 6,727.7 13,799 3 / 3

Nested Loop Semi Join (cost=1.97..1,993,065,029.78 rows=92,835,712 width=125) (actual time=101,639.814..102,837.865 rows=13,799 loops=3)

6. 14,930.738 18,259.153 ↑ 44.1 45,473,842 3 / 3

Merge Join (cost=1.41..31,607,135.65 rows=2,006,588,849 width=129) (actual time=0.155..18,259.153 rows=45,473,842 loops=3)

  • Merge Cond: (activity_truckposition.truck_id = asset_truck.id)
7. 198.287 198.287 ↑ 122.7 49,640 3 / 3

Parallel Index Scan using activity_tr_truck_i_239ba9_idx on activity_truckposition (cost=0.56..1,488,715.23 rows=6,089,202 width=109) (actual time=0.066..198.287 rows=49,640 loops=3)

8. 3,125.987 3,130.128 ↓ 245.6 45,479,135 3 / 3

Materialize (cost=0.85..4,973.68 rows=185,197 width=20) (actual time=0.086..3,130.128 rows=45,479,135 loops=3)

9. 2.701 4.141 ↑ 16.8 10,991 3 / 3

Merge Join (cost=0.85..4,510.69 rows=185,197 width=20) (actual time=0.081..4.141 rows=10,991 loops=3)

  • Merge Cond: (asset_truck.id = t4.truck_id)
10. 0.104 0.325 ↑ 25.7 397 3 / 3

Merge Join (cost=0.56..934.43 rows=10,202 width=12) (actual time=0.066..0.325 rows=397 loops=3)

  • Merge Cond: (asset_truck.id = asset_assignment.truck_id)
11. 0.058 0.058 ↑ 19.4 29 3 / 3

Index Only Scan using asset_truck_pkey on asset_truck (cost=0.28..32.70 rows=562 width=4) (actual time=0.047..0.058 rows=29 loops=3)

  • Heap Fetches: 1
12. 0.163 0.163 ↑ 25.7 397 3 / 3

Index Scan using asset_assignment_truck_id_f4699b05 on asset_assignment (cost=0.29..772.80 rows=10,202 width=8) (actual time=0.017..0.163 rows=397 loops=3)

13. 0.737 1.115 ↓ 1.1 10,991 3 / 3

Materialize (cost=0.29..798.30 rows=10,202 width=8) (actual time=0.013..1.115 rows=10,991 loops=3)

14. 0.378 0.378 ↑ 25.7 397 3 / 3

Index Scan using asset_assignment_truck_id_f4699b05 on asset_assignment t4 (cost=0.29..772.80 rows=10,202 width=8) (actual time=0.010..0.378 rows=397 loops=3)

15. 43,992.971 90,947.684 ↓ 0.0 0 136,421,526 / 3

Nested Loop (cost=0.56..0.97 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=136,421,526)

16. 45,473.842 45,473.842 ↓ 0.0 0 136,421,526 / 3

Index Scan using asset_assignment_pkey on asset_assignment u0_1 (cost=0.29..0.36 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=136,421,526)

  • Index Cond: (id = t4.id)
  • Filter: ((activated_at IS NOT NULL) AND (NOT deleted) AND (end_datetime IS NULL))
  • Rows Removed by Filter: 1
17. 1,480.871 1,480.871 ↓ 0.0 0 4,442,614 / 3

Index Scan using asset_driver_pkey on asset_driver u1_1 (cost=0.28..0.60 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=4,442,614)

  • Index Cond: (id = u0_1.driver1_id)
  • Filter: (next_weekendbreak_length = 'short'::text)
  • Rows Removed by Filter: 1
18. 646.813 656.000 ↓ 3.4 329 41,000

Materialize (cost=265.09..267.54 rows=98 width=4) (actual time=0.000..0.016 rows=329 loops=41,000)

19. 0.193 9.187 ↓ 3.4 335 1

HashAggregate (cost=265.09..266.07 rows=98 width=4) (actual time=9.146..9.187 rows=335 loops=1)

  • Group Key: u0_2.id
20. 0.308 8.994 ↓ 3.6 354 1

Nested Loop (cost=6.83..264.84 rows=98 width=4) (actual time=0.258..8.994 rows=354 loops=1)

  • Join Filter: (u0_2.driver1_id = u1_2.id)
21. 0.143 8.332 ↓ 2.9 354 1

Nested Loop (cost=6.55..225.81 rows=121 width=12) (actual time=0.221..8.332 rows=354 loops=1)

22. 0.166 1.125 ↓ 5.4 883 1

Nested Loop (cost=6.27..131.59 rows=165 width=4) (actual time=0.087..1.125 rows=883 loops=1)

23. 0.019 0.019 ↑ 1.0 1 1

Seq Scan on asset_certification u3_1 (cost=0.00..1.56 rows=1 width=4) (actual time=0.011..0.019 rows=1 loops=1)

  • Filter: (slug = 'adr'::text)
  • Rows Removed by Filter: 44
24. 0.887 0.940 ↓ 3.4 883 1

Bitmap Heap Scan on asset_drivercertification u2_1 (cost=6.27..127.47 rows=256 width=8) (actual time=0.072..0.940 rows=883 loops=1)

  • Recheck Cond: (certification_id = u3_1.id)
  • Heap Blocks: exact=115
25. 0.053 0.053 ↓ 3.4 883 1

Bitmap Index Scan on asset_drivercertification_certification_id_f0c9f17f (cost=0.00..6.20 rows=256 width=0) (actual time=0.053..0.053 rows=883 loops=1)

  • Index Cond: (certification_id = u3_1.id)
26. 7.064 7.064 ↓ 0.0 0 883

Index Scan using asset_assignment_driver1_id_fb485468 on asset_assignment u0_2 (cost=0.29..0.56 rows=1 width=8) (actual time=0.007..0.008 rows=0 loops=883)

  • Index Cond: (driver1_id = u2_1.driver_id)
  • Filter: ((activated_at IS NOT NULL) AND (NOT deleted) AND (end_datetime IS NULL))
  • Rows Removed by Filter: 11
27. 0.354 0.354 ↑ 1.0 1 354

Index Only Scan using asset_driver_pkey on asset_driver u1_2 (cost=0.28..0.31 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=354)

  • Index Cond: (id = u2_1.driver_id)
  • Heap Fetches: 101
28. 0.228 69.252 ↑ 28.7 356 1

Nested Loop (cost=0.55..284,163.30 rows=10,202 width=8) (actual time=0.366..69.252 rows=356 loops=1)

29. 0.204 68.668 ↑ 28.7 356 1

Nested Loop (cost=0.28..278,756.37 rows=10,202 width=16) (actual time=0.333..68.668 rows=356 loops=1)

  • Join Filter: (u3.entity_id = u4.id)
  • Rows Removed by Join Filter: 540
30. 1.677 68.464 ↑ 28.7 356 1

Nested Loop (cost=0.28..278,235.00 rows=10,202 width=12) (actual time=0.323..68.464 rows=356 loops=1)

  • Join Filter: (u2.contract_id = u3.id)
  • Rows Removed by Join Filter: 19,954
31. 17.679 65.719 ↑ 28.7 356 1

Nested Loop (cost=0.28..261,689.26 rows=10,202 width=12) (actual time=0.270..65.719 rows=356 loops=1)

  • Join Filter: (u1.allocation_id = u2.id)
  • Rows Removed by Join Filter: 226,897
32. 21.356 37.004 ↑ 28.7 356 1

Nested Loop (cost=0.28..86,288.09 rows=10,202 width=12) (actual time=0.085..37.004 rows=356 loops=1)

  • Join Filter: (u0.id = u1.truck_id)
  • Rows Removed by Join Filter: 275,420
33. 0.024 0.024 ↑ 20.1 28 1

Index Only Scan using asset_truck_pkey on asset_truck u0 (cost=0.28..32.70 rows=562 width=4) (actual time=0.019..0.024 rows=28 loops=1)

  • Heap Fetches: 1
34. 13.906 15.624 ↑ 1.0 9,849 28

Materialize (cost=0.00..278.03 rows=10,202 width=8) (actual time=0.001..0.558 rows=9,849 loops=28)

35. 1.718 1.718 ↑ 1.0 10,202 1

Seq Scan on asset_assignment u1 (cost=0.00..227.02 rows=10,202 width=8) (actual time=0.004..1.718 rows=10,202 loops=1)

36. 10.772 11.036 ↑ 1.8 638 356

Materialize (cost=0.00..33.20 rows=1,147 width=8) (actual time=0.000..0.031 rows=638 loops=356)

37. 0.264 0.264 ↑ 1.0 1,142 1

Seq Scan on activity_allocation u2 (cost=0.00..27.47 rows=1,147 width=8) (actual time=0.007..0.264 rows=1,142 loops=1)

38. 0.994 1.068 ↑ 1.9 57 356

Materialize (cost=0.00..16.64 rows=109 width=8) (actual time=0.000..0.003 rows=57 loops=356)

39. 0.074 0.074 ↑ 1.0 106 1

Seq Scan on customer_contract u3 (cost=0.00..16.09 rows=109 width=8) (actual time=0.008..0.074 rows=106 loops=1)

40. 0.000 0.000 ↑ 1.3 3 356

Materialize (cost=0.00..1.06 rows=4 width=4) (actual time=0.000..0.000 rows=3 loops=356)

41. 0.006 0.006 ↑ 1.0 4 1

Seq Scan on customer_entity u4 (cost=0.00..1.04 rows=4 width=4) (actual time=0.006..0.006 rows=4 loops=1)

42. 0.356 0.356 ↑ 1.0 1 356

Index Only Scan using customer_entity_users_entity_id_user_id_cdc6b5d7_uniq on customer_entity_users u5 (cost=0.28..0.59 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=356)

  • Index Cond: ((entity_id = u3.entity_id) AND (user_id = 254))
  • Heap Fetches: 356
Planning time : 7.550 ms
Execution time : 105,784.785 ms