explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OzTs

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 11.486 ↑ 3.2 8 1

Subquery Scan on items_0 (cost=2,485.73..2,488.43 rows=26 width=752) (actual time=11.451..11.486 rows=8 loops=1)

  • Filter: (items_0.ingredient_ranking <= 1)
  • Rows Removed by Filter: 14
2. 0.059 11.476 ↑ 3.5 22 1

WindowAgg (cost=2,485.73..2,487.47 rows=77 width=756) (actual time=11.445..11.476 rows=22 loops=1)

3. 0.663 11.417 ↑ 3.5 22 1

Sort (cost=2,485.73..2,485.93 rows=77 width=747) (actual time=11.415..11.417 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: 48kB
4. 0.031 10.754 ↑ 3.5 22 1

Nested Loop Left Join (cost=1,601.00..2,483.32 rows=77 width=747) (actual time=10.604..10.754 rows=22 loops=1)

  • Filter: (COALESCE(items_availabilities.availability_score, '0.87'::double precision) > '0.525'::double precision)
5. 1.728 10.657 ↑ 10.5 22 1

Hash Join (cost=1,600.71..2,089.08 rows=232 width=739) (actual time=10.583..10.657 rows=22 loops=1)

  • Hash Cond: (recipe_ingredient_products.ingredient_id = recipe_ingredients.ingredient_id)
6. 0.617 8.895 ↑ 60.6 17 1

Hash Join (cost=1,580.51..2,058.84 rows=1,030 width=739) (actual time=8.834..8.895 rows=17 loops=1)

  • Hash Cond: ((unnest(recipe_ingredient_products.product_ids)) = items_0_1.product_id)
7. 0.043 0.111 ↑ 235.2 54 1

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

8. 0.028 0.068 ↑ 235.2 54 1

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

9. 0.040 0.040 ↑ 158.8 8 1

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

10. 2.998 8.167 ↓ 2.5 2,054 1

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

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,435kB
11. 2.916 5.169 ↓ 2.5 2,054 1

Bitmap Heap Scan on items_0 items_0_1 (cost=183.11..1,570.16 rows=828 width=731) (actual time=2.383..5.169 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
12. 0.072 2.253 ↓ 0.0 0 1

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

13. 0.981 0.981 ↑ 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.981..0.981 rows=4,404 loops=1)

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

  • Index Cond: ((available = true) AND (visible = true))
15. 0.008 0.034 ↑ 4.5 10 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.010 0.026 ↑ 4.5 10 1

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

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

  • Index Cond: (recipe_id = ANY ('{1,2,3,4}'::integer[]))
18. 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))