explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6ba2 : Optimization for: plan #e1uW

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 25.234 170,319.318 ↑ 2.3 11,038 1

Sort (cost=8,429,964.21..8,430,028.94 rows=25,890 width=2,448) (actual time=170,315.247..170,319.318 rows=11,038 loops=1)

  • Sort Key: orderstatus.order_id
  • Sort Method: external merge Disk: 3,136kB
2.          

CTE customerdata

3. 1,949.589 164,202.473 ↑ 1.2 33,685 1

GroupAggregate (cost=6,200,496.10..6,265,897.04 rows=39,597 width=12) (actual time=160,198.917..164,202.473 rows=33,685 loops=1)

  • Group Key: a.customer_id
4. 7,523.594 162,252.884 ↓ 1.8 5,155,922 1

Sort (cost=6,200,496.10..6,207,641.88 rows=2,858,312 width=12) (actual time=160,197.400..162,252.884 rows=5,155,922 loops=1)

  • Sort Key: a.customer_id
  • Sort Method: external merge Disk: 131,104kB
5. 41,926.140 154,729.290 ↓ 1.8 5,155,922 1

Hash Right Join (cost=4,429,966.84..5,845,139.34 rows=2,858,312 width=12) (actual time=100,108.639..154,729.290 rows=5,155,922 loops=1)

  • Hash Cond: (sa.id = o.addon_id)
  • Filter: ((sa.sku_id IS NULL) OR (NOT (hashed SubPlan 1)))
  • Rows Removed by Filter: 29,269
6. 12,714.728 12,714.728 ↑ 1.0 34,099,624 1

Seq Scan on subscriptions_addon sa (cost=0.00..760,240.36 rows=34,184,236 width=8) (actual time=0.014..12,714.728 rows=34,099,624 loops=1)

7. 3,102.428 100,088.410 ↑ 1.1 5,185,191 1

Hash (cost=4,330,579.36..4,330,579.36 rows=5,716,625 width=16) (actual time=100,088.410..100,088.410 rows=5,185,191 loops=1)

  • Buckets: 131,072 (originally 131072) Batches: 32,768 (originally 128) Memory Usage: 165,336kB
8. 47,686.345 96,985.982 ↑ 1.1 5,185,191 1

Hash Join (cost=91,361.28..4,330,579.36 rows=5,716,625 width=16) (actual time=473.620..96,985.982 rows=5,185,191 loops=1)

  • Hash Cond: (o.address_id = a.id)
9. 48,827.551 48,827.551 ↓ 1.0 109,318,409 1

Seq Scan on orders_order o (cost=0.00..2,759,346.24 rows=105,214,024 width=16) (actual time=0.000..48,827.551 rows=109,318,409 loops=1)

10. 62.935 472.086 ↑ 1.0 166,426 1

Hash (cost=88,544.92..88,544.92 rows=171,629 width=8) (actual time=472.086..472.086 rows=166,426 loops=1)

  • Buckets: 131,072 Batches: 4 Memory Usage: 2,654kB
11. 384.280 409.151 ↑ 1.0 166,426 1

Bitmap Heap Scan on address_v3_addressv3cached a (cost=5,702.55..88,544.92 rows=171,629 width=8) (actual time=31.342..409.151 rows=166,426 loops=1)

  • Recheck Cond: (hub_id = 2)
  • Rows Removed by Index Recheck: 1,011,404
  • Heap Blocks: exact=32,562 lossy=26,559
12. 24.871 24.871 ↑ 1.0 167,735 1

Bitmap Index Scan on address_v3_addressv3cached_97469368 (cost=0.00..5,659.65 rows=171,629 width=0) (actual time=24.871..24.871 rows=167,735 loops=1)

  • Index Cond: (hub_id = 2)
13.          

SubPlan (for Hash Right Join)

14. 0.012 0.012 ↑ 500.0 2 1

Index Scan using orders_ordersettings_key_5c51ff9d_uniq on orders_ordersettings (cost=0.14..13.16 rows=1,000 width=38) (actual time=0.010..0.012 rows=2 loops=1)

  • Index Cond: ((key)::text = 'bag_skus'::text)
15.          

CTE skuunitdata

16. 0.085 0.085 ↑ 17.2 58 1

Index Scan using orders_ordersettings_key_5c51ff9d_uniq on orders_ordersettings orders_ordersettings_1 (cost=0.14..13.16 rows=1,000 width=38) (actual time=0.037..0.085 rows=58 loops=1)

  • Index Cond: ((key)::text = 'order_generation_sku_quantity_ids'::text)
17.          

CTE enabledcitydata

18. 0.031 0.031 ↑ 1,000.0 1 1

Index Scan using orders_ordersettings_key_5c51ff9d_uniq on orders_ordersettings orders_ordersettings_2 (cost=0.14..13.16 rows=1,000 width=38) (actual time=0.030..0.031 rows=1 loops=1)

  • Index Cond: ((key)::text = 'order_generation_quantity_conversion_city_ids'::text)
19. 46.461 170,294.084 ↑ 2.3 11,038 1

Hash Left Join (cost=2,119,132.94..2,134,797.59 rows=25,890 width=2,448) (actual time=170,092.039..170,294.084 rows=11,038 loops=1)

  • Hash Cond: (addressdata.customer_id = ad_customer.customer_id)
20. 7.750 170,244.926 ↑ 2.3 11,038 1

Hash Left Join (cost=2,118,628.35..2,131,606.80 rows=25,890 width=2,445) (actual time=170,089.245..170,244.926 rows=11,038 loops=1)

  • Hash Cond: (subscriptionsku.unit_id = subscriptionskuunit.id)
21. 7.263 170,237.166 ↑ 2.3 11,038 1

Hash Left Join (cost=2,118,626.97..2,131,255.73 rows=25,890 width=2,031) (actual time=170,089.235..170,237.166 rows=11,038 loops=1)

  • Hash Cond: (addonsku.unit_id = addonskuunit.id)
22. 7.099 170,229.856 ↑ 2.3 11,038 1

Hash Left Join (cost=2,118,625.58..2,130,904.66 rows=25,890 width=1,617) (actual time=170,089.182..170,229.856 rows=11,038 loops=1)

  • Hash Cond: (citydata.id = conversionenabledcitydata.city_id)
23. 18.310 170,222.722 ↓ 2.1 11,038 1

Nested Loop (cost=2,118,593.08..2,129,953.06 rows=5,178 width=1,617) (actual time=170,088.998..170,222.722 rows=11,038 loops=1)

24. 19.297 170,127.146 ↓ 2.1 11,038 1

Merge Left Join (cost=2,118,592.65..2,118,819.34 rows=5,178 width=1,605) (actual time=170,088.950..170,127.146 rows=11,038 loops=1)

  • Merge Cond: (addressdata.customer_id = customerdata.customer_id)
25. 30.913 5,858.255 ↓ 2.1 11,038 1

Sort (cost=2,114,776.87..2,114,789.81 rows=5,178 width=1,573) (actual time=5,850.064..5,858.255 rows=11,038 loops=1)

  • Sort Key: addressdata.customer_id
  • Sort Method: external merge Disk: 3,048kB
26. 6.123 5,827.342 ↓ 2.1 11,038 1

Hash Left Join (cost=185,303.53..2,110,915.43 rows=5,178 width=1,573) (actual time=5,695.284..5,827.342 rows=11,038 loops=1)

  • Hash Cond: (subscriptionsku.id = subscriptionskuunitdata.sku_id)
27. 6.452 5,821.174 ↓ 2.1 11,038 1

Hash Left Join (cost=185,271.03..2,110,217.74 rows=5,178 width=1,569) (actual time=5,695.239..5,821.174 rows=11,038 loops=1)

  • Hash Cond: (addonsku.id = addonskuunitdata.sku_id)
28. 7.628 5,814.589 ↓ 2.1 11,038 1

Nested Loop Left Join (cost=185,238.53..2,109,520.04 rows=5,178 width=1,565) (actual time=5,695.089..5,814.589 rows=11,038 loops=1)

29. 60.563 5,795.923 ↓ 2.1 11,038 1

Nested Loop (cost=185,238.39..2,108,657.72 rows=5,178 width=1,151) (actual time=5,695.085..5,795.923 rows=11,038 loops=1)

  • Join Filter: (areadata.id = buildingdata.area_id)
  • Rows Removed by Join Filter: 132,456
30. 0.036 0.085 ↓ 1.1 13 1

Merge Join (cost=2.71..2.95 rows=12 width=426) (actual time=0.032..0.085 rows=13 loops=1)

  • Merge Cond: (areadata.city_id = citydata.id)
31. 0.017 0.026 ↑ 1.0 13 1

Sort (cost=1.37..1.40 rows=13 width=8) (actual time=0.017..0.026 rows=13 loops=1)

  • Sort Key: areadata.city_id
  • Sort Method: quicksort Memory: 25kB
32. 0.009 0.009 ↑ 1.0 13 1

Seq Scan on accounts_area areadata (cost=0.00..1.13 rows=13 width=8) (actual time=0.003..0.009 rows=13 loops=1)

33. 0.016 0.023 ↓ 1.1 13 1

Sort (cost=1.34..1.37 rows=12 width=422) (actual time=0.013..0.023 rows=13 loops=1)

  • Sort Key: citydata.id
  • Sort Method: quicksort Memory: 25kB
34. 0.007 0.007 ↑ 1.0 12 1

Seq Scan on accounts_city citydata (cost=0.00..1.12 rows=12 width=422) (actual time=0.002..0.007 rows=12 loops=1)

35. 59.360 5,735.275 ↓ 2.0 11,038 13

Materialize (cost=185,235.68..2,101,928.00 rows=5,610 width=733) (actual time=166.880..441.175 rows=11,038 loops=13)

36. 6.004 5,675.915 ↓ 2.0 11,038 1

Nested Loop (cost=185,235.68..2,101,378.95 rows=5,610 width=733) (actual time=2,169.404..5,675.915 rows=11,038 loops=1)

37. 0.033 0.033 ↑ 1.0 1 1

Index Only Scan using accounts_hub_pkey on accounts_hub hubdata (cost=0.27..8.29 rows=1 width=4) (actual time=0.008..0.033 rows=1 loops=1)

  • Index Cond: (id = 2)
  • Heap Fetches: 1
38. 7.182 5,669.878 ↓ 2.0 11,038 1

Hash Join (cost=185,235.41..2,101,314.56 rows=5,610 width=733) (actual time=2,169.391..5,669.878 rows=11,038 loops=1)

  • Hash Cond: (addressdata.building_id = buildingdata.id)
39. 6.903 5,661.628 ↓ 2.0 11,038 1

Hash Left Join (cost=185,183.27..2,101,185.28 rows=5,610 width=720) (actual time=2,168.310..5,661.628 rows=11,038 loops=1)

  • Hash Cond: (subscriptionsku.item_id = subscriptionitemdata.id)
40. 301.365 5,649.103 ↓ 2.0 11,038 1

Hash Join (cost=184,682.41..2,100,608.86 rows=5,610 width=720) (actual time=2,162.682..5,649.103 rows=11,038 loops=1)

  • Hash Cond: (orderdata.address_id = addressdata.id)
41. 206.194 4,802.564 ↓ 2.9 300,372 1

Hash Left Join (cost=90,304.13..1,986,973.29 rows=103,250 width=575) (actual time=1,613.649..4,802.564 rows=300,372 loops=1)

  • Hash Cond: (subscriptiondata.sku_id = subscriptionsku.id)
42. 473.682 4,584.577 ↓ 2.9 300,372 1

Nested Loop (cost=88,355.28..1,983,604.75 rows=103,250 width=526) (actual time=1,601.838..4,584.577 rows=300,372 loops=1)

43. 183.006 3,209.779 ↓ 2.9 300,372 1

Hash Left Join (cost=88,354.71..1,134,363.00 rows=103,250 width=517) (actual time=1,601.812..3,209.779 rows=300,372 loops=1)

  • Hash Cond: (addonsku.item_id = addonitemdata.id)
44. 183.291 3,016.923 ↓ 2.9 300,372 1

Hash Left Join (cost=87,745.00..1,132,362.62 rows=103,250 width=103) (actual time=1,591.949..3,016.923 rows=300,372 loops=1)

  • Hash Cond: (addondata.sku_id = addonsku.id)
45. 387.096 2,822.038 ↓ 2.9 300,372 1

Nested Loop Left Join (cost=85,796.14..1,128,994.08 rows=103,250 width=54) (actual time=1,580.266..2,822.038 rows=300,372 loops=1)

46. 423.891 2,134.570 ↓ 2.9 300,372 1

Hash Left Join (cost=85,795.70..427,852.71 rows=103,250 width=50) (actual time=1,580.248..2,134.570 rows=300,372 loops=1)

  • Hash Cond: (orderdata.subscription_id = subscriptiondata.id)
47. 131.358 152.872 ↓ 2.9 300,372 1

Bitmap Heap Scan on orders_order orderdata (cost=2,080.76..331,404.68 rows=103,250 width=45) (actual time=22.288..152.872 rows=300,372 loops=1)

  • Recheck Cond: (delivery_date = '2020-09-04'::date)
  • Heap Blocks: exact=5,014
48. 21.514 21.514 ↓ 2.9 300,372 1

Bitmap Index Scan on orders_order_delivery_date_61fa62d1_uniq (cost=0.00..2,054.94 rows=103,250 width=0) (actual time=21.514..21.514 rows=300,372 loops=1)

  • Index Cond: (delivery_date = '2020-09-04'::date)
49. 699.037 1,557.807 ↑ 1.0 1,972,062 1

Hash (cost=49,433.31..49,433.31 rows=1,972,131 width=13) (actual time=1,557.807..1,557.807 rows=1,972,062 loops=1)

  • Buckets: 131,072 Batches: 32 Memory Usage: 3,924kB
50. 858.770 858.770 ↑ 1.0 1,972,062 1

Seq Scan on subscriptions_subscription subscriptiondata (cost=0.00..49,433.31 rows=1,972,131 width=13) (actual time=0.000..858.770 rows=1,972,062 loops=1)

51. 300.372 300.372 ↓ 0.0 0 300,372

Index Scan using subscriptions_addon_pkey on subscriptions_addon addondata (cost=0.44..6.78 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=300,372)

  • Index Cond: (orderdata.addon_id = id)
52. 4.382 11.594 ↑ 1.0 10,349 1

Hash (cost=1,819.49..1,819.49 rows=10,349 width=53) (actual time=11.594..11.594 rows=10,349 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 1,020kB
53. 7.212 7.212 ↑ 1.0 10,349 1

Seq Scan on products_sku addonsku (cost=0.00..1,819.49 rows=10,349 width=53) (actual time=0.000..7.212 rows=10,349 loops=1)

54. 2.585 9.850 ↓ 1.0 8,089 1

Hash (cost=512.01..512.01 rows=7,816 width=422) (actual time=9.850..9.850 rows=8,089 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 386kB
55. 5.358 7.265 ↓ 1.0 8,089 1

Hash Left Join (cost=1.38..512.01 rows=7,816 width=422) (actual time=0.007..7.265 rows=8,089 loops=1)

  • Hash Cond: (addonitemdata.unit_id = addonitemunitdata.id)
56. 1.903 1.903 ↓ 1.0 8,089 1

Seq Scan on products_item addonitemdata (cost=0.00..403.16 rows=7,816 width=8) (actual time=0.000..1.903 rows=8,089 loops=1)

57. 0.003 0.004 ↑ 1.0 17 1

Hash (cost=1.17..1.17 rows=17 width=422) (actual time=0.004..0.004 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
58. 0.001 0.001 ↑ 1.0 17 1

Seq Scan on products_unit addonitemunitdata (cost=0.00..1.17 rows=17 width=422) (actual time=0.000..0.001 rows=17 loops=1)

59. 901.116 901.116 ↑ 1.0 1 300,372

Index Scan using orders_orderstatus_69dfcb07 on orders_orderstatus orderstatus (cost=0.57..8.22 rows=1 width=9) (actual time=0.003..0.003 rows=1 loops=300,372)

  • Index Cond: (order_id = orderdata.id)
60. 4.566 11.793 ↑ 1.0 10,349 1

Hash (cost=1,819.49..1,819.49 rows=10,349 width=53) (actual time=11.793..11.793 rows=10,349 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 1,020kB
61. 7.227 7.227 ↑ 1.0 10,349 1

Seq Scan on products_sku subscriptionsku (cost=0.00..1,819.49 rows=10,349 width=53) (actual time=0.000..7.227 rows=10,349 loops=1)

62. 119.146 545.174 ↑ 1.0 166,426 1

Hash (cost=88,544.92..88,544.92 rows=171,629 width=149) (actual time=545.174..545.174 rows=166,426 loops=1)

  • Buckets: 32,768 Batches: 16 Memory Usage: 1,820kB
63. 403.371 426.028 ↑ 1.0 166,426 1

Bitmap Heap Scan on address_v3_addressv3cached addressdata (cost=5,702.55..88,544.92 rows=171,629 width=149) (actual time=28.741..426.028 rows=166,426 loops=1)

  • Recheck Cond: (hub_id = 2)
  • Rows Removed by Index Recheck: 1,011,404
  • Heap Blocks: exact=32,562 lossy=26,559
64. 22.657 22.657 ↑ 1.0 167,735 1

Bitmap Index Scan on address_v3_addressv3cached_97469368 (cost=0.00..5,659.65 rows=171,629 width=0) (actual time=22.657..22.657 rows=167,735 loops=1)

  • Index Cond: (hub_id = 2)
65. 2.473 5.622 ↓ 1.0 8,089 1

Hash (cost=403.16..403.16 rows=7,816 width=8) (actual time=5.622..5.622 rows=8,089 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 380kB
66. 3.149 3.149 ↓ 1.0 8,089 1

Seq Scan on products_item subscriptionitemdata (cost=0.00..403.16 rows=7,816 width=8) (actual time=0.006..3.149 rows=8,089 loops=1)

67. 0.539 1.068 ↑ 1.0 1,537 1

Hash (cost=32.62..32.62 rows=1,562 width=21) (actual time=1.068..1.068 rows=1,537 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 100kB
68. 0.529 0.529 ↑ 1.0 1,537 1

Seq Scan on accounts_building buildingdata (cost=0.00..32.62 rows=1,562 width=21) (actual time=0.003..0.529 rows=1,537 loops=1)

69. 11.038 11.038 ↑ 1.0 1 11,038

Index Scan using products_unit_pkey on products_unit subscriptionitemunitdata (cost=0.14..0.16 rows=1 width=422) (actual time=0.001..0.001 rows=1 loops=11,038)

  • Index Cond: (id = subscriptionitemdata.unit_id)
70. 0.024 0.133 ↑ 17.2 58 1

Hash (cost=20.00..20.00 rows=1,000 width=4) (actual time=0.133..0.133 rows=58 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
71. 0.109 0.109 ↑ 17.2 58 1

CTE Scan on skuunitdata addonskuunitdata (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.038..0.109 rows=58 loops=1)

72. 0.012 0.045 ↑ 17.2 58 1

Hash (cost=20.00..20.00 rows=1,000 width=4) (actual time=0.045..0.045 rows=58 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
73. 0.033 0.033 ↑ 17.2 58 1

CTE Scan on skuunitdata subscriptionskuunitdata (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.000..0.033 rows=58 loops=1)

74. 23.347 164,249.594 ↑ 1.0 38,744 1

Sort (cost=3,815.79..3,914.78 rows=39,597 width=36) (actual time=164,238.877..164,249.594 rows=38,744 loops=1)

  • Sort Key: customerdata.customer_id
  • Sort Method: quicksort Memory: 2,945kB
75. 164,226.247 164,226.247 ↑ 1.2 33,685 1

CTE Scan on customerdata (cost=0.00..791.94 rows=39,597 width=36) (actual time=160,198.917..164,226.247 rows=33,685 loops=1)

76. 77.266 77.266 ↑ 1.0 1 11,038

Index Scan using accounts_user_pkey on accounts_user userdata (cost=0.43..2.14 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=11,038)

  • Index Cond: (id = addressdata.customer_id)
77. 0.002 0.035 ↑ 1,000.0 1 1

Hash (cost=20.00..20.00 rows=1,000 width=4) (actual time=0.035..0.035 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
78. 0.033 0.033 ↑ 1,000.0 1 1

CTE Scan on enabledcitydata conversionenabledcitydata (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.031..0.033 rows=1 loops=1)

79. 0.007 0.047 ↑ 1.0 17 1

Hash (cost=1.17..1.17 rows=17 width=422) (actual time=0.047..0.047 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
80. 0.040 0.040 ↑ 1.0 17 1

Seq Scan on products_unit addonskuunit (cost=0.00..1.17 rows=17 width=422) (actual time=0.004..0.040 rows=17 loops=1)

81. 0.008 0.010 ↑ 1.0 17 1

Hash (cost=1.17..1.17 rows=17 width=422) (actual time=0.010..0.010 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
82. 0.002 0.002 ↑ 1.0 17 1

Seq Scan on products_unit subscriptionskuunit (cost=0.00..1.17 rows=17 width=422) (actual time=0.002..0.002 rows=17 loops=1)

83. 0.223 2.697 ↓ 1.8 595 1

Hash (cost=500.46..500.46 rows=330 width=11) (actual time=2.697..2.697 rows=595 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
84. 2.474 2.474 ↓ 1.8 595 1

Index Scan using orders_alternatedeliverycustomerdata_delivery_date_c06a4760_idx on delivery_alternatedeliverycustomerdata ad_customer (cost=0.41..500.46 rows=330 width=11) (actual time=0.602..2.474 rows=595 loops=1)

  • Index Cond: ((delivery_date = '2020-09-04'::date) AND ((delivery_type)::text = 'ALTERNATE_DELIVERY'::text))
Planning time : 7.701 ms
Execution time : 170,350.002 ms