explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hQMt

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 6.343 ↑ 321.9 8 1

Subquery Scan on items_0 (cost=25,818.57..26,069.67 rows=2,575 width=8) (actual time=6.320..6.343 rows=8 loops=1)

  • Filter: (items_0.ingredient_ranking <= 1)
  • Rows Removed by Filter: 14
2. 0.034 6.339 ↑ 351.2 22 1

WindowAgg (cost=25,818.57..25,973.09 rows=7,726 width=429) (actual time=6.319..6.339 rows=22 loops=1)

3. 0.011 6.305 ↑ 351.2 22 1

Sort (cost=25,818.57..25,837.89 rows=7,726 width=12) (actual time=6.303..6.305 rows=22 loops=1)

  • Sort Key: recipe_ingredient_products.ingredient_id, (generate_series(1, array_upper(recipe_ingredient_products.product_ids, 1)))
  • Sort Method: quicksort Memory: 26kB
4. 0.011 6.294 ↑ 351.2 22 1

Hash Join (cost=2,020.24..25,319.65 rows=7,726 width=12) (actual time=6.254..6.294 rows=22 loops=1)

  • Hash Cond: (recipe_ingredient_products.ingredient_id = recipe_ingredients.ingredient_id)
5. 0.015 6.266 ↑ 2,019.9 17 1

Hash Left Join (cost=2,000.04..24,964.65 rows=34,338 width=12) (actual time=6.232..6.266 rows=17 loops=1)

  • Hash Cond: ((items_0_1.inventory_area_id = items_availabilities.inventory_area_id) AND (items_0_1.item_id = items_availabilities.item_id))
  • Filter: (COALESCE(items_availabilities.availability_score, '0.87'::double precision) > '0.525'::double precision)
6. 0.017 3.991 ↑ 6,059.6 17 1

Hash Join (cost=1,580.51..24,004.22 rows=103,013 width=24) (actual time=3.963..3.991 rows=17 loops=1)

  • Hash Cond: ((unnest(recipe_ingredient_products.product_ids)) = items_0_1.product_id)
7. 0.019 0.025 ↑ 23,518.5 54 1

ProjectSet (cost=0.00..6,388.58 rows=1,270,000 width=12) (actual time=0.009..0.025 rows=54 loops=1)

8. 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.004..0.006 rows=8 loops=1)

9. 0.443 3.949 ↓ 2.5 2,054 1

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

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 137kB
10. 2.374 3.506 ↓ 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.256..3.506 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
11. 0.071 1.132 ↓ 0.0 0 1

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

12. 0.520 0.520 ↑ 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.520..0.520 rows=4,404 loops=1)

  • Index Cond: (inventory_area_id = 20,636)
13. 0.541 0.541 ↓ 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.541..0.541 rows=6,025 loops=1)

  • Index Cond: ((available = true) AND (visible = true))
14. 0.960 2.260 ↑ 1.0 4,404 1

Hash (cost=353.47..353.47 rows=4,404 width=20) (actual time=2.260..2.260 rows=4,404 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 305kB
15. 1.063 1.300 ↑ 1.0 4,404 1

Bitmap Heap Scan on items_availabilities_0 items_availabilities (cost=106.42..353.47 rows=4,404 width=20) (actual time=0.252..1.300 rows=4,404 loops=1)

  • Recheck Cond: (inventory_area_id = 20,636)
  • Heap Blocks: exact=159
16. 0.237 0.237 ↑ 1.0 4,404 1

Bitmap Index Scan on items_availabilities_0_inventory_area_id_idx (cost=0.00..105.32 rows=4,404 width=0) (actual time=0.237..0.237 rows=4,404 loops=1)

  • Index Cond: (inventory_area_id = 20,636)
17. 0.005 0.017 ↑ 4.5 10 1

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

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

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

  • Recheck Cond: (recipe_id = ANY ('{1,2,3,4}'::integer[]))
  • Heap Blocks: exact=1
19. 0.007 0.007 ↑ 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.007..0.007 rows=10 loops=1)

  • Index Cond: (recipe_id = ANY ('{1,2,3,4}'::integer[]))
Planning time : 3.481 ms
Execution time : 6.491 ms