explain.depesz.com

PostgreSQL's explain analyze made readable

Result: f0rO

Settings
# exclusive inclusive rows x rows loops node
1. 205.470 11,390.859 ↑ 33.6 125 1

GroupAggregate (cost=247,304.14..247,597.37 rows=4,200 width=145) (actual time=10,996.434..11,390.859 rows=125 loops=1)

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

CTE production_dates

3. 0.968 22.674 ↓ 2.9 61 1

HashAggregate (cost=823.02..823.23 rows=21 width=4) (actual time=22.659..22.674 rows=61 loops=1)

  • Group Key: pd_1.production_date
4. 4.428 21.706 ↓ 1.8 4,224 1

Hash Join (cost=102.77..817.15 rows=2,346 width=4) (actual time=2.497..21.706 rows=4,224 loops=1)

  • Hash Cond: (rd.ms_recipe_id = r.ms_recipe_id)
5. 4.585 15.027 ↓ 1.5 18,469 1

Nested Loop (cost=0.29..628.55 rows=12,532 width=8) (actual time=0.188..15.027 rows=18,469 loops=1)

6. 1.892 1.892 ↑ 1.0 114 1

Seq Scan on production_dates pd_1 (cost=0.00..164.86 rows=118 width=8) (actual time=0.176..1.892 rows=114 loops=1)

  • Filter: ((production_date >= '2018-12-29'::date) AND (fc_country = 'us'::text))
  • Rows Removed by Filter: 3903
7. 8.550 8.550 ↓ 1.5 162 114

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.006..0.075 rows=162 loops=114)

  • Index Cond: (delivery_date = pd_1.delivery_date)
8. 0.485 2.251 ↑ 1.0 2,554 1

Hash (cost=70.56..70.56 rows=2,554 width=4) (actual time=2.251..2.251 rows=2,554 loops=1)

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

Index Scan using recipes_country_index on recipes r (cost=0.29..70.56 rows=2,554 width=4) (actual time=0.030..1.766 rows=2,554 loops=1)

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

CTE production_site_boxes_with_dishbags

11. 687.873 9,750.509 ↓ 1.9 150,821 1

GroupAggregate (cost=236,549.23..243,957.29 rows=79,736 width=105) (actual time=8,774.158..9,750.509 rows=150,821 loops=1)

  • Group Key: b_1.id, b_1.fruit_box, b_1.status, b_1.production_date, b_1.number, b_1.production_site, b_1.shipper_name, b_1.product_type
12. 3,697.343 9,062.636 ↓ 2.0 426,522 1

Sort (cost=236,549.23..237,083.51 rows=213,712 width=105) (actual time=8,774.108..9,062.636 rows=426,522 loops=1)

  • Sort Key: b_1.id, b_1.fruit_box, b_1.status, b_1.production_date, b_1.number, b_1.production_site, b_1.shipper_name, b_1.product_type
  • Sort Method: external merge Disk: 50512kB
13. 349.748 5,365.293 ↓ 2.0 426,522 1

Hash Left Join (cost=332.81..216,920.45 rows=213,712 width=105) (actual time=7.356..5,365.293 rows=426,522 loops=1)

  • Hash Cond: (r_1.ms_recipe_id = recipes.ms_recipe_id)
14. 353.986 5,008.323 ↓ 2.0 426,522 1

Nested Loop Left Join (cost=1.12..212,047.38 rows=213,712 width=56) (actual time=0.116..5,008.323 rows=426,522 loops=1)

15. 431.349 431.349 ↓ 3.8 150,821 1

Index Scan using boxes_fc_country_production_site_production_date_index on boxes b_1 (cost=0.56..4,891.49 rows=39,868 width=48) (actual time=0.076..431.349 rows=150,821 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[]))
16. 4,222.988 4,222.988 ↑ 45.3 3 150,821

Index Scan using ordered_recipes_shipment_number_ms_recipe_id_index on ordered_recipes r_1 (cost=0.56..3.84 rows=136 width=25) (actual time=0.027..0.028 rows=3 loops=150,821)

  • Index Cond: (b_1.number = shipment_number)
17. 3.337 7.222 ↓ 1.0 13,647 1

Hash (cost=161.14..161.14 rows=13,644 width=57) (actual time=7.222..7.222 rows=13,647 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 1208kB
18. 3.885 3.885 ↓ 1.0 13,647 1

Seq Scan on recipes (cost=0.00..161.14 rows=13,644 width=57) (actual time=0.007..3.885 rows=13,647 loops=1)

19. 1,136.262 11,185.389 ↓ 18.0 150,820 1

Sort (cost=2,523.63..2,544.56 rows=8,372 width=145) (actual time=10,987.896..11,185.389 rows=150,820 loops=1)

  • Sort Key: pd.production_date, b.production_site
  • Sort Method: external merge Disk: 9696kB
20. 120.079 10,049.127 ↓ 18.0 150,820 1

Hash Right Join (cost=0.68..1,978.13 rows=8,372 width=145) (actual time=8,796.887..10,049.127 rows=150,820 loops=1)

  • Hash Cond: (b.production_date = pd.production_date)
21. 9,906.332 9,906.332 ↓ 1.9 150,821 1

CTE Scan on production_site_boxes_with_dishbags b (cost=0.00..1,594.72 rows=79,736 width=145) (actual time=8,774.162..9,906.332 rows=150,821 loops=1)

22. 0.019 22.716 ↓ 2.9 61 1

Hash (cost=0.42..0.42 rows=21 width=4) (actual time=22.716..22.716 rows=61 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
23. 22.697 22.697 ↓ 2.9 61 1

CTE Scan on production_dates pd (cost=0.00..0.42 rows=21 width=4) (actual time=22.664..22.697 rows=61 loops=1)