explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AWml

Settings
# exclusive inclusive rows x rows loops node
1. 206.946 12,864.987 ↑ 7.4 123 1

GroupAggregate (cost=1,274,450.62..1,274,489.47 rows=914 width=145) (actual time=12,467.669..12,864.987 rows=123 loops=1)

  • Group Key: pd.production_date, b.production_site
2.          

CTE production_dates

3. 1.292 31.145 ↓ 2.9 60 1

HashAggregate (cost=813.86..814.07 rows=21 width=4) (actual time=31.112..31.145 rows=60 loops=1)

  • Group Key: pd_1.production_date
4. 6.981 29.853 ↓ 1.8 4,144 1

Hash Join (cost=102.46..808.08 rows=2,311 width=4) (actual time=3.365..29.853 rows=4,144 loops=1)

  • Hash Cond: (rd.ms_recipe_id = r.ms_recipe_id)
5. 5.024 19.974 ↓ 1.5 17,873 1

Nested Loop (cost=0.29..621.20 rows=12,320 width=8) (actual time=0.320..19.974 rows=17,873 loops=1)

6. 3.302 3.302 ↑ 1.0 112 1

Seq Scan on production_dates pd_1 (cost=0.00..164.81 rows=116 width=8) (actual time=0.295..3.302 rows=112 loops=1)

  • Filter: ((production_date >= '2018-12-29'::date) AND (fc_country = 'us'::text))
  • Rows Removed by Filter: 3902
7. 11.648 11.648 ↓ 1.5 160 112

Index Scan using recipe_delivery_dates_delivery_date_index on recipe_delivery_dates rd (cost=0.29..2.86 rows=107 width=8) (actual time=0.008..0.104 rows=160 loops=112)

  • Index Cond: (delivery_date = pd_1.delivery_date)
8. 0.653 2.898 ↓ 1.0 2,554 1

Hash (cost=70.39..70.39 rows=2,542 width=4) (actual time=2.898..2.898 rows=2,554 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 90kB
9. 2.245 2.245 ↓ 1.0 2,554 1

Index Scan using recipes_country_index on recipes r (cost=0.29..70.39 rows=2,542 width=4) (actual time=0.037..2.245 rows=2,554 loops=1)

  • Index Cond: (country = 'us'::text)
10.          

CTE production_site_boxes

11. 661.105 661.105 ↓ 3.5 152,142 1

Index Scan using boxes_fc_country_production_site_production_date_index on boxes b_1 (cost=0.56..5,023.57 rows=43,542 width=48) (actual time=0.089..661.105 rows=152,142 loops=1)

  • Index Cond: ((fc_country = 'us'::text) AND (production_site = ANY ('{NYC1,TEX1,CA1}'::text[])) AND (production_date >= '2018-12-29'::date))
  • Filter: (status = ANY ('{placed,complete}'::text[]))
12.          

CTE production_site_boxes_with_dishbags

13. 673.646 11,255.566 ↓ 17.5 152,142 1

GroupAggregate (cost=1,090,548.76..1,268,351.37 rows=8,709 width=226) (actual time=10,282.676..11,255.566 rows=152,142 loops=1)

  • Group Key: b_2.id, b_2.fruit_box, b_2.status, b_2.production_date, b_2.number, b_2.production_site, b_2.shipper_name, b_2.product_type
14. 4,028.680 10,581.920 ↑ 13.8 430,288 1

Sort (cost=1,090,548.76..1,105,356.57 rows=5,923,125 width=226) (actual time=10,282.629..10,581.920 rows=430,288 loops=1)

  • Sort Key: b_2.id, b_2.fruit_box, b_2.status, b_2.production_date, b_2.number, b_2.production_site, b_2.shipper_name, b_2.product_type
  • Sort Method: external merge Disk: 50960kB
15. 417.490 6,553.240 ↑ 13.8 430,288 1

Hash Left Join (cost=330.12..350,219.39 rows=5,923,125 width=226) (actual time=9.967..6,553.240 rows=430,288 loops=1)

  • Hash Cond: (r_1.ms_recipe_id = recipes.ms_recipe_id)
16. 399.133 6,125.941 ↑ 13.8 430,288 1

Nested Loop Left Join (cost=0.56..224,023.43 rows=5,923,125 width=177) (actual time=0.135..6,125.941 rows=430,288 loops=1)

17. 858.264 858.264 ↓ 3.5 152,142 1

CTE Scan on production_site_boxes b_2 (cost=0.00..870.84 rows=43,542 width=169) (actual time=0.092..858.264 rows=152,142 loops=1)

18. 4,868.544 4,868.544 ↑ 45.3 3 152,142

Index Scan using ordered_recipes_shipment_number_ms_recipe_id_index on ordered_recipes r_1 (cost=0.56..3.76 rows=136 width=25) (actual time=0.031..0.032 rows=3 loops=152,142)

  • Index Cond: (b_2.number = shipment_number)
19. 4.575 9.809 ↓ 1.0 13,605 1

Hash (cost=160.19..160.19 rows=13,549 width=57) (actual time=9.809..9.809 rows=13,605 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 1205kB
20. 5.234 5.234 ↓ 1.0 13,605 1

Seq Scan on recipes (cost=0.00..160.19 rows=13,549 width=57) (actual time=0.027..5.234 rows=13,605 loops=1)

21. 1,102.466 12,658.041 ↓ 166.5 152,146 1

Sort (cost=261.61..263.90 rows=914 width=145) (actual time=12,458.336..12,658.041 rows=152,146 loops=1)

  • Sort Key: pd.production_date, b.production_site
  • Sort Method: external merge Disk: 9784kB
22. 117.223 11,555.575 ↓ 166.5 152,146 1

Hash Right Join (cost=0.68..216.66 rows=914 width=145) (actual time=10,313.950..11,555.575 rows=152,146 loops=1)

  • Hash Cond: (b.production_date = pd.production_date)
23. 11,407.096 11,407.096 ↓ 17.5 152,142 1

CTE Scan on production_site_boxes_with_dishbags b (cost=0.00..174.18 rows=8,709 width=145) (actual time=10,282.682..11,407.096 rows=152,142 loops=1)

24. 0.023 31.256 ↓ 2.9 60 1

Hash (cost=0.42..0.42 rows=21 width=4) (actual time=31.256..31.256 rows=60 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
25. 31.233 31.233 ↓ 2.9 60 1

CTE Scan on production_dates pd (cost=0.00..0.42 rows=21 width=4) (actual time=31.117..31.233 rows=60 loops=1)