explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1QCJ : distinct

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

Sort (cost=1,520.20..1,523.09 rows=1,159 width=79) (actual time=12.547..12.547 rows=3 loops=1)

  • Sort Key: (GREATEST(user_recipe_purchases.purchased_at, user_recipe_favorites.created_at)) DESC
  • Sort Method: quicksort Memory: 25kB
2. 0.093 12.543 ↑ 386.3 3 1

Merge Left Join (cost=1,345.34..1,461.21 rows=1,159 width=79) (actual time=10.565..12.543 rows=3 loops=1)

  • Merge 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.139 12.441 ↑ 1.0 1,159 1

Merge Left Join (cost=1,331.60..1,441.15 rows=1,159 width=77) (actual time=10.553..12.441 rows=1,159 loops=1)

  • Merge Cond: (recipes.id = user_recipe_purchases.recipe_id)
4. 1.353 12.289 ↑ 1.0 1,159 1

GroupAggregate (cost=1,330.54..1,425.44 rows=1,159 width=337) (actual time=10.539..12.289 rows=1,159 loops=1)

  • Group Key: recipes.id
5. 1.797 10.936 ↑ 1.5 7,361 1

Sort (cost=1,330.54..1,357.34 rows=10,722 width=45) (actual time=10.530..10.936 rows=7,361 loops=1)

  • Sort Key: recipes.id
  • Sort Method: quicksort Memory: 931kB
6. 1.657 9.139 ↑ 1.5 7,361 1

Hash Join (cost=259.99..612.79 rows=10,722 width=45) (actual time=2.716..9.139 rows=7,361 loops=1)

  • Hash Cond: (recipe_ingredient_groups.recipe_id = recipes.id)
7. 1.435 6.929 ↑ 1.5 7,361 1

Hash Join (cost=166.91..491.47 rows=10,722 width=16) (actual time=2.157..6.929 rows=7,361 loops=1)

  • Hash Cond: (recipe_ingredients.recipe_ingredient_group_id = recipe_ingredient_groups.id)
8. 2.129 5.078 ↑ 1.5 7,361 1

Hash Join (cost=128.69..425.00 rows=10,722 width=16) (actual time=1.736..5.078 rows=7,361 loops=1)

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

10. 0.189 1.726 ↓ 1.0 1,257 1

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

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

Seq Scan on ingredients (cost=0.00..113.06 rows=1,250 width=8) (actual time=0.009..1.537 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
12. 0.222 0.416 ↑ 1.0 1,210 1

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

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

14. 0.258 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
15. 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.005..0.295 rows=1,159 loops=1)

16. 0.001 0.013 ↑ 1.0 2 1

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

17. 0.004 0.012 ↑ 1.0 3 1

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

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

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

  • Filter: (user_id = 23,168,370)
19. 0.002 0.009 ↑ 3.0 2 1

Sort (cost=13.74..13.76 rows=6 width=24) (actual time=0.009..0.009 rows=2 loops=1)

  • Sort Key: user_recipe_favorites.recipe_id
  • Sort Method: quicksort Memory: 25kB
20. 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.007..0.007 rows=2 loops=1)

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