explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FbPc : distinct

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

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

Merge Left Join (cost=1,396.45..1,512.32 rows=1,159 width=79) (actual time=12.311..14.377 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.140 14.294 ↑ 1.0 1,159 1

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

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

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

  • Group Key: recipes.id
5. 2.005 12.685 ↑ 1.5 7,361 1

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

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

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

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

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

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

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

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

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

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

11. 0.247 0.460 ↑ 1.0 1,304 1

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

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

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

13. 0.013 0.026 ↑ 1.0 54 1

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

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

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

15. 0.204 0.411 ↑ 1.0 1,210 1

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

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

17. 0.255 0.547 ↑ 1.0 1,159 1

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

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

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

19. 0.002 0.014 ↑ 1.0 2 1

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

20. 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
21. 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.008..0.008 rows=3 loops=1)

  • Filter: (user_id = 23,168,370)
22. 0.003 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
23. 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
24. 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.978 ms
Execution time : 14.463 ms