explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SA8V

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

Sort (cost=1,571.30..1,574.20 rows=1,159 width=79) (actual time=15.676..15.676 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.087 15.672 ↑ 386.3 3 1

Merge Left Join (cost=1,396.45..1,512.32 rows=1,159 width=79) (actual time=13.339..15.672 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.152 15.575 ↑ 1.0 1,159 1

Merge Left Join (cost=1,382.70..1,492.25 rows=1,159 width=77) (actual time=13.326..15.575 rows=1,159 loops=1)

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

GroupAggregate (cost=1,381.64..1,476.54 rows=1,159 width=337) (actual time=13.308..15.407 rows=1,159 loops=1)

  • Group Key: recipes.id
5. 2.239 13.812 ↑ 1.5 7,361 1

Sort (cost=1,381.64..1,408.45 rows=10,722 width=45) (actual time=13.297..13.812 rows=7,361 loops=1)

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

Hash Join (cost=173.99..663.90 rows=10,722 width=45) (actual time=1.484..11.573 rows=7,361 loops=1)

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

Hash Join (cost=80.91..542.57 rows=10,722 width=16) (actual time=0.952..9.340 rows=7,361 loops=1)

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

Hash Anti Join (cost=42.69..476.10 rows=10,722 width=16) (actual time=0.528..7.243 rows=7,361 loops=1)

  • Hash Cond: ((ingredients.term)::text = "*VALUES*".column1)
9. 3.021 4.905 ↑ 1.0 11,185 1

Hash Join (cost=41.34..337.65 rows=11,185 width=32) (actual time=0.482..4.905 rows=11,185 loops=1)

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

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

11. 0.251 0.463 ↑ 1.0 1,304 1

Hash (cost=25.04..25.04 rows=1,304 width=24) (actual time=0.463..0.463 rows=1,304 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 88kB
12. 0.212 0.212 ↑ 1.0 1,304 1

Seq Scan on ingredients (cost=0.00..25.04 rows=1,304 width=24) (actual time=0.008..0.212 rows=1,304 loops=1)

13. 0.014 0.038 ↑ 1.0 54 1

Hash (cost=0.68..0.68 rows=54 width=32) (actual time=0.038..0.038 rows=54 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
14. 0.024 0.024 ↑ 1.0 54 1

Values Scan on "*VALUES*" (cost=0.00..0.68 rows=54 width=32) (actual time=0.001..0.024 rows=54 loops=1)

15. 0.215 0.404 ↑ 1.0 1,210 1

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

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

17. 0.249 0.508 ↑ 1.0 1,159 1

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

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

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

19. 0.001 0.016 ↑ 1.0 2 1

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

20. 0.006 0.015 ↑ 1.0 3 1

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

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

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

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

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

  • Sort Key: user_recipe_favorites.recipe_id
  • Sort Method: quicksort Memory: 25kB
23. 0.003 0.008 ↑ 3.0 2 1

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

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

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