explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7uYT

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 12.547 ↑ 289.8 4 1

Sort (cost=1,571.40..1,574.30 rows=1,159 width=79) (actual time=12.547..12.547 rows=4 loops=1)

  • Sort Key: (GREATEST(user_recipe_purchases.purchased_at, user_recipe_favorites.created_at)) DESC
  • Sort Method: quicksort Memory: 25kB
2. 0.077 12.543 ↑ 289.8 4 1

Merge Left Join (cost=1,396.45..1,512.41 rows=1,159 width=79) (actual time=10.596..12.543 rows=4 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.135 12.458 ↓ 1.0 1,160 1

Merge Left Join (cost=1,382.70..1,492.35 rows=1,159 width=77) (actual time=10.584..12.458 rows=1,160 loops=1)

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

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

  • Group Key: recipes.id
5. 1.780 10.968 ↑ 1.5 7,361 1

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

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

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

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

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

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

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

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

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

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

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

11. 0.199 0.359 ↑ 1.0 1,304 1

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

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

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

13. 0.010 0.021 ↑ 1.0 54 1

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

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

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

15. 0.159 0.305 ↑ 1.0 1,210 1

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

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

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

17. 0.191 0.411 ↑ 1.0 1,159 1

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

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

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

19. 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
  • Sort Method: quicksort Memory: 25kB
20. 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)
21. 0.001 0.008 ↑ 2.0 3 1

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

  • Sort Key: user_recipe_favorites.recipe_id
  • Sort Method: quicksort Memory: 25kB
22. 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
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.680 ms
Execution time : 12.619 ms