explain.depesz.com

PostgreSQL's explain analyze made readable

Result: faaU

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 44.437 ↓ 1.6 8 1

Subquery Scan on items_0 (cost=1,720.33..1,720.82 rows=5 width=8) (actual time=44.414..44.437 rows=8 loops=1)

  • Filter: (items_0.ingredient_ranking <= 1)
  • Rows Removed by Filter: 14
2. 0.033 44.433 ↓ 1.5 22 1

WindowAgg (cost=1,720.33..1,720.63 rows=15 width=433) (actual time=44.412..44.433 rows=22 loops=1)

3. 0.024 44.400 ↓ 1.5 22 1

Sort (cost=1,720.33..1,720.37 rows=15 width=16) (actual time=44.398..44.400 rows=22 loops=1)

  • Sort Key: recipe_ingredient_products.ingredient_id, a.ordinality
  • Sort Method: quicksort Memory: 26kB
4. 0.071 44.376 ↓ 1.5 22 1

Nested Loop Left Join (cost=1,601.00..1,720.04 rows=15 width=16) (actual time=43.848..44.376 rows=22 loops=1)

  • Filter: (COALESCE(items_availabilities.availability_score, '0.87'::double precision) > '0.525'::double precision)
5. 0.062 44.063 ↑ 2.0 22 1

Hash Join (cost=1,600.72..1,643.57 rows=45 width=28) (actual time=43.803..44.063 rows=22 loops=1)

  • Hash Cond: (a.product_id = items_0_1.product_id)
6. 0.086 0.247 ↑ 6.6 68 1

Nested Loop (cost=20.20..61.88 rows=450 width=16) (actual time=0.037..0.247 rows=68 loops=1)

7. 0.040 0.071 ↑ 4.5 10 1

Hash Join (cost=20.20..52.87 rows=45 width=36) (actual time=0.031..0.071 rows=10 loops=1)

  • Hash Cond: (recipe_ingredient_products.ingredient_id = recipe_ingredients.ingredient_id)
8. 0.015 0.015 ↑ 158.8 8 1

Seq Scan on recipe_ingredient_products (cost=0.00..22.70 rows=1,270 width=36) (actual time=0.009..0.015 rows=8 loops=1)

9. 0.003 0.016 ↑ 4.5 10 1

Hash (cost=19.64..19.64 rows=45 width=4) (actual time=0.016..0.016 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
10. 0.005 0.013 ↑ 4.5 10 1

Bitmap Heap Scan on recipe_ingredients (cost=8.96..19.64 rows=45 width=4) (actual time=0.011..0.013 rows=10 loops=1)

  • Recheck Cond: (recipe_id = ANY ('{1,2,3,4}'::integer[]))
  • Heap Blocks: exact=1
11. 0.008 0.008 ↑ 4.5 10 1

Bitmap Index Scan on index_recipe_ingredients_on_recipe_id_and_ingredient_id (cost=0.00..8.95 rows=45 width=0) (actual time=0.008..0.008 rows=10 loops=1)

  • Index Cond: (recipe_id = ANY ('{1,2,3,4}'::integer[]))
12. 0.090 0.090 ↑ 1.4 7 10

Function Scan on unnest a (cost=0.00..0.10 rows=10 width=12) (actual time=0.007..0.009 rows=7 loops=10)

13. 40.289 43.754 ↓ 2.5 2,054 1

Hash (cost=1,570.16..1,570.16 rows=828 width=16) (actual time=43.754..43.754 rows=2,054 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 137kB
14. 2.448 3.465 ↓ 2.5 2,054 1

Bitmap Heap Scan on items_0 items_0_1 (cost=183.11..1,570.16 rows=828 width=16) (actual time=1.128..3.465 rows=2,054 loops=1)

  • Recheck Cond: (inventory_area_id = 20,636)
  • Filter: (available AND visible AND (retailer_id = 29))
  • Heap Blocks: exact=1,067
15. 0.069 1.017 ↓ 0.0 0 1

BitmapAnd (cost=183.11..183.11 rows=828 width=0) (actual time=1.017..1.017 rows=0 loops=1)

16. 0.468 0.468 ↑ 1.0 4,404 1

Bitmap Index Scan on search_index_v2_items_0 (cost=0.00..49.03 rows=4,404 width=0) (actual time=0.468..0.468 rows=4,404 loops=1)

  • Index Cond: (inventory_area_id = 20,636)
17. 0.480 0.480 ↓ 1.8 6,025 1

Bitmap Index Scan on items_0_available_visible_idx (cost=0.00..133.42 rows=3,313 width=0) (actual time=0.480..0.480 rows=6,025 loops=1)

  • Index Cond: ((available = true) AND (visible = true))
18. 0.242 0.242 ↑ 1.0 1 22

Index Scan using items_availabilities_0_item_id_idx on items_availabilities_0 items_availabilities (cost=0.29..1.69 rows=1 width=20) (actual time=0.011..0.011 rows=1 loops=22)

  • Index Cond: (item_id = items_0_1.item_id)
  • Filter: ((inventory_area_id = 20,636) AND (inventory_area_id = items_0_1.inventory_area_id))
Planning time : 0.618 ms
Execution time : 44.534 ms