explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LtDE : JOIN

Settings
# exclusive inclusive rows x rows loops node
1. 0.802 13.878 ↑ 289.8 4 1

Sort (cost=750.14..753.03 rows=1,159 width=47) (actual time=13.877..13.878 rows=4 loops=1)

  • Sort Key: (LEAST(user_recipe_purchases.purchased_at, user_recipe_favorites.created_at)) DESC
  • Sort Method: quicksort Memory: 25kB
2. 0.114 13.076 ↑ 289.8 4 1

Hash Left Join (cost=654.41..691.15 rows=1,159 width=47) (actual time=12.721..13.076 rows=4 loops=1)

  • Hash Cond: (recipes.id = user_recipe_favorites.recipe_id)
  • Filter: ((user_recipe_favorites.user_id IS NOT NULL) OR (user_recipe_purchases.purchased_at IS NOT NULL))
  • Rows Removed by Filter: 1,156
3. 0.173 12.950 ↓ 1.0 1,160 1

Hash Left Join (cost=640.67..669.81 rows=1,159 width=45) (actual time=12.617..12.950 rows=1,160 loops=1)

  • Hash Cond: (recipes.id = user_recipe_purchases.recipe_id)
4. 2.305 12.763 ↑ 1.0 1,159 1

HashAggregate (cost=639.59..651.18 rows=1,159 width=337) (actual time=12.580..12.763 rows=1,159 loops=1)

  • Group Key: recipes.id
5. 1.650 10.458 ↑ 1.5 7,361 1

Hash Join (cost=259.99..612.79 rows=10,722 width=37) (actual time=3.627..10.458 rows=7,361 loops=1)

  • Hash Cond: (recipe_ingredient_groups.recipe_id = recipes.id)
6. 2.342 8.218 ↑ 1.5 7,361 1

Hash Join (cost=166.91..491.47 rows=10,722 width=8) (actual time=3.004..8.218 rows=7,361 loops=1)

  • Hash Cond: (recipe_ingredients.recipe_ingredient_group_id = recipe_ingredient_groups.id)
7. 2.311 5.449 ↑ 1.5 7,361 1

Hash Join (cost=128.69..425.00 rows=10,722 width=8) (actual time=1.795..5.449 rows=7,361 loops=1)

  • Hash Cond: (recipe_ingredients.ingredient_id = ingredients.id)
8. 1.382 1.382 ↑ 1.0 11,185 1

Seq Scan on recipe_ingredients (cost=0.00..266.85 rows=11,185 width=16) (actual time=0.007..1.382 rows=11,185 loops=1)

9. 0.202 1.756 ↓ 1.0 1,257 1

Hash (cost=113.06..113.06 rows=1,250 width=8) (actual time=1.755..1.756 rows=1,257 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 66kB
10. 1.554 1.554 ↓ 1.0 1,257 1

Seq Scan on ingredients (cost=0.00..113.06 rows=1,250 width=8) (actual time=0.013..1.554 rows=1,257 loops=1)

  • Filter: ((term)::text <> ALL ('{ice,water,"hot water",salt,"kosher salt","sea salt","coarse salt",pepper,"ground pepper","black pepper",salt,"black pepper","extra virgin olive oil","olive oil",water,"all purpose flour",butter,sugar,"vanilla extract","unsalted butter","ground cumin","canola oil","brown sugar",honey,"ground cinnamon","kosher salt","cayenne pepper","dried oregano","red wine vinegar","vegetable oil","balsamic vinegar","chili powder","garlic powder","baking powder","low sodium soy sauce",cornstarch,"baking soda","ground nutmeg",paprika,"light brown sugar","worcestershire sauce","italian seasoning","hot sauce","smoked paprika","toasted sesame oil",vinegar,"confectioners sugar","bay leaf","curry powder","ground coriander","ground ginger","cooking spray","sherry vinegar","whole wheat flour"}'::text[]))
  • Rows Removed by Filter: 47
11. 0.208 0.427 ↑ 1.0 1,210 1

Hash (cost=23.10..23.10 rows=1,210 width=16) (actual time=0.427..0.427 rows=1,210 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 73kB
12. 0.219 0.219 ↑ 1.0 1,210 1

Seq Scan on recipe_ingredient_groups (cost=0.00..23.10 rows=1,210 width=16) (actual time=0.028..0.219 rows=1,210 loops=1)

13. 0.267 0.590 ↑ 1.0 1,159 1

Hash (cost=78.59..78.59 rows=1,159 width=37) (actual time=0.590..0.590 rows=1,159 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 96kB
14. 0.323 0.323 ↑ 1.0 1,159 1

Seq Scan on recipes (cost=0.00..78.59 rows=1,159 width=37) (actual time=0.022..0.323 rows=1,159 loops=1)

15. 0.002 0.014 ↑ 1.0 3 1

Hash (cost=1.04..1.04 rows=3 width=16) (actual time=0.014..0.014 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.012 0.012 ↑ 1.0 3 1

Seq Scan on user_recipe_purchases (cost=0.00..1.04 rows=3 width=16) (actual time=0.012..0.012 rows=3 loops=1)

  • Filter: (user_id = 23,168,370)
17. 0.001 0.012 ↑ 3.0 2 1

Hash (cost=13.67..13.67 rows=6 width=24) (actual time=0.012..0.012 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
18. 0.004 0.011 ↑ 3.0 2 1

Bitmap Heap Scan on user_recipe_favorites (cost=4.20..13.67 rows=6 width=24) (actual time=0.010..0.011 rows=2 loops=1)

  • Recheck Cond: (user_id = 23,168,370)
  • Heap Blocks: exact=1
19. 0.007 0.007 ↑ 1.2 5 1

Bitmap Index Scan on index_user_recipe_favorites_on_user_id (cost=0.00..4.20 rows=6 width=0) (actual time=0.007..0.007 rows=5 loops=1)

  • Index Cond: (user_id = 23,168,370)
Planning time : 0.724 ms
Execution time : 13.968 ms