explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JZg9 : UNION

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 10.988 ↑ 17.3 3 1

Sort (cost=684.05..684.18 rows=52 width=47) (actual time=10.987..10.988 rows=3 loops=1)

  • Sort Key: purchases_and_favorites.last_purchased_or_favorited_at DESC
  • Sort Method: quicksort Memory: 25kB
2. 0.104 10.981 ↑ 17.3 3 1

Hash Join (cost=654.98..682.57 rows=52 width=47) (actual time=10.722..10.981 rows=3 loops=1)

  • Hash Cond: (recipes.id = purchases_and_favorites.recipe_id)
3. 1.518 10.828 ↑ 1.0 1,159 1

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

  • Group Key: recipes.id
4. 1.580 9.310 ↑ 1.5 7,361 1

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

  • Hash Cond: (recipe_ingredient_groups.recipe_id = recipes.id)
5. 1.513 7.178 ↑ 1.5 7,361 1

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

  • Hash Cond: (recipe_ingredients.recipe_ingredient_group_id = recipe_ingredient_groups.id)
6. 2.221 5.255 ↑ 1.5 7,361 1

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

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

8. 0.186 1.706 ↓ 1.0 1,257 1

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

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

Seq Scan on ingredients (cost=0.00..113.06 rows=1,250 width=8) (actual time=0.009..1.520 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
10. 0.216 0.410 ↑ 1.0 1,210 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 73kB
11. 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)

12. 0.257 0.552 ↑ 1.0 1,159 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 96kB
13. 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.004..0.295 rows=1,159 loops=1)

14. 0.002 0.049 ↑ 3.0 3 1

Hash (cost=15.28..15.28 rows=9 width=48) (actual time=0.049..0.049 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.001 0.047 ↑ 3.0 3 1

Subquery Scan on purchases_and_favorites (cost=14.98..15.28 rows=9 width=48) (actual time=0.042..0.047 rows=3 loops=1)

16. 0.020 0.046 ↑ 3.0 3 1

GroupAggregate (cost=14.98..15.19 rows=9 width=48) (actual time=0.041..0.046 rows=3 loops=1)

  • Group Key: "*SELECT* 1".recipe_id
17. 0.008 0.026 ↑ 1.8 5 1

Sort (cost=14.98..15.01 rows=9 width=48) (actual time=0.025..0.026 rows=5 loops=1)

  • Sort Key: "*SELECT* 1".recipe_id
  • Sort Method: quicksort Memory: 25kB
18. 0.001 0.018 ↑ 1.8 5 1

Append (cost=0.00..14.84 rows=9 width=48) (actual time=0.008..0.018 rows=5 loops=1)

19. 0.001 0.009 ↑ 1.0 3 1

Subquery Scan on *SELECT* 1 (cost=0.00..1.07 rows=3 width=48) (actual time=0.007..0.009 rows=3 loops=1)

20. 0.008 0.008 ↑ 1.0 3 1

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

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

Subquery Scan on *SELECT* 2 (cost=4.20..13.73 rows=6 width=48) (actual time=0.007..0.008 rows=2 loops=1)

22. 0.003 0.007 ↑ 3.0 2 1

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

  • Recheck Cond: (user_id = 23,168,370)
  • Heap Blocks: exact=1
23. 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.979 ms
Execution time : 11.156 ms