explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ikxp

Settings
# exclusive inclusive rows x rows loops node
1. 0.029 2,580.703 ↑ 12.5 16 1

Sort (cost=13,178,612.98..13,178,613.48 rows=200 width=92) (actual time=2,580.702..2,580.703 rows=16 loops=1)

  • Sort Key: dst.week_start
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=125954, temp read=10557 written=10557
2.          

CTE weeks

3. 0.009 0.029 ↑ 62.5 16 1

Subquery Scan on d (cost=0.00..17.52 rows=1,000 width=16) (actual time=0.012..0.029 rows=16 loops=1)

4. 0.019 0.020 ↑ 62.5 16 1

ProjectSet (cost=0.00..5.03 rows=1,000 width=8) (actual time=0.011..0.020 rows=16 loops=1)

5. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)

6.          

CTE weekly_availability_based_driver_status

7. 4.211 84.035 ↑ 62.5 14,496 1

Sort (cost=8,639,607.36..8,641,872.36 rows=906,000 width=32) (actual time=83.379..84.035 rows=14,496 loops=1)

  • Sort Key: w.week_start
  • Sort Method: quicksort Memory: 1517kB
  • Buffers: shared hit=125842
8. 14.626 79.824 ↑ 62.5 14,496 1

Nested Loop Left Join (cost=464.85..8,528,283.50 rows=906,000 width=32) (actual time=2.100..79.824 rows=14,496 loops=1)

  • Buffers: shared hit=125842
9. 0.037 0.037 ↑ 62.5 16 1

CTE Scan on weeks w (cost=0.00..20.00 rows=1,000 width=8) (actual time=0.014..0.037 rows=16 loops=1)

10. 5.040 51.312 ↑ 1.0 906 16

Merge Left Join (cost=464.85..992.61 rows=906 width=48) (actual time=1.507..3.207 rows=906 loops=16)

  • Merge Cond: (d_1.id = av.driver_id)
  • Buffers: shared hit=84179
11. 19.360 19.360 ↑ 1.0 906 16

Index Scan using asset_driver_pkey on asset_driver d_1 (cost=0.28..517.67 rows=906 width=12) (actual time=0.006..1.210 rows=906 loops=16)

  • Index Cond: (id = ANY ('{104,105,106,107,108,109,111,112,113,114,115,116,117,119,120,121,122,123,124,126,127,128,129,130,131,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,1
  • Buffers: shared hit=41523
12. 2.080 26.912 ↓ 2.9 866 16

Unique (cost=464.58..466.09 rows=302 width=36) (actual time=1.500..1.682 rows=866 loops=16)

  • Buffers: shared hit=42656
13. 3.120 24.832 ↓ 2.9 866 16

Sort (cost=464.58..465.33 rows=302 width=36) (actual time=1.500..1.552 rows=866 loops=16)

  • Sort Key: av.driver_id, av.arrival_datetime DESC
  • Sort Method: quicksort Memory: 95kB
  • Buffers: shared hit=42656
14. 21.712 21.712 ↓ 2.9 866 16

Index Scan using asset_driveravailability_driver_id_daf1687b on asset_driveravailability av (cost=0.28..452.14 rows=302 width=36) (actual time=0.004..1.357 rows=866 loops=16)

  • Index Cond: (driver_id = ANY ('{104,105,106,107,108,109,111,112,113,114,115,116,117,119,120,121,122,123,124,126,127,128,129,130,131,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,15
  • Filter: (arrival_datetime <= (w.week_start + '3 days'::interval))
  • Rows Removed by Filter: 40
  • Buffers: shared hit=42656
15.          

SubPlan (for Nested Loop Left Join)

16. 13.849 13.849 ↓ 0.0 0 13,849

Index Scan using asset_driveravailability_driver_id_daf1687b on asset_driveravailability (cost=0.28..8.30 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=13,849)

  • Index Cond: (driver_id = d_1.id)
  • Filter: (arrival_datetime > (w.week_start + '3 days'::interval))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=41663
17.          

CTE weekly_extrapolated_driver_status

18. 3.473 117.365 ↑ 5.5 14,496 1

Unique (cost=183,737.11..185,136.08 rows=79,941 width=47) (actual time=113.222..117.365 rows=14,496 loops=1)

  • Buffers: shared hit=125873
19. 6.405 113.892 ↑ 5.5 14,496 1

Sort (cost=183,737.11..183,936.96 rows=79,941 width=47) (actual time=113.221..113.892 rows=14,496 loops=1)

  • Sort Key: "*SELECT* 1".week_start, "*SELECT* 1".driver_id, "*SELECT* 1".working, "*SELECT* 1".confirmed_at_home, "*SELECT* 1".confirmed_working, "*SELECT* 1".driver_status
  • Sort Method: quicksort Memory: 1517kB
  • Buffers: shared hit=125873
20. 0.900 107.487 ↑ 5.5 14,496 1

Append (cost=112,977.09..174,766.76 rows=79,941 width=47) (actual time=87.456..107.487 rows=14,496 loops=1)

  • Buffers: shared hit=125865
21. 0.091 87.674 ↑ 54.6 733 1

Subquery Scan on *SELECT* 1 (cost=112,977.09..118,473.34 rows=40,000 width=21) (actual time=87.456..87.674 rows=733 loops=1)

  • Buffers: shared hit=125855
22. 0.096 87.583 ↑ 54.6 733 1

Unique (cost=112,977.09..118,073.34 rows=40,000 width=25) (actual time=87.455..87.583 rows=733 loops=1)

  • Buffers: shared hit=125855
23. 0.197 87.487 ↑ 927.0 733 1

Sort (cost=112,977.09..114,675.84 rows=679,500 width=25) (actual time=87.454..87.487 rows=733 loops=1)

  • Sort Key: s.week_start, s.driver_id, c.date DESC
  • Sort Method: quicksort Memory: 82kB
  • Buffers: shared hit=125855
24. 0.352 87.290 ↑ 927.0 733 1

Hash Left Join (cost=31.92..30,892.54 rows=679,500 width=25) (actual time=83.687..87.290 rows=733 loops=1)

  • Hash Cond: (s.driver_id = c.driver_id)
  • Join Filter: (c.date <= (s.week_start + '3 days'::interval))
  • Buffers: shared hit=125852
25. 86.646 86.646 ↑ 927.0 733 1

CTE Scan on weekly_availability_based_driver_status s (cost=0.00..18,120.00 rows=679,500 width=15) (actual time=83.382..86.646 rows=733 loops=1)

  • Filter: (working OR (NOT extrapolate))
  • Rows Removed by Filter: 13763
  • Buffers: shared hit=125842
26. 0.130 0.292 ↑ 1.0 974 1

Hash (cost=19.74..19.74 rows=974 width=14) (actual time=0.292..0.292 rows=974 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
  • Buffers: shared hit=10
27. 0.162 0.162 ↑ 1.0 974 1

Seq Scan on asset_driverstatuschange c (cost=0.00..19.74 rows=974 width=14) (actual time=0.016..0.162 rows=974 loops=1)

  • Buffers: shared hit=10
28. 1.707 18.913 ↑ 2.9 13,763 1

Subquery Scan on *SELECT* 2 (cost=54,195.25..56,293.41 rows=39,941 width=21) (actual time=14.738..18.913 rows=13,763 loops=1)

  • Buffers: shared hit=10
29. 1.847 17.206 ↑ 2.9 13,763 1

Unique (cost=54,195.25..55,894.00 rows=39,941 width=25) (actual time=14.737..17.206 rows=13,763 loops=1)

  • Buffers: shared hit=10
30. 3.350 15.359 ↑ 16.5 13,763 1

Sort (cost=54,195.25..54,761.50 rows=226,500 width=25) (actual time=14.736..15.359 rows=13,763 loops=1)

  • Sort Key: s_1.week_start, s_1.driver_id, c_1.date DESC
  • Sort Method: quicksort Memory: 1460kB
  • Buffers: shared hit=10
31. 9.878 12.009 ↑ 16.5 13,763 1

Hash Left Join (cost=31.92..28,627.54 rows=226,500 width=25) (actual time=0.303..12.009 rows=13,763 loops=1)

  • Hash Cond: (s_1.driver_id = c_1.driver_id)
  • Join Filter: (date_trunc('week'::text, (c_1.date)::timestamp with time zone) <= s_1.week_start)
  • Buffers: shared hit=10
32. 1.846 1.846 ↑ 16.5 13,763 1

CTE Scan on weekly_availability_based_driver_status s_1 (cost=0.00..18,120.00 rows=226,500 width=29) (actual time=0.001..1.846 rows=13,763 loops=1)

  • Filter: ((NOT working) AND extrapolate)
  • Rows Removed by Filter: 733
33. 0.141 0.285 ↑ 1.0 974 1

Hash (cost=19.74..19.74 rows=974 width=14) (actual time=0.285..0.285 rows=974 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
  • Buffers: shared hit=10
34. 0.144 0.144 ↑ 1.0 974 1

Seq Scan on asset_driverstatuschange c_1 (cost=0.00..19.74 rows=974 width=14) (actual time=0.007..0.144 rows=974 loops=1)

  • Buffers: shared hit=10
35.          

CTE weekly_allocation_truck_targets

36. 645.151 2,451.987 ↑ 12.5 16 1

GroupAggregate (cost=4,151,043.77..4,344,174.34 rows=200 width=24) (actual time=1,642.629..2,451.987 rows=16 loops=1)

  • Group Key: w_1.week_start
  • Buffers: shared hit=78, temp read=10557 written=10557
37. 1,140.056 1,806.836 ↑ 7.6 2,542,400 1

Sort (cost=4,151,043.77..4,199,325.91 rows=19,312,857 width=21) (actual time=1,588.708..1,806.836 rows=2,542,400 loops=1)

  • Sort Key: w_1.week_start
  • Sort Method: external sort Disk: 84456kB
  • Buffers: shared hit=78, temp read=10557 written=10557
38. 546.725 666.780 ↑ 7.6 2,542,400 1

Nested Loop Left Join (cost=14.94..1,021,758.75 rows=19,312,857 width=21) (actual time=0.155..666.780 rows=2,542,400 loops=1)

  • Join Filter: ((a.start_date <= w_1.week_end) AND ((a.end_date IS NULL) OR (a.end_date >= w_1.week_start)))
  • Buffers: shared hit=78
39. 1.585 2.455 ↑ 22.8 5,600 1

Nested Loop Left Join (cost=0.00..7,773.10 rows=127,618 width=20) (actual time=0.040..2.455 rows=5,600 loops=1)

  • Join Filter: ((t.start_date <= w_1.week_end) AND ((t.exit_date IS NULL) OR (t.exit_date >= w_1.week_start)))
  • Rows Removed by Join Filter: 1456
  • Buffers: shared hit=30
40. 0.022 0.022 ↑ 62.5 16 1

CTE Scan on weeks w_1 (cost=0.00..20.00 rows=1,000 width=16) (actual time=0.001..0.022 rows=16 loops=1)

41. 0.587 0.848 ↑ 1.0 441 16

Materialize (cost=0.00..36.70 rows=441 width=12) (actual time=0.002..0.053 rows=441 loops=16)

  • Buffers: shared hit=30
42. 0.261 0.261 ↑ 1.0 441 1

Seq Scan on asset_truck t (cost=0.00..34.50 rows=441 width=12) (actual time=0.024..0.261 rows=441 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 9
  • Buffers: shared hit=30
43. 117.250 117.600 ↑ 1.0 454 5,600

Materialize (cost=14.94..61.77 rows=454 width=17) (actual time=0.000..0.021 rows=454 loops=5,600)

  • Buffers: shared hit=48
44. 0.089 0.350 ↑ 1.0 454 1

Hash Left Join (cost=14.94..59.50 rows=454 width=17) (actual time=0.113..0.350 rows=454 loops=1)

  • Hash Cond: (a.contract_id = c_2.id)
  • Buffers: shared hit=48
45. 0.186 0.186 ↑ 1.0 454 1

Seq Scan on activity_allocation a (cost=0.00..43.32 rows=454 width=16) (actual time=0.032..0.186 rows=454 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 378
  • Buffers: shared hit=35
46. 0.011 0.075 ↑ 1.0 75 1

Hash (cost=14.00..14.00 rows=75 width=5) (actual time=0.075..0.075 rows=75 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=13
47. 0.064 0.064 ↑ 1.0 75 1

Seq Scan on customer_contract c_2 (cost=0.00..14.00 rows=75 width=5) (actual time=0.005..0.064 rows=75 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 25
  • Buffers: shared hit=13
48. 5.391 2,580.674 ↑ 12.5 16 1

HashAggregate (cost=7,401.04..7,405.04 rows=200 width=92) (actual time=2,580.659..2,580.674 rows=16 loops=1)

  • Group Key: dst.week_start, tgt.week_start
  • Buffers: shared hit=125951, temp read=10557 written=10557
49. 3.481 2,575.283 ↑ 5.5 14,496 1

Hash Join (cost=6.50..4,603.11 rows=79,941 width=71) (actual time=2,565.323..2,575.283 rows=14,496 loops=1)

  • Hash Cond: (dst.week_start = tgt.week_start)
  • Buffers: shared hit=125951, temp read=10557 written=10557
50. 119.717 119.717 ↑ 5.5 14,496 1

CTE Scan on weekly_extrapolated_driver_status dst (cost=0.00..1,598.82 rows=79,941 width=47) (actual time=113.224..119.717 rows=14,496 loops=1)

  • Buffers: shared hit=125873
51. 0.034 2,452.085 ↑ 12.5 16 1

Hash (cost=4.00..4.00 rows=200 width=24) (actual time=2,452.085..2,452.085 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=78, temp read=10557 written=10557
52. 2,452.051 2,452.051 ↑ 12.5 16 1

CTE Scan on weekly_allocation_truck_targets tgt (cost=0.00..4.00 rows=200 width=24) (actual time=1,642.633..2,452.051 rows=16 loops=1)

  • Buffers: shared hit=78, temp read=10557 written=10557
Planning time : 5.455 ms
Execution time : 2,594.238 ms