explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5MDi

Settings
# exclusive inclusive rows x rows loops node
1. 1.765 19.467 ↓ 1.4 164 1

HashAggregate (cost=1,213.08..1,215.54 rows=120 width=189) (actual time=19.246..19.467 rows=164 loops=1)

  • Hash Key: true
  • Hash Key: retailer_products.gluten_free
  • Hash Key: retailer_products.fat_free
  • Hash Key: retailer_products.vegan
  • Hash Key: retailer_products.kosher
  • Hash Key: retailer_products.sugar_free
  • Hash Key: items_0.usa_snap_eligible
  • Hash Key: items_0.on_sale
  • Hash Key: retailer_products_cpgs.has_deal
  • Hash Key: retailer_products.alcoholic
  • Hash Key: retailer_products.store_brand
  • Hash Key: retailer_products.alcohol_type
  • Hash Key: retailer_products.alcohol_varietal
  • Hash Key: retailer_products.unit_value
  • Hash Key: CASE WHEN ((((items_0.full_price)::numeric)::double precision >= '0'::double precision) AND (((items_0.full_price)::numeric)::double precision < '10'::double precision)) THEN '*-10.0'::text WHEN ((((items_0.full_price)::numeric)::double precision >= '10'::double precision) AND (((items_0.full_price)::numeric)::double precision < '20'::double precision)) THEN '10.0-20.0'::text WHEN ((((items_0.full_price)::numeric)::double precision >= '20'::double precision) AND (((items_0.full_price)::numeric)::double precision < '30'::double precision)) THEN '20.0-30.0'::text WHEN ((((items_0.full_price)::numeric)::double precision >= '30'::double precision) AND (((items_0.full_price)::numeric)::double precision < '40'::double precision)) THEN '30.0-40.0'::text WHEN ((((items_0.full_price)::numeric)::double precision >= '40'::double precision) AND (((items_0.full_price)::numeric)::double precision < '50'::double precision)) THEN '40.0-50.0'::text WHEN ((((items_0.full_price)::numeric)::double precision >= '50'::double precision) AND (((items_0.full_price)::numeric)::double precision < '2147483647'::double precision)) THEN '50.0-*'::text ELSE NULL::text END
  • Hash Key: CASE WHEN ((((retailer_products.alcohol_by_volume)::numeric)::double precision >= '0'::double precision) AND (((retailer_products.alcohol_by_volume)::numeric)::double precision < '4.5'::double precision)) THEN '*-4.5'::text WHEN ((((retailer_products.alcohol_by_volume)::numeric)::double precision >= '4.5'::double precision) AND (((retailer_products.alcohol_by_volume)::numeric)::double precision < '6'::double precision)) THEN '4.5-6.0'::text WHEN ((((retailer_products.alcohol_by_volume)::numeric)::double precision >= '6'::double precision) AND (((retailer_products.alcohol_by_volume)::numeric)::double precision < '8'::double precision)) THEN '6.0-8.0'::text WHEN ((((retailer_products.alcohol_by_volume)::numeric)::double precision >= '8'::double precision) AND (((retailer_products.alcohol_by_volume)::numeric)::double precision < '2147483647'::double precision)) THEN '8.0-*'::text ELSE NULL::text END
  • Hash Key: CASE WHEN ((((retailer_products.alcohol_rating)::numeric)::double precision >= '70'::double precision) AND (((retailer_products.alcohol_rating)::numeric)::double precision < '79'::double precision)) THEN '70.0-79.0'::text WHEN ((((retailer_products.alcohol_rating)::numeric)::double precision >= '80'::double precision) AND (((retailer_products.alcohol_rating)::numeric)::double precision < '89'::double precision)) THEN '80.0-89.0'::text WHEN ((((retailer_products.alcohol_rating)::numeric)::double precision >= '90'::double precision) AND (((retailer_products.alcohol_rating)::numeric)::double precision < '100'::double precision)) THEN '90.0-100.0'::text ELSE NULL::text END
  • Hash Key: retailer_products.organic
  • Hash Key: retailer_products.brand_id, retailer_products.brand_name
  • Hash Key: retailer_products.department_id, retailer_products.department_name, retailer_products.aisle_id, retailer_products.aisle_name
2. 1.532 17.702 ↓ 16.7 200 1

Nested Loop Left Join (cost=502.77..1,212.90 rows=12 width=190) (actual time=8.059..17.702 rows=200 loops=1)

3. 0.758 14.770 ↓ 16.7 200 1

Nested Loop (cost=502.33..1,178.67 rows=12 width=115) (actual time=8.019..14.770 rows=200 loops=1)

4. 0.748 12.212 ↓ 6.5 200 1

Nested Loop Left Join (cost=501.89..1,096.28 rows=31 width=18) (actual time=7.989..12.212 rows=200 loops=1)

  • Filter: (COALESCE(items_availabilities.availability_score, '0.87'::double precision) > '0.35'::double precision)
5. 2.630 10.264 ↓ 2.2 200 1

Bitmap Heap Scan on items_0 (cost=501.33..835.65 rows=93 width=30) (actual time=7.964..10.264 rows=200 loops=1)

  • Recheck Cond: ((full_text @@ '''appl'''::tsquery) AND (inventory_area_id = 32))
  • Filter: (available AND visible)
  • Rows Removed by Filter: 829
  • Heap Blocks: exact=969
6. 7.634 7.634 ↓ 3.4 1,031 1

Bitmap Index Scan on search_index_items_0 (cost=0.00..501.30 rows=300 width=0) (actual time=7.633..7.634 rows=1,031 loops=1)

  • Index Cond: ((full_text @@ '''appl'''::tsquery) AND (inventory_area_id = 32))
7. 1.200 1.200 ↓ 0.0 0 200

Index Scan using items_availabilities_0_item_id_idx on items_availabilities_0 items_availabilities (cost=0.57..2.79 rows=1 width=20) (actual time=0.006..0.006 rows=0 loops=200)

  • Index Cond: (item_id = items_0.item_id)
  • Filter: ((inventory_area_id = 32) AND (inventory_area_id = items_0.inventory_area_id))
8. 1.800 1.800 ↑ 1.0 1 200

Index Scan using index_retailer_products_on_retailer_id_and_product_id on retailer_products (cost=0.44..2.66 rows=1 width=105) (actual time=0.009..0.009 rows=1 loops=200)

  • Index Cond: ((retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
9. 1.400 1.400 ↑ 1.0 1 200

Index Scan using index_retailer_products_cpgs_cpk on retailer_products_cpgs (cost=0.44..2.66 rows=1 width=9) (actual time=0.007..0.007 rows=1 loops=200)

  • Index Cond: ((retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
Planning time : 2.214 ms
Execution time : 19.971 ms