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,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,652,653,654,655,657,658,659,660,661,662,663,666,667,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,688,690,692,695,696,697,698,699,700,701,702,703,704,705,706,707,708,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,856,857,858,860,861,862,863,864,865,866,867,868,869,871,874,875,876,877,878,879,880,881,882,883,884,885,886,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,914,915,916,918,919,922,923,924,925,926,927,928,929,930,931,932,933,934,935,938,939,940,941,942,943,944,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,967,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000,1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016,1017,1018,1019,1020,1021,1022,1023,1024,1025,1026,1027,1028,1029,1031,1032,1033,1034,1035,1036,1037,1038,1039,1040,1041,1044,1045,1046}'::integer[]))
  • 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,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,652,653,654,655,657,658,659,660,661,662,663,666,667,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,688,690,692,695,696,697,698,699,700,701,702,703,704,705,706,707,708,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,856,857,858,860,861,862,863,864,865,866,867,868,869,871,874,875,876,877,878,879,880,881,882,883,884,885,886,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,914,915,916,918,919,922,923,924,925,926,927,928,929,930,931,932,933,934,935,938,939,940,941,942,943,944,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,967,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000,1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016,1017,1018,1019,1020,1021,1022,1023,1024,1025,1026,1027,1028,1029,1031,1032,1033,1034,1035,1036,1037,1038,1039,1040,1041,1044,1045,1046}'::integer[]))
  • 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