explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E652 : join

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

Sort (cost=747.33..750.22 rows=1,159 width=47) (actual time=10.908..10.908 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.100 10.904 ↑ 386.3 3 1

Hash Left Join (cost=654.46..688.34 rows=1,159 width=47) (actual time=10.551..10.904 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.152 10.797 ↑ 1.0 1,159 1

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

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

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

  • Group Key: recipes.id
5. 1.554 9.238 ↑ 1.5 7,361 1

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

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

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

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

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

  • Hash Cond: (recipe_ingredients.ingredient_id = ingredients.id)
8. 1.297 1.297 ↑ 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.297 rows=11,185 loops=1)

9. 0.194 1.774 ↓ 1.0 1,257 1

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

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

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

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 73kB
12. 0.205 0.205 ↑ 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.205 rows=1,210 loops=1)

13. 0.257 0.553 ↑ 1.0 1,159 1

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

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

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

15. 0.001 0.013 ↑ 1.0 2 1

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

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

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

17. 0.002 0.011 ↑ 1.0 2 1

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

18. 0.004 0.009 ↑ 1.0 3 1

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

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
21. 0.003 0.006 ↑ 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.006 rows=2 loops=1)

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

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