explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gqd5

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 2.796 ↑ 1.6 8 1

Subquery Scan on items_0 (cost=1,962.84..1,964.11 rows=13 width=4) (actual time=2.773..2.796 rows=8 loops=1)

  • Filter: (items_0.ingredient_ranking <= 1)
  • Rows Removed by Filter: 14
2. 0.031 2.793 ↑ 1.8 22 1

WindowAgg (cost=1,962.84..1,963.62 rows=39 width=429) (actual time=2.773..2.793 rows=22 loops=1)

3. 0.011 2.762 ↑ 1.8 22 1

Sort (cost=1,962.84..1,962.94 rows=39 width=8) (actual time=2.760..2.762 rows=22 loops=1)

  • Sort Key: recipe_ingredient_products.ingredient_id, (array_position(recipe_ingredient_products.product_ids, (unnest(recipe_ingredient_products.product_ids))))
  • Sort Method: quicksort Memory: 26kB
4. 0.018 2.751 ↑ 1.8 22 1

Nested Loop Left Join (cost=19.63..1,961.81 rows=39 width=8) (actual time=2.458..2.751 rows=22 loops=1)

  • Filter: (COALESCE(items_availabilities.availability_score, '0.87'::double precision) > '0.525'::double precision)
5. 0.043 2.667 ↑ 5.4 22 1

Nested Loop (cost=19.34..1,761.29 rows=118 width=20) (actual time=2.451..2.667 rows=22 loops=1)

6. 2.432 2.488 ↑ 21.5 68 1

Hash Join (cost=19.06..542.13 rows=1,460 width=12) (actual time=2.440..2.488 rows=68 loops=1)

  • Hash Cond: (recipe_ingredient_products.ingredient_id = recipe_ingredients.ingredient_id)
7. 0.021 0.041 ↑ 235.2 54 1

Result (cost=0.00..317.98 rows=12,700 width=12) (actual time=0.009..0.041 rows=54 loops=1)

8. 0.014 0.020 ↑ 235.2 54 1

ProjectSet (cost=0.00..95.72 rows=12,700 width=40) (actual time=0.007..0.020 rows=54 loops=1)

9. 0.006 0.006 ↑ 158.8 8 1

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

10. 0.004 0.015 ↑ 2.3 10 1

Hash (cost=18.77..18.77 rows=23 width=4) (actual time=0.015..0.015 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
11. 0.005 0.011 ↑ 2.3 10 1

Bitmap Heap Scan on recipe_ingredients (cost=8.48..18.77 rows=23 width=4) (actual time=0.010..0.011 rows=10 loops=1)

  • Recheck Cond: (recipe_id = ANY ('{1,2}'::integer[]))
  • Heap Blocks: exact=1
12. 0.006 0.006 ↑ 2.3 10 1

Bitmap Index Scan on index_recipe_ingredients_on_recipe_id_and_ingredient_id (cost=0.00..8.47 rows=23 width=0) (actual time=0.006..0.006 rows=10 loops=1)

  • Index Cond: (recipe_id = ANY ('{1,2}'::integer[]))
13. 0.136 0.136 ↓ 0.0 0 68

Index Scan using index_items_0_on_product_id_and_inventory_area_id on items_0 items_0_1 (cost=0.29..0.84 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=68)

  • Index Cond: ((product_id = (unnest(recipe_ingredient_products.product_ids))) AND (inventory_area_id = 20,636))
  • Filter: (available AND visible AND (retailer_id = 29))
  • Rows Removed by Filter: 0
14. 0.066 0.066 ↑ 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.003..0.003 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.621 ms
Execution time : 2.868 ms