explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gkji

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 7.034 ↑ 12.1 8 1

Subquery Scan on items_0 (cost=2,510.29..2,519.78 rows=97 width=4) (actual time=7.014..7.034 rows=8 loops=1)

  • Filter: (items_0.ingredient_ranking <= 1)
  • Rows Removed by Filter: 14
2. 0.028 7.031 ↑ 13.3 22 1

WindowAgg (cost=2,510.29..2,516.13 rows=292 width=429) (actual time=7.013..7.031 rows=22 loops=1)

3. 0.010 7.003 ↑ 13.3 22 1

Sort (cost=2,510.29..2,511.02 rows=292 width=8) (actual time=7.001..7.003 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.614 6.993 ↑ 13.3 22 1

Hash Join (cost=1,991.24..2,498.34 rows=292 width=8) (actual time=6.956..6.993 rows=22 loops=1)

  • Hash Cond: (recipe_ingredient_products.ingredient_id = recipe_ingredients.ingredient_id)
5. 0.012 6.358 ↑ 20.2 17 1

Hash Join (cost=1,962.62..2,458.65 rows=343 width=8) (actual time=6.325..6.358 rows=17 loops=1)

  • Hash Cond: ((unnest(recipe_ingredient_products.product_ids)) = items_0_1.product_id)
6. 0.017 0.037 ↑ 235.2 54 1

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

7. 0.012 0.020 ↑ 235.2 54 1

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

8. 0.008 0.008 ↑ 158.8 8 1

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

9. 0.336 6.309 ↓ 7.4 2,054 1

Hash (cost=1,959.17..1,959.17 rows=276 width=4) (actual time=6.309..6.309 rows=2,054 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 105kB
10. 0.999 5.973 ↓ 7.4 2,054 1

Hash Right Join (cost=1,689.00..1,959.17 rows=276 width=4) (actual time=4.212..5.973 rows=2,054 loops=1)

  • Hash Cond: ((items_availabilities.inventory_area_id = items_0_1.inventory_area_id) AND (items_availabilities.item_id = items_0_1.item_id))
  • Filter: (COALESCE(items_availabilities.availability_score, '0.87'::double precision) > '0.525'::double precision)
11. 0.783 1.008 ↑ 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.239..1.008 rows=4,404 loops=1)

  • Recheck Cond: (inventory_area_id = 20,636)
  • Heap Blocks: exact=159
12. 0.225 0.225 ↑ 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.225..0.225 rows=4,404 loops=1)

  • Index Cond: (inventory_area_id = 20,636)
13. 0.450 3.966 ↓ 2.5 2,054 1

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

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 137kB
14. 2.324 3.516 ↓ 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.316..3.516 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.070 1.192 ↓ 0.0 0 1

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

16. 0.546 0.546 ↑ 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.546..0.546 rows=4,404 loops=1)

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

  • Index Cond: ((available = true) AND (visible = true))
18. 0.005 0.021 ↑ 17.0 10 1

Hash (cost=26.49..26.49 rows=170 width=4) (actual time=0.021..0.021 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.005 0.016 ↑ 17.0 10 1

Bitmap Heap Scan on recipe_ingredients (cost=11.61..26.49 rows=170 width=4) (actual time=0.015..0.016 rows=10 loops=1)

  • Recheck Cond: (recipe_id = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}'::integer[]))
  • Heap Blocks: exact=1
20. 0.011 0.011 ↑ 17.0 10 1

Bitmap Index Scan on index_recipe_ingredients_on_recipe_id_and_ingredient_id (cost=0.00..11.56 rows=170 width=0) (actual time=0.011..0.011 rows=10 loops=1)

  • Index Cond: (recipe_id = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}'::integer[]))
Planning time : 0.590 ms
Execution time : 7.140 ms