explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4tBd

Settings
# exclusive inclusive rows x rows loops node
1. 1.042 113,022.583 ↓ 182.0 364 1

Sort (cost=69,772.56..69,772.56 rows=2 width=507) (actual time=113,022.536..113,022.583 rows=364 loops=1)

  • Sort Key: orders.current_jobdate DESC, container_classes.container_class, container_types.container_type, container_sizes.container_size, ((NULLIF(container_periods.date_start, '-infinity'::timestamp without time zone))::date) DESC
  • Sort Method: quicksort Memory: 251kB
2.          

Initplan (forSort)

3. 0.000 0.000 ↓ 0.0 0

Seq Scan on options (cost=0.00..4.00 rows=1 width=23) (never executed)

  • Filter: ((option_name)::text = 'use_container_ids'::text)
4. 87.718 113,021.541 ↓ 182.0 364 1

Nested Loop Left Join (cost=61,401.35..69,768.55 rows=2 width=507) (actual time=1,434.032..113,021.541 rows=364 loops=1)

5. 0.869 112,932.003 ↓ 182.0 364 1

Nested Loop Left Join (cost=61,401.07..69,723.99 rows=2 width=301) (actual time=1,433.724..112,932.003 rows=364 loops=1)

6. 0.639 112,928.950 ↓ 182.0 364 1

Nested Loop Left Join (cost=61,400.78..69,723.36 rows=2 width=297) (actual time=1,433.711..112,928.950 rows=364 loops=1)

7. 16,495.974 112,923.943 ↓ 182.0 364 1

Nested Loop (cost=61,399.93..69,721.30 rows=2 width=281) (actual time=1,433.692..112,923.943 rows=364 loops=1)

  • Join Filter: ("*SELECT* 1".order_id = history.order_id)
  • Rows Removed by Join Filter: 132683220
8. 1.639 37,343.137 ↓ 928.0 928 1

Nested Loop Left Join (cost=29.89..5,619.54 rows=1 width=277) (actual time=140.313..37,343.137 rows=928 loops=1)

9. 2.448 37,335.930 ↓ 928.0 928 1

Nested Loop Left Join (cost=29.76..5,619.38 rows=1 width=280) (actual time=140.309..37,335.930 rows=928 loops=1)

10. 791.373 37,333.482 ↓ 928.0 928 1

Nested Loop Left Join (cost=29.48..5,619.06 rows=1 width=275) (actual time=140.307..37,333.482 rows=928 loops=1)

  • Join Filter: (vehicle_periods.vehicle_id = driversheets.end_vehicle_id)
  • Rows Removed by Join Filter: 5697920
11. 2.292 35,883.229 ↓ 928.0 928 1

Nested Loop Left Join (cost=29.48..5,420.94 rows=1 width=271) (actual time=138.775..35,883.229 rows=928 loops=1)

  • Join Filter: (driversheets.jobdate = orders.current_jobdate)
  • Rows Removed by Join Filter: 2
12. 1.750 35,876.297 ↓ 928.0 928 1

Nested Loop Left Join (cost=29.19..5,419.44 rows=1 width=267) (actual time=138.766..35,876.297 rows=928 loops=1)

13. 1.975 35,874.547 ↓ 928.0 928 1

Nested Loop Left Join (cost=29.06..5,419.28 rows=1 width=270) (actual time=138.764..35,874.547 rows=928 loops=1)

14. 1.752 35,841.020 ↓ 928.0 928 1

Nested Loop Left Join (cost=28.08..5,417.85 rows=1 width=217) (actual time=138.739..35,841.020 rows=928 loops=1)

15. 1.440 35,839.268 ↓ 928.0 928 1

Nested Loop Left Join (cost=27.79..5,409.53 rows=1 width=205) (actual time=138.737..35,839.268 rows=928 loops=1)

16. 1.325 35,831.332 ↓ 928.0 928 1

Nested Loop Left Join (cost=27.37..5,406.19 rows=1 width=201) (actual time=138.730..35,831.332 rows=928 loops=1)

17. 1.813 35,595.223 ↓ 928.0 928 1

Nested Loop Left Join (cost=26.69..5,347.36 rows=1 width=197) (actual time=138.483..35,595.223 rows=928 loops=1)

  • Join Filter: (orders.customer_id = container_periods.customer_id)
18. 4.057 35,249.122 ↓ 928.0 928 1

Nested Loop (cost=25.43..4,803.53 rows=1 width=138) (actual time=138.120..35,249.122 rows=928 loops=1)

19. 2.604 35,242.281 ↓ 928.0 928 1

Nested Loop (cost=25.43..4,802.47 rows=1 width=110) (actual time=138.106..35,242.281 rows=928 loops=1)

20. 233.161 35,229.469 ↓ 928.0 928 1

Nested Loop (cost=25.01..4,798.16 rows=1 width=86) (actual time=138.090..35,229.469 rows=928 loops=1)

  • Join Filter: (containers.container_id = "*SELECT* 1".container_id)
  • Rows Removed by Join Filter: 1558432
21. 1.808 9.054 ↓ 886.0 886 1

Nested Loop (cost=1.31..45.16 rows=1 width=49) (actual time=0.420..9.054 rows=886 loops=1)

  • Join Filter: (containers.container_type_id = container_types.container_type_id)
  • Rows Removed by Join Filter: 6202
22. 0.030 0.030 ↓ 8.0 8 1

Seq Scan on container_types (cost=0.00..1.36 rows=1 width=40) (actual time=0.020..0.030 rows=8 loops=1)

  • Filter: (container_class_id = 3)
  • Rows Removed by Filter: 21
23. 3.372 7.216 ↓ 23.9 886 8

Hash Join (cost=1.31..43.33 rows=37 width=21) (actual time=0.012..0.902 rows=886 loops=8)

  • Hash Cond: (containers.container_size_id = container_sizes.container_size_id)
24. 3.832 3.832 ↑ 1.0 886 8

Seq Scan on containers (cost=0.00..38.33 rows=886 width=21) (actual time=0.009..0.479 rows=886 loops=8)

  • Filter: (container_class_id = 3)
  • Rows Removed by Filter: 1060
25. 0.004 0.012 ↓ 8.0 8 1

Hash (cost=1.30..1.30 rows=1 width=12) (actual time=0.012..0.012 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.008 0.008 ↓ 8.0 8 1

Seq Scan on container_sizes (cost=0.00..1.30 rows=1 width=12) (actual time=0.006..0.008 rows=8 loops=1)

  • Filter: (container_class_id = 3)
  • Rows Removed by Filter: 16
27. 129.356 34,987.254 ↑ 1.1 1,760 886

Append (cost=23.70..4,729.85 rows=1,852 width=41) (actual time=0.007..39.489 rows=1,760 loops=886)

28. 91.258 34,698.418 ↓ 1.0 832 886

Subquery Scan on *SELECT* 1 (cost=23.70..4,713.64 rows=831 width=41) (actual time=0.007..39.163 rows=832 loops=886)

29. 17,235.932 34,607.160 ↓ 1.0 832 886

Hash Right Join (cost=23.70..4,705.33 rows=831 width=41) (actual time=0.006..39.060 rows=832 loops=886)

  • Hash Cond: (o.order_id = cos.order_parent_id)
30. 17,370.916 17,370.916 ↑ 1.0 160,149 886

Seq Scan on orders o (cost=0.00..4,072.69 rows=160,169 width=5) (actual time=0.004..19.606 rows=160,149 loops=886)

31. 0.165 0.312 ↓ 1.0 832 1

Hash (cost=13.31..13.31 rows=831 width=12) (actual time=0.312..0.312 rows=832 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
32. 0.147 0.147 ↓ 1.0 832 1

Seq Scan on containers_on_site cos (cost=0.00..13.31 rows=831 width=12) (actual time=0.013..0.147 rows=832 loops=1)

33. 159.480 159.480 ↑ 1.1 928 886

Seq Scan on bins_on_site (cost=0.00..16.21 rows=1,021 width=41) (actual time=0.007..0.180 rows=928 loops=886)

34. 10.208 10.208 ↑ 1.0 1 928

Index Scan using orders_pkey on orders (cost=0.42..4.30 rows=1 width=24) (actual time=0.010..0.011 rows=1 loops=928)

  • Index Cond: (order_id = "*SELECT* 1".order_id)
35. 2.784 2.784 ↑ 1.0 1 928

Seq Scan on container_classes (cost=0.00..1.05 rows=1 width=36) (actual time=0.002..0.003 rows=1 loops=928)

  • Filter: (container_class_id = 3)
  • Rows Removed by Filter: 3
36. 0.928 344.288 ↓ 0.0 0 928

Nested Loop Left Join (cost=1.26..543.81 rows=1 width=63) (actual time=0.371..0.371 rows=0 loops=928)

37. 0.000 343.360 ↓ 0.0 0 928

Nested Loop (cost=0.28..534.38 rows=1 width=31) (actual time=0.370..0.370 rows=0 loops=928)

  • Join Filter: (containers_1.container_class_id = container_classes_1.container_class_id)
38. 0.928 343.360 ↓ 0.0 0 928

Nested Loop (cost=0.28..533.29 rows=1 width=43) (actual time=0.370..0.370 rows=0 loops=928)

  • Join Filter: ((containers_1.container_class_id = container_types_1.container_class_id) AND (containers_1.container_type_id = container_types_1.container_type_id))
39. 0.928 342.432 ↓ 0.0 0 928

Nested Loop (cost=0.28..531.57 rows=1 width=43) (actual time=0.369..0.369 rows=0 loops=928)

  • Join Filter: ((containers_1.container_size_id = container_sizes_1.container_size_id) AND (containers_1.container_class_id = container_sizes_1.container_class_id))
40. 3.712 341.504 ↓ 0.0 0 928

Nested Loop (cost=0.28..529.97 rows=1 width=43) (actual time=0.368..0.368 rows=0 loops=928)

  • Join Filter: (containers_1.container_id = container_periods.container_id)
  • Rows Removed by Join Filter: 5
41. 4.640 4.640 ↑ 1.0 1 928

Index Scan using containers_pkey on containers containers_1 (cost=0.28..0.39 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=928)

  • Index Cond: (containers.container_id = container_id)
42. 333.152 333.152 ↓ 5.0 5 928

Seq Scan on container_periods (cost=0.00..529.56 rows=1 width=31) (actual time=0.352..0.359 rows=5 loops=928)

  • Filter: ((we_pay IS FALSE) AND (NOT is_invoiced) AND (customer_id <> the_company()))
  • Rows Removed by Filter: 1945
43. 0.000 0.000 ↓ 0.0 0

Seq Scan on container_sizes container_sizes_1 (cost=0.00..1.24 rows=24 width=8) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Seq Scan on container_types container_types_1 (cost=0.00..1.29 rows=29 width=8) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Seq Scan on container_classes container_classes_1 (cost=0.00..1.04 rows=4 width=4) (never executed)

46. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.98..9.42 rows=1 width=36) (never executed)

47. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..9.01 rows=1 width=24) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.57..8.67 rows=1 width=28) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Index Scan using customers_pkey on customers c (cost=0.29..8.30 rows=1 width=24) (never executed)

  • Index Cond: (customer_id = container_periods.customer_id)
50. 0.000 0.000 ↓ 0.0 0

Index Scan using unique_customer_id_address_id on customer_addresses ca_1 (cost=0.29..0.36 rows=1 width=16) (never executed)

  • Index Cond: (customer_id = c.customer_id)
  • Filter: is_main
51. 0.000 0.000 ↓ 0.0 0

Index Only Scan using addresses_pkey on addresses a (cost=0.29..0.33 rows=1 width=4) (never executed)

  • Index Cond: (address_id = ca_1.address_id)
  • Heap Fetches: 0
52. 0.000 0.000 ↓ 0.0 0

Index Scan using watch_categories_pkey on watch_categories cwc (cost=0.12..0.14 rows=1 width=57) (never executed)

  • Index Cond: (watch_category_id = c.watch_category_id)
53. 2.784 234.784 ↓ 0.0 0 928

Nested Loop (cost=0.68..58.82 rows=1 width=8) (actual time=0.253..0.253 rows=0 loops=928)

  • Join Filter: (containers_2.container_id = container_periods_1.container_id)
54. 1.856 17.632 ↑ 1.0 1 928

Nested Loop (cost=0.68..0.91 rows=1 width=4) (actual time=0.017..0.019 rows=1 loops=928)

55. 1.856 12.992 ↑ 1.0 1 928

Nested Loop (cost=0.55..0.74 rows=1 width=16) (actual time=0.013..0.014 rows=1 loops=928)

56. 1.856 8.352 ↑ 1.0 1 928

Nested Loop (cost=0.42..0.57 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=928)

57. 2.784 2.784 ↑ 1.0 1 928

Index Scan using containers_pkey on containers containers_2 (cost=0.28..0.39 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=928)

  • Index Cond: (containers.container_id = container_id)
58. 3.712 3.712 ↑ 1.0 1 928

Index Scan using container_sizes_pkey on container_sizes container_sizes_2 (cost=0.14..0.16 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=928)

  • Index Cond: (container_size_id = containers_2.container_size_id)
  • Filter: (containers_2.container_class_id = container_class_id)
59. 2.784 2.784 ↑ 1.0 1 928

Index Scan using container_types_pkey on container_types container_types_2 (cost=0.14..0.16 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=928)

  • Index Cond: (container_type_id = containers_2.container_type_id)
  • Filter: (containers_2.container_class_id = container_class_id)
60. 2.784 2.784 ↑ 1.0 1 928

Index Only Scan using container_classes_pkey on container_classes container_classes_2 (cost=0.13..0.15 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=928)

  • Index Cond: (container_class_id = containers_2.container_class_id)
  • Heap Fetches: 928
61. 214.368 214.368 ↓ 0.0 0 928

Seq Scan on container_periods container_periods_1 (cost=0.00..57.90 rows=1 width=8) (actual time=0.231..0.231 rows=0 loops=928)

  • Filter: (we_pay AND (customer_id <> 1) AND (date_end > ('now'::cstring)::date))
  • Rows Removed by Filter: 1950
62. 6.496 6.496 ↑ 1.0 1 928

Index Only Scan using order_itinerary_main_idx on order_itinerary (cost=0.42..3.34 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=928)

  • Index Cond: (order_id = "*SELECT* 1".order_id)
  • Heap Fetches: 928
63. 0.000 0.000 ↓ 0.0 0 928

Index Scan using customers_pkey on customers (cost=0.29..8.30 rows=1 width=20) (actual time=0.000..0.000 rows=0 loops=928)

  • Index Cond: (container_periods_1.customer_id = customer_id)
64. 2.784 31.552 ↑ 1.0 1 928

Nested Loop Left Join (cost=0.98..1.43 rows=1 width=57) (actual time=0.022..0.034 rows=1 loops=928)

65. 1.856 27.840 ↑ 1.0 1 928

Nested Loop (cost=0.86..1.02 rows=1 width=29) (actual time=0.018..0.030 rows=1 loops=928)

66. 1.856 21.344 ↑ 1.0 1 928

Nested Loop (cost=0.57..0.68 rows=1 width=33) (actual time=0.013..0.023 rows=1 loops=928)

67. 6.496 6.496 ↑ 1.0 1 928

Index Scan using customers_pkey on customers c_1 (cost=0.29..0.31 rows=1 width=29) (actual time=0.006..0.007 rows=1 loops=928)

  • Index Cond: (customer_id = orders.customer_id)
68. 12.992 12.992 ↑ 1.0 1 928

Index Scan using unique_customer_id_address_id on customer_addresses ca_2 (cost=0.29..0.36 rows=1 width=16) (actual time=0.006..0.014 rows=1 loops=928)

  • Index Cond: (customer_id = c_1.customer_id)
  • Filter: is_main
  • Rows Removed by Filter: 6
69. 4.640 4.640 ↑ 1.0 1 928

Index Only Scan using addresses_pkey on addresses a_1 (cost=0.29..0.33 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=928)

  • Index Cond: (address_id = ca_2.address_id)
  • Heap Fetches: 0
70. 0.928 0.928 ↓ 0.0 0 928

Index Scan using watch_categories_pkey on watch_categories cwc_1 (cost=0.12..0.14 rows=1 width=57) (actual time=0.001..0.001 rows=0 loops=928)

  • Index Cond: (watch_category_id = c_1.watch_category_id)
71. 0.000 0.000 ↓ 0.0 0 928

Index Scan using subcontracted_skips_order_id on subcontracted_skips (cost=0.14..0.15 rows=1 width=5) (actual time=0.000..0.000 rows=0 loops=928)

  • Index Cond: (order_id = orders.order_parent_id)
72. 4.640 4.640 ↑ 1.0 1 928

Index Scan using driversheets_order_id_idx on driversheets (cost=0.29..1.48 rows=1 width=12) (actual time=0.004..0.005 rows=1 loops=928)

  • Index Cond: (order_id = "*SELECT* 1".order_id)
73. 658.880 658.880 ↓ 1.0 6,140 928

Seq Scan on vehicle_periods (cost=0.00..121.39 rows=6,139 width=8) (actual time=0.005..0.710 rows=6,140 loops=928)

74. 0.000 0.000 ↓ 0.0 0 928

Index Scan using vehicles_pkey on vehicles (cost=0.28..0.31 rows=1 width=13) (actual time=0.000..0.000 rows=0 loops=928)

  • Index Cond: (vehicle_id = driversheets.end_vehicle_id)
75. 5.568 5.568 ↑ 1.0 1 928

Index Scan using currencies_pkey on currencies (cost=0.13..0.15 rows=1 width=5) (actual time=0.003..0.006 rows=1 loops=928)

  • Index Cond: (c_1.currency_id = currency_id)
76. 24,543.744 59,084.832 ↓ 3.0 142,978 928

Subquery Scan on history (cost=61,370.03..63,507.91 rows=47,508 width=8) (actual time=0.766..63.669 rows=142,978 loops=928)

  • Filter: (history.status_update <= '2019-01-03'::date)
77. 33,999.602 34,541.088 ↓ 1.0 142,978 928

Sort (cost=61,370.03..61,726.35 rows=142,525 width=12) (actual time=0.765..37.221 rows=142,978 loops=928)

  • Sort Key: order_history.order_id DESC
  • Sort Method: external sort Disk: 3296kB
78. 100.068 541.486 ↓ 1.0 142,978 1

WindowAgg (cost=43,882.78..46,733.28 rows=142,525 width=12) (actual time=416.165..541.486 rows=142,978 loops=1)

79. 101.381 441.418 ↓ 1.0 142,978 1

Sort (cost=43,882.78..44,239.10 rows=142,525 width=16) (actual time=416.127..441.418 rows=142,978 loops=1)

  • Sort Key: orders_1.bin_contract_container_id
  • Sort Method: external merge Disk: 3632kB
80. 104.295 340.037 ↓ 1.0 142,978 1

Hash Left Join (cost=6,700.80..29,246.03 rows=142,525 width=16) (actual time=80.077..340.037 rows=142,978 loops=1)

  • Hash Cond: (order_history.order_id = orders_1.order_id)
81. 156.706 156.706 ↓ 1.0 142,978 1

Seq Scan on order_history (cost=0.00..18,567.51 rows=142,525 width=12) (actual time=0.847..156.706 rows=142,978 loops=1)

  • Filter: (state_id = 50)
  • Rows Removed by Filter: 670951
82. 36.361 79.036 ↑ 1.0 160,149 1

Hash (cost=4,072.69..4,072.69 rows=160,169 width=8) (actual time=79.036..79.036 rows=160,149 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2504kB
83. 42.675 42.675 ↑ 1.0 160,149 1

Seq Scan on orders orders_1 (cost=0.00..4,072.69 rows=160,169 width=8) (actual time=0.008..42.675 rows=160,149 loops=1)

84. 0.420 4.368 ↓ 0.0 0 364

Nested Loop (cost=0.86..1.02 rows=1 width=20) (actual time=0.011..0.012 rows=0 loops=364)

85. 0.553 3.640 ↓ 0.0 0 364

Nested Loop (cost=0.57..0.68 rows=1 width=24) (actual time=0.009..0.010 rows=0 loops=364)

86. 2.548 2.548 ↓ 0.0 0 364

Index Scan using customers_pkey on customers c_2 (cost=0.29..0.31 rows=1 width=24) (actual time=0.007..0.007 rows=0 loops=364)

  • Index Cond: (orders.subcontractor_id = customer_id)
87. 0.539 0.539 ↑ 1.0 1 77

Index Scan using unique_customer_id_address_id on customer_addresses ca_3 (cost=0.29..0.36 rows=1 width=16) (actual time=0.005..0.007 rows=1 loops=77)

  • Index Cond: (customer_id = c_2.customer_id)
  • Filter: is_main
  • Rows Removed by Filter: 1
88. 0.308 0.308 ↑ 1.0 1 77

Index Only Scan using addresses_pkey on addresses a_2 (cost=0.29..0.33 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=77)

  • Index Cond: (address_id = ca_3.address_id)
  • Heap Fetches: 0
89. 2.184 2.184 ↑ 1.0 1 364

Index Scan using customer_addresses_pkey on customer_addresses ca (cost=0.29..0.31 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=364)

  • Index Cond: (customer_address_id = order_itinerary.customer_address_id)
90. 1.820 1.820 ↑ 1.0 1 364

Index Scan using address_coordinates_address_id_index on address_coordinates ac (cost=0.28..0.31 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=364)

  • Index Cond: (address_id = ca.address_id)
91.          

SubPlan (forNested Loop Left Join)

92. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..20.93 rows=1 width=16) (never executed)

93. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.57..16.62 rows=1 width=20) (never executed)

94. 0.000 0.000 ↓ 0.0 0

Index Scan using unique_customer_id_address_id on customer_addresses ca_4 (cost=0.29..8.30 rows=1 width=16) (never executed)

  • Index Cond: (customer_id = vehicle_periods.customer_id)
  • Filter: is_main
95. 0.000 0.000 ↓ 0.0 0

Index Scan using customers_pkey on customers c_3 (cost=0.29..8.30 rows=1 width=24) (never executed)

  • Index Cond: (customer_id = vehicle_periods.customer_id)
96. 0.000 0.000 ↓ 0.0 0

Index Only Scan using addresses_pkey on addresses a_3 (cost=0.29..4.30 rows=1 width=4) (never executed)

  • Index Cond: (address_id = ca_4.address_id)
  • Heap Fetches: 0