explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z6hQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 10.826 ↑ 386.3 3 1

Sort (cost=747.33..750.22 rows=1,159 width=47) (actual time=10.826..10.826 rows=3 loops=1)

  • Sort Key: (GREATEST(most_recent_user_recipe_purchase.purchased_at, user_recipe_favorites.created_at)) DESC
  • Sort Method: quicksort Memory: 25kB
2. 0.111 10.822 ↑ 386.3 3 1

Hash Left Join (cost=654.46..688.34 rows=1,159 width=47) (actual time=10.466..10.822 rows=3 loops=1)

  • Hash Cond: (recipes.id = user_recipe_favorites.recipe_id)
  • Filter: ((user_recipe_favorites.user_id IS NOT NULL) OR (most_recent_user_recipe_purchase.purchased_at IS NOT NULL))
  • Rows Removed by Filter: 1,156
3. 0.161 10.703 ↑ 1.0 1,159 1

Hash Left Join (cost=640.72..667.00 rows=1,159 width=45) (actual time=10.370..10.703 rows=1,159 loops=1)

  • Hash Cond: (recipes.id = most_recent_user_recipe_purchase.recipe_id)
4. 1.402 10.520 ↑ 1.0 1,159 1

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

  • Group Key: recipes.id
5. 1.520 9.118 ↑ 1.5 7,361 1

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

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

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

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

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

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

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

9. 0.195 1.763 ↓ 1.0 1,257 1

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

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

Seq Scan on ingredients (cost=0.00..113.06 rows=1,250 width=8) (actual time=0.010..1.568 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.215 0.409 ↑ 1.0 1,210 1

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

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

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

13. 0.259 0.554 ↑ 1.0 1,159 1

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

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

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

15. 0.001 0.022 ↑ 1.0 2 1

Hash (cost=1.10..1.10 rows=2 width=16) (actual time=0.022..0.022 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.002 0.021 ↑ 1.0 2 1

Subquery Scan on most_recent_user_recipe_purchase (cost=1.06..1.10 rows=2 width=16) (actual time=0.019..0.021 rows=2 loops=1)

17. 0.001 0.019 ↑ 1.0 2 1

Unique (cost=1.06..1.08 rows=2 width=16) (actual time=0.018..0.019 rows=2 loops=1)

18. 0.011 0.018 ↑ 1.0 3 1

Sort (cost=1.06..1.07 rows=3 width=16) (actual time=0.018..0.018 rows=3 loops=1)

  • Sort Key: user_recipe_purchases.recipe_id, user_recipe_purchases.purchased_at DESC
  • Sort Method: quicksort Memory: 25kB
19. 0.007 0.007 ↑ 1.0 3 1

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
21. 0.003 0.007 ↑ 3.0 2 1

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

  • Recheck Cond: (user_id = 23,168,370)
  • Heap Blocks: exact=1
22. 0.004 0.004 ↑ 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.004..0.004 rows=5 loops=1)

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