explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yLVC

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

HashAggregate (cost=83,709.65..83,870.41 rows=10,717 width=56) (actual rows= loops=)

  • Group Key: recipe_ids.production_date, recipe_ids.production_site, production_items.id, ingredients.id, recipe_ingredients.quantity
2.          

CTE recipe_ids

3. 0.000 0.000 ↓ 0.0

HashAggregate (cost=77,296.60..77,304.99 rows=839 width=16) (actual rows= loops=)

  • Group Key: boxes.production_date, boxes.production_site, ordered_recipes.ms_recipe_id
4. 0.000 0.000 ↓ 0.0

Merge Join (cost=4,840.90..77,288.21 rows=839 width=16) (actual rows= loops=)

  • Merge Cond: (ordered_recipes.shipment_number = boxes.number)
5. 0.000 0.000 ↓ 0.0

Index Scan using ordered_recipes_shipment_number_ms_recipe_id_index on ordered_recipes (cost=0.43..442,100.83 rows=6,538,939 width=25) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Sort (cost=4,840.47..4,840.63 rows=66 width=25) (actual rows= loops=)

  • Sort Key: boxes.number
7. 0.000 0.000 ↓ 0.0

Seq Scan on boxes (cost=0.00..4,838.48 rows=66 width=25) (actual rows= loops=)

  • Filter: ((status = ANY ('{complete,placed}'::text[])) AND (production_site = 'NWG'::text) AND (production_date = ANY ('{2018-11-25,2018-11-26,2018-11-27,2018-11-29,2018-11-30,2018-12-01}'::date[])))
8. 0.000 0.000 ↓ 0.0

Hash Join (cost=5,067.32..6,217.12 rows=10,717 width=56) (actual rows= loops=)

  • Hash Cond: (production_items.ingredient_id = ingredients.id)
9. 0.000 0.000 ↓ 0.0

Hash Join (cost=4,821.54..5,743.60 rows=10,717 width=56) (actual rows= loops=)

  • Hash Cond: (recipe_ingredients.production_item_id = production_items.id)
10. 0.000 0.000 ↓ 0.0

Hash Join (cost=3,797.55..4,478.47 rows=10,717 width=52) (actual rows= loops=)

  • Hash Cond: (recipe_ids.ms_recipe_id = recipe_ingredients.ms_recipe_id)
11. 0.000 0.000 ↓ 0.0

CTE Scan on recipe_ids (cost=0.00..16.78 rows=839 width=48) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Hash (cost=2,100.80..2,100.80 rows=97,580 width=12) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Seq Scan on recipe_ingredients (cost=0.00..2,100.80 rows=97,580 width=12) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Hash (cost=632.33..632.33 rows=31,333 width=8) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Seq Scan on production_items (cost=0.00..632.33 rows=31,333 width=8) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Hash (cost=162.57..162.57 rows=6,657 width=4) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Seq Scan on ingredients (cost=0.00..162.57 rows=6,657 width=4) (actual rows= loops=)