explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GWPk

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 5.347 ↓ 2.8 22 1

Unique (cost=1,673.54..1,673.62 rows=8 width=24) (actual time=5.340..5.347 rows=22 loops=1)

2. 0.009 5.341 ↓ 2.8 22 1

Sort (cost=1,673.54..1,673.56 rows=8 width=24) (actual time=5.340..5.341 rows=22 loops=1)

  • Sort Key: recipe_ingredient_products.ingredient_id, items_0.product_id, (row_number() OVER (?))
  • Sort Method: quicksort Memory: 26kB
3. 0.020 5.332 ↓ 2.8 22 1

WindowAgg (cost=1,673.26..1,673.42 rows=8 width=24) (actual time=5.317..5.332 rows=22 loops=1)

4. 0.012 5.312 ↓ 2.8 22 1

Sort (cost=1,673.26..1,673.28 rows=8 width=16) (actual time=5.310..5.312 rows=22 loops=1)

  • Sort Key: recipe_ingredient_products.ingredient_id, a.raw_rank
  • Sort Method: quicksort Memory: 26kB
5. 0.015 5.300 ↓ 2.8 22 1

Nested Loop Left Join (cost=1,599.86..1,673.14 rows=8 width=16) (actual time=5.173..5.300 rows=22 loops=1)

  • Filter: (COALESCE(items_availabilities.availability_score, '0.87'::double precision) > '0.525'::double precision)
6. 0.019 5.219 ↑ 1.0 22 1

Hash Join (cost=1,599.57..1,634.06 rows=23 width=28) (actual time=5.159..5.219 rows=22 loops=1)

  • Hash Cond: (a.product_id = items_0.product_id)
7. 0.017 0.585 ↑ 3.4 68 1

Nested Loop (cost=19.06..52.94 rows=230 width=16) (actual time=0.538..0.585 rows=68 loops=1)

8. 0.515 0.538 ↑ 2.3 10 1

Hash Join (cost=19.06..48.34 rows=23 width=36) (actual time=0.531..0.538 rows=10 loops=1)

  • Hash Cond: (recipe_ingredient_products.ingredient_id = recipe_ingredients.ingredient_id)
9. 0.007 0.007 ↑ 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.007 rows=8 loops=1)

10. 0.005 0.016 ↑ 2.3 10 1

Hash (cost=18.77..18.77 rows=23 width=4) (actual time=0.016..0.016 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.030 0.030 ↑ 1.4 7 10

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

14. 0.416 4.615 ↓ 2.5 2,054 1

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

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 137kB
15. 2.195 4.199 ↓ 2.5 2,054 1

Bitmap Heap Scan on items_0 (cost=183.11..1,570.16 rows=828 width=16) (actual time=2.102..4.199 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
16. 0.056 2.004 ↓ 0.0 0 1

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

17. 1.511 1.511 ↑ 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=1.511..1.511 rows=4,404 loops=1)

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

  • Index Cond: ((available = true) AND (visible = true))
19. 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.item_id)
  • Filter: ((inventory_area_id = 20,636) AND (inventory_area_id = items_0.inventory_area_id))
Planning time : 43.312 ms
Execution time : 5.445 ms