explain.depesz.com

PostgreSQL's explain analyze made readable

Result: j1VW

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 3.605 ↓ 2.4 26 1

Limit (cost=667.80..667.83 rows=11 width=5,745) (actual time=3.588..3.605 rows=26 loops=1)

  • Output: 214, (COALESCE(items_availabilities.availability_score, '0.87'::double precision)), ((COALESCE(items_availabilities.availability_score, '0.87'::double precision) > '0'::double precision)), ((ts_rank(items_0.full_text_v2, '''appl'''::tsquery, 1) * '100'::double precision)), (COALESCE(products_conversions.score, '0'::double precision)), ((((ts_rank(items_0.full_text_v2, '''appl'''::tsquery, 1) * '100'::double precision) + (COALESCE(products_conversions.score, '0'::double precision) * '1000'::double precision)) * '1'::double precision)), (count(*) OVER (?)), retailer_term_v2s.score, generic_term_v2s.score, (COALESCE(retailer_term_v2s.score, generic_term_v2s.score)), deprecated_retailer_products_cpgs.cpg_elastic_retailer_product_id, retailer_products.retailer_id, retailer_products.product_id, retailer_products.elastic_retailer_product_id, retailer_products.generated_at, retailer_products._aisle_sort_orders, retailer_products._dept_sort_orders, retailer_products.aisle_id, retailer_products.aisle_ids, retailer_products.aisle_name, retailer_products.aisle_sort_order, retailer_products.alcohol_attributes, retailer_products.alcohol_by_volume, retailer_products.alcoholic, retailer_products.allergens_keywords, retailer_products.bottle, retailer_products.brand_id, retailer_products.brand_name, retailer_products.calories, retailer_products.carbohydrate, retailer_products.canonical_product, retailer_products.cholesterol, retailer_products.cch_group_code, retailer_products.cch_item_code, retailer_products.classified_code, retailer_products.country_id, retailer_products.department_id, retailer_products.department_ids, retailer_products.department_name, retailer_products.dept_sort_order, retailer_products.description, retailer_products.details, retailer_products.directions, retailer_products.display_name, retailer_products.edible, retailer_products.fat, retailer_products.fat_calories, retailer_products.fat_free, retailer_products.fiber, retailer_products.form_factor, retailer_products.form_factor_descriptor, retailer_products.gluten_free, retailer_products.has_image, retailer_products.image_list, retailer_products.image_url, retailer_products.images, retailer_products.in_season, retailer_products.ingredients, retailer_products.is_organic, retailer_products.keywords, retailer_products.kosher, retailer_products.large_image_url, retailer_products.locale_code, retailer_products.low_fat, retailer_products.margin_mix, retailer_products.monounsaturated_fat, retailer_products.name, retailer_products.normalized_weight_g, retailer_products.nutrition_sources, retailer_products.organic, retailer_products.original_name, retailer_products.polyunsaturated_fat, retailer_products.potassium, retailer_products.primary_aisle_id, retailer_products.primary_department_id, retailer_products.primary_image_url, retailer_products.protein, retailer_products.product_category, retailer_products.product_category_taxonomy, retailer_products.product_codes, retailer_products.product_enrichment_keywords, retailer_products.prop65_chemicals, retailer_products.saturated_fat, retailer_products.seasonality_description, retailer_products.serving_size, retailer_products.servings_per_container, retailer_products.should_scan, retailer_products.similar_name, retailer_products.size_uom, retailer_products.size_value, retailer_products.sodium, retailer_products.store_brand, retailer_products.sugars, retailer_products.sugar_free, retailer_products.synthetic_codes, retailer_products.thumb_image_url, retailer_products.trans_fat, retailer_products.unit_attributes, retailer_products.unit_count, retailer_products.vegan, retailer_products.vegetarian, retailer_products.warehouse_id, retailer_products.warnings, retailer_products.updated_at, retailer_products.product_category_l1_name, retailer_products.product_category_l2_name, retailer_products.product_category_l3_name, retailer_products.product_category_l4_name, retailer_products.product_category_l5_name, retailer_products.product_category_l6_name, retailer_products.product_category_l1_id, retailer_products.product_category_l2_id, retailer_products.product_category_l3_id, retailer_products.product_category_l4_id, retailer_products.product_category_l5_id, retailer_products.product_category_l6_id, retailer_products.prop65_text, retailer_products.retailer_reference_code, retailer_products.product_category_taxonomy_l4, retailer_products.common_weight, retailer_products.min_picked_weight, retailer_products.suggested_incremental_weight, retailer_products.aisle_names, retailer_products.department_names, retailer_products.antibiotic_free, retailer_products.breed_angus, retailer_products.breed_berkshire, retailer_products.breed_duroc_pork, retailer_products.breed_gloucestershire, retailer_products.breed_kobe_wagyu, retailer_products.breed_kurobata_berkshire, retailer_products.breed_mangalitsapork, retailer_products.breed_piedmontese, retailer_products.breed_veal, retailer_products.breed_wagyu, retailer_products.cooking_method_braise, retailer_products.cooking_method_grill, retailer_products.cooking_method_pan_fry, retailer_products.cooking_method_roast, retailer_products.farmed, retailer_products.free_range, retailer_products.grade, retailer_products.grass_fed, retailer_products.halal, retailer_products.hormone_free, retailer_products.meat_state, retailer_products.non_gmo, retailer_products.origin, retailer_products.packaging, retailer_products.pasture_raised, retailer_products.prep_breaded_crusted, retailer_products.prep_cured, retailer_products.prep_dry_aged, retailer_products.prep_glazed, retailer_products.prep_grilled, retailer_products.prep_marinated, retailer_products.prep_roasted, retailer_products.prep_rubbed, retailer_products.prep_seasoned, retailer_products.prep_smoked, retailer_products.prep_uncured, retailer_products.primal_cut, retailer_products.trim_bone_in, retailer_products.trim_boneless, retailer_products.trim_deveined, retailer_products.trim_dressed, retailer_products.trim_frenched, retailer_products.trim_head_on, retailer_products.trim_peeled, retailer_products.trim_shell_on, retailer_products.trim_shredded, retailer_products.trim_skin_on, retailer_products.trim_skinless, retailer_products.trim_sliced, retailer_products.trim_stuffed, retailer_products.trim_tail_off, retailer_products.trim_thick_cut, retailer_products.trim_thin_cut, retailer_products.wild_caught, retailer_products.product_tags, retailer_products.alcohol_type, retailer_products.alcohol_varietal, retailer_products.full_text_v1, retailer_products.dairy_free, retailer_products.temporary_override_elastic_retailer_product_id, retailer_products.milk_type_cow, retailer_products.milk_type_goat, retailer_products.milk_type_sheep, retailer_products.unit_value, retailer_products.is_catering, retailer_products.usa_snap_eligible, retailer_products.alcohol_rating, retailer_products.full_text_v2, retailer_products.secondary_aisle_ids, retailer_products.secondary_department_ids, retailer_products.variant_group_id, retailer_products.variant_attributes, retailer_products.product_codes_array, retailer_products.color, retailer_products.color_hex_code, retailer_products.flavor, deprecated_items_denormalizeds_0.elastic_item_id, deprecated_items_denormalizeds_0.retailer_id, deprecated_items_denormalizeds_0.product_id, deprecated_items_denormalizeds_0.inventory_area_id, deprecated_items_denormalizeds_0.item_id, deprecated_items_denormalizeds_0.generated_at, deprecated_items_denormalizeds_0.blackout_times, deprecated_items_denormalizeds_0.buy_n_get_n, deprecated_items_denormalizeds_0.combo_promotions, deprecated_items_denormalizeds_0.configurable_products, deprecated_items_denormalizeds_0.disclaimer, deprecated_items_denormalizeds_0.display_size, deprecated_items_denormalizeds_0.in_store_location, deprecated_items_denormalizeds_0.item_tax_data, deprecated_items_denormalizeds_0.retailer_department, deprecated_items_denormalizeds_0.retailer_lookup_code, deprecated_items_denormalizeds_0.retailer_promotion_details, deprecated_items_denormalizeds_0.retailer_reference_code, deprecated_items_denormalizeds_0.retailer_sales_tax_code, deprecated_items_denormalizeds_0.retailer_specific_metadata, deprecated_items_denormalizeds_0.size, deprecated_items_denormalizeds_0.storage_temp, deprecated_items_denormalizeds_0.unata_fulfillment_types, deprecated_items_denormalizeds_0.zone_ids, deprecated_items_denormalizeds_0.elastic_item_id, deprecated_items_denormalizeds_0.temporary_override_elastic_item_id, deprecated_items_denormalizeds_0.vertex_tax_code, deprecated_items_denormalizeds_0.combo_promotions_jsonb, deprecated_retailer_products_mls.data, deprecated_retailer_products_cpgs.data, items_0.retailer_id, items_0.product_id, items_0.inventory_area_id, items_0.item_id, items_0.elastic_item_id, items_0.generated_at, items_0.alcoholic, items_0.available, items_0.balance_on_hand_qty, items_0.bottle_deposit_bank_id, items_0.bottle_deposit_cents, items_0.bottle_deposit_currency, items_0.code_set_version, items_0.cost, items_0.cost_price_per_unit, items_0.cost_unit, items_0.full_price, items_0.has_retailer_promotion, items_0.is_alcoholic, items_0.item_type, items_0.loyalty_allowed, items_0.loyalty_cost_price_cents, items_0.loyalty_cost_price_currency, items_0.loyalty_cost_price_end_at, items_0.loyalty_cost_price_per_unit, items_0.loyalty_cost_price_start_at, items_0.loyalty_price, items_0.loyalty_price_active, items_0.loyalty_price_end_at, items_0.loyalty_price_start_at, items_0.on_sale, items_0.par_weight, items_0.price, items_0.price_multiple, items_0.price_per_unit, items_0.product_type, items_0.recently_shopped_at, items_0.retailer_product_score_browse_ctr_pop_v0, items_0.region_id, items_0.retailer_specified_alcoholic, items_0.retailer_sales_tax_rate, items_0.sale_allowed, items_0.sale_price_cents, items_0.sale_cost_price_per_unit, items_0.sale_end_at, items_0.sale_start_at, items_0.searchable, items_0.taxable, items_0.unata_available, items_0.unit, items_0.unit_count, items_0.unit_price, items_0.unlisted, items_0.use_weight, items_0.visible, items_0.created_at, items_0.updated_at, items_0.promotion_discount_begins_at, items_0.promotion_discount_ends_at, items_0.bogo_buy, items_0.bogo_get, items_0.max_in_cart, items_0.temporary_override_elastic_item_id, items_0.full_text, items_0.max_cart, items_0.cancel_lead_time, items_0.is_catering, items_0.order_lead_time, items_0.usa_snap_eligible, items_0.full_text_v2, items_0.fulfill_pickup, items_0.fulfill_delivery, items_0.fulfill_instore, items_0.loyalty_members_only, items_0.promotion_group_id, items_0.is_hot, items_0.item_id, (COALESCE(deprecated_items_denormalizeds_0.retailer_reference_code, retailer_products.retailer_reference_code)), 'item'::text, (count(*) OVER (?))
2. 0.472 3.598 ↓ 2.4 26 1

Sort (cost=667.80..667.83 rows=11 width=5,745) (actual time=3.587..3.598 rows=26 loops=1)

  • Output: 214, (COALESCE(items_availabilities.availability_score, '0.87'::double precision)), ((COALESCE(items_availabilities.availability_score, '0.87'::double precision) > '0'::double precision)), ((ts_rank(items_0.full_text_v2, '''appl'''::tsquery, 1) * '100'::double precision)), (COALESCE(products_conversions.score, '0'::double precision)), ((((ts_rank(items_0.full_text_v2, '''appl'''::tsquery, 1) * '100'::double precision) + (COALESCE(products_conversions.score, '0'::double precision) * '1000'::double precision)) * '1'::double precision)), (count(*) OVER (?)), retailer_term_v2s.score, generic_term_v2s.score, (COALESCE(retailer_term_v2s.score, generic_term_v2s.score)), deprecated_retailer_products_cpgs.cpg_elastic_retailer_product_id, retailer_products.retailer_id, retailer_products.product_id, retailer_products.elastic_retailer_product_id, retailer_products.generated_at, retailer_products._aisle_sort_orders, retailer_products._dept_sort_orders, retailer_products.aisle_id, retailer_products.aisle_ids, retailer_products.aisle_name, retailer_products.aisle_sort_order, retailer_products.alcohol_attributes, retailer_products.alcohol_by_volume, retailer_products.alcoholic, retailer_products.allergens_keywords, retailer_products.bottle, retailer_products.brand_id, retailer_products.brand_name, retailer_products.calories, retailer_products.carbohydrate, retailer_products.canonical_product, retailer_products.cholesterol, retailer_products.cch_group_code, retailer_products.cch_item_code, retailer_products.classified_code, retailer_products.country_id, retailer_products.department_id, retailer_products.department_ids, retailer_products.department_name, retailer_products.dept_sort_order, retailer_products.description, retailer_products.details, retailer_products.directions, retailer_products.display_name, retailer_products.edible, retailer_products.fat, retailer_products.fat_calories, retailer_products.fat_free, retailer_products.fiber, retailer_products.form_factor, retailer_products.form_factor_descriptor, retailer_products.gluten_free, retailer_products.has_image, retailer_products.image_list, retailer_products.image_url, retailer_products.images, retailer_products.in_season, retailer_products.ingredients, retailer_products.is_organic, retailer_products.keywords, retailer_products.kosher, retailer_products.large_image_url, retailer_products.locale_code, retailer_products.low_fat, retailer_products.margin_mix, retailer_products.monounsaturated_fat, retailer_products.name, retailer_products.normalized_weight_g, retailer_products.nutrition_sources, retailer_products.organic, retailer_products.original_name, retailer_products.polyunsaturated_fat, retailer_products.potassium, retailer_products.primary_aisle_id, retailer_products.primary_department_id, retailer_products.primary_image_url, retailer_products.protein, retailer_products.product_category, retailer_products.product_category_taxonomy, retailer_products.product_codes, retailer_products.product_enrichment_keywords, retailer_products.prop65_chemicals, retailer_products.saturated_fat, retailer_products.seasonality_description, retailer_products.serving_size, retailer_products.servings_per_container, retailer_products.should_scan, retailer_products.similar_name, retailer_products.size_uom, retailer_products.size_value, retailer_products.sodium, retailer_products.store_brand, retailer_products.sugars, retailer_products.sugar_free, retailer_products.synthetic_codes, retailer_products.thumb_image_url, retailer_products.trans_fat, retailer_products.unit_attributes, retailer_products.unit_count, retailer_products.vegan, retailer_products.vegetarian, retailer_products.warehouse_id, retailer_products.warnings, retailer_products.updated_at, retailer_products.product_category_l1_name, retailer_products.product_category_l2_name, retailer_products.product_category_l3_name, retailer_products.product_category_l4_name, retailer_products.product_category_l5_name, retailer_products.product_category_l6_name, retailer_products.product_category_l1_id, retailer_products.product_category_l2_id, retailer_products.product_category_l3_id, retailer_products.product_category_l4_id, retailer_products.product_category_l5_id, retailer_products.product_category_l6_id, retailer_products.prop65_text, retailer_products.retailer_reference_code, retailer_products.product_category_taxonomy_l4, retailer_products.common_weight, retailer_products.min_picked_weight, retailer_products.suggested_incremental_weight, retailer_products.aisle_names, retailer_products.department_names, retailer_products.antibiotic_free, retailer_products.breed_angus, retailer_products.breed_berkshire, retailer_products.breed_duroc_pork, retailer_products.breed_gloucestershire, retailer_products.breed_kobe_wagyu, retailer_products.breed_kurobata_berkshire, retailer_products.breed_mangalitsapork, retailer_products.breed_piedmontese, retailer_products.breed_veal, retailer_products.breed_wagyu, retailer_products.cooking_method_braise, retailer_products.cooking_method_grill, retailer_products.cooking_method_pan_fry, retailer_products.cooking_method_roast, retailer_products.farmed, retailer_products.free_range, retailer_products.grade, retailer_products.grass_fed, retailer_products.halal, retailer_products.hormone_free, retailer_products.meat_state, retailer_products.non_gmo, retailer_products.origin, retailer_products.packaging, retailer_products.pasture_raised, retailer_products.prep_breaded_crusted, retailer_products.prep_cured, retailer_products.prep_dry_aged, retailer_products.prep_glazed, retailer_products.prep_grilled, retailer_products.prep_marinated, retailer_products.prep_roasted, retailer_products.prep_rubbed, retailer_products.prep_seasoned, retailer_products.prep_smoked, retailer_products.prep_uncured, retailer_products.primal_cut, retailer_products.trim_bone_in, retailer_products.trim_boneless, retailer_products.trim_deveined, retailer_products.trim_dressed, retailer_products.trim_frenched, retailer_products.trim_head_on, retailer_products.trim_peeled, retailer_products.trim_shell_on, retailer_products.trim_shredded, retailer_products.trim_skin_on, retailer_products.trim_skinless, retailer_products.trim_sliced, retailer_products.trim_stuffed, retailer_products.trim_tail_off, retailer_products.trim_thick_cut, retailer_products.trim_thin_cut, retailer_products.wild_caught, retailer_products.product_tags, retailer_products.alcohol_type, retailer_products.alcohol_varietal, retailer_products.full_text_v1, retailer_products.dairy_free, retailer_products.temporary_override_elastic_retailer_product_id, retailer_products.milk_type_cow, retailer_products.milk_type_goat, retailer_products.milk_type_sheep, retailer_products.unit_value, retailer_products.is_catering, retailer_products.usa_snap_eligible, retailer_products.alcohol_rating, retailer_products.full_text_v2, retailer_products.secondary_aisle_ids, retailer_products.secondary_department_ids, retailer_products.variant_group_id, retailer_products.variant_attributes, retailer_products.product_codes_array, retailer_products.color, retailer_products.color_hex_code, retailer_products.flavor, deprecated_items_denormalizeds_0.elastic_item_id, deprecated_items_denormalizeds_0.retailer_id, deprecated_items_denormalizeds_0.product_id, deprecated_items_denormalizeds_0.inventory_area_id, deprecated_items_denormalizeds_0.item_id, deprecated_items_denormalizeds_0.generated_at, deprecated_items_denormalizeds_0.blackout_times, deprecated_items_denormalizeds_0.buy_n_get_n, deprecated_items_denormalizeds_0.combo_promotions, deprecated_items_denormalizeds_0.configurable_products, deprecated_items_denormalizeds_0.disclaimer, deprecated_items_denormalizeds_0.display_size, deprecated_items_denormalizeds_0.in_store_location, deprecated_items_denormalizeds_0.item_tax_data, deprecated_items_denormalizeds_0.retailer_department, deprecated_items_denormalizeds_0.retailer_lookup_code, deprecated_items_denormalizeds_0.retailer_promotion_details, deprecated_items_denormalizeds_0.retailer_reference_code, deprecated_items_denormalizeds_0.retailer_sales_tax_code, deprecated_items_denormalizeds_0.retailer_specific_metadata, deprecated_items_denormalizeds_0.size, deprecated_items_denormalizeds_0.storage_temp, deprecated_items_denormalizeds_0.unata_fulfillment_types, deprecated_items_denormalizeds_0.zone_ids, deprecated_items_denormalizeds_0.elastic_item_id, deprecated_items_denormalizeds_0.temporary_override_elastic_item_id, deprecated_items_denormalizeds_0.vertex_tax_code, deprecated_items_denormalizeds_0.combo_promotions_jsonb, deprecated_retailer_products_mls.data, deprecated_retailer_products_cpgs.data, items_0.retailer_id, items_0.product_id, items_0.inventory_area_id, items_0.item_id, items_0.elastic_item_id, items_0.generated_at, items_0.alcoholic, items_0.available, items_0.balance_on_hand_qty, items_0.bottle_deposit_bank_id, items_0.bottle_deposit_cents, items_0.bottle_deposit_currency, items_0.code_set_version, items_0.cost, items_0.cost_price_per_unit, items_0.cost_unit, items_0.full_price, items_0.has_retailer_promotion, items_0.is_alcoholic, items_0.item_type, items_0.loyalty_allowed, items_0.loyalty_cost_price_cents, items_0.loyalty_cost_price_currency, items_0.loyalty_cost_price_end_at, items_0.loyalty_cost_price_per_unit, items_0.loyalty_cost_price_start_at, items_0.loyalty_price, items_0.loyalty_price_active, items_0.loyalty_price_end_at, items_0.loyalty_price_start_at, items_0.on_sale, items_0.par_weight, items_0.price, items_0.price_multiple, items_0.price_per_unit, items_0.product_type, items_0.recently_shopped_at, items_0.retailer_product_score_browse_ctr_pop_v0, items_0.region_id, items_0.retailer_specified_alcoholic, items_0.retailer_sales_tax_rate, items_0.sale_allowed, items_0.sale_price_cents, items_0.sale_cost_price_per_unit, items_0.sale_end_at, items_0.sale_start_at, items_0.searchable, items_0.taxable, items_0.unata_available, items_0.unit, items_0.unit_count, items_0.unit_price, items_0.unlisted, items_0.use_weight, items_0.visible, items_0.created_at, items_0.updated_at, items_0.promotion_discount_begins_at, items_0.promotion_discount_ends_at, items_0.bogo_buy, items_0.bogo_get, items_0.max_in_cart, items_0.temporary_override_elastic_item_id, items_0.full_text, items_0.max_cart, items_0.cancel_lead_time, items_0.is_catering, items_0.order_lead_time, items_0.usa_snap_eligible, items_0.full_text_v2, items_0.fulfill_pickup, items_0.fulfill_delivery, items_0.fulfill_instore, items_0.loyalty_members_only, items_0.promotion_group_id, items_0.is_hot, items_0.item_id, (COALESCE(deprecated_items_denormalizeds_0.retailer_reference_code, retailer_products.retailer_reference_code)), 'item'::text, (count(*) OVER (?))
  • Sort Key: retailer_term_v2s.score DESC NULLS LAST, generic_term_v2s.score DESC NULLS LAST, ((((ts_rank(items_0.full_text_v2, '''appl'''::tsquery, 1) * '100'::double precision) + (COALESCE(products_conversions.score, '0'::double precision) * '1000'::double precision)) * '1'::double precision)) DESC, items_0.item_id DESC
  • Sort Method: quicksort Memory: 193kB
3. 0.704 3.126 ↓ 2.4 26 1

WindowAgg (cost=147.09..667.61 rows=11 width=5,745) (actual time=2.877..3.126 rows=26 loops=1)

  • Output: 214, COALESCE(items_availabilities.availability_score, '0.87'::double precision), (COALESCE(items_availabilities.availability_score, '0.87'::double precision) > '0'::double precision), (ts_rank(items_0.full_text_v2, '''appl'''::tsquery, 1) * '100'::double precision), COALESCE(products_conversions.score, '0'::double precision), (((ts_rank(items_0.full_text_v2, '''appl'''::tsquery, 1) * '100'::double precision) + (COALESCE(products_conversions.score, '0'::double precision) * '1000'::double precision)) * '1'::double precision), count(*) OVER (?), retailer_term_v2s.score, generic_term_v2s.score, COALESCE(retailer_term_v2s.score, generic_term_v2s.score), deprecated_retailer_products_cpgs.cpg_elastic_retailer_product_id, retailer_products.retailer_id, retailer_products.product_id, retailer_products.elastic_retailer_product_id, retailer_products.generated_at, retailer_products._aisle_sort_orders, retailer_products._dept_sort_orders, retailer_products.aisle_id, retailer_products.aisle_ids, retailer_products.aisle_name, retailer_products.aisle_sort_order, retailer_products.alcohol_attributes, retailer_products.alcohol_by_volume, retailer_products.alcoholic, retailer_products.allergens_keywords, retailer_products.bottle, retailer_products.brand_id, retailer_products.brand_name, retailer_products.calories, retailer_products.carbohydrate, retailer_products.canonical_product, retailer_products.cholesterol, retailer_products.cch_group_code, retailer_products.cch_item_code, retailer_products.classified_code, retailer_products.country_id, retailer_products.department_id, retailer_products.department_ids, retailer_products.department_name, retailer_products.dept_sort_order, retailer_products.description, retailer_products.details, retailer_products.directions, retailer_products.display_name, retailer_products.edible, retailer_products.fat, retailer_products.fat_calories, retailer_products.fat_free, retailer_products.fiber, retailer_products.form_factor, retailer_products.form_factor_descriptor, retailer_products.gluten_free, retailer_products.has_image, retailer_products.image_list, retailer_products.image_url, retailer_products.images, retailer_products.in_season, retailer_products.ingredients, retailer_products.is_organic, retailer_products.keywords, retailer_products.kosher, retailer_products.large_image_url, retailer_products.locale_code, retailer_products.low_fat, retailer_products.margin_mix, retailer_products.monounsaturated_fat, retailer_products.name, retailer_products.normalized_weight_g, retailer_products.nutrition_sources, retailer_products.organic, retailer_products.original_name, retailer_products.polyunsaturated_fat, retailer_products.potassium, retailer_products.primary_aisle_id, retailer_products.primary_department_id, retailer_products.primary_image_url, retailer_products.protein, retailer_products.product_category, retailer_products.product_category_taxonomy, retailer_products.product_codes, retailer_products.product_enrichment_keywords, retailer_products.prop65_chemicals, retailer_products.saturated_fat, retailer_products.seasonality_description, retailer_products.serving_size, retailer_products.servings_per_container, retailer_products.should_scan, retailer_products.similar_name, retailer_products.size_uom, retailer_products.size_value, retailer_products.sodium, retailer_products.store_brand, retailer_products.sugars, retailer_products.sugar_free, retailer_products.synthetic_codes, retailer_products.thumb_image_url, retailer_products.trans_fat, retailer_products.unit_attributes, retailer_products.unit_count, retailer_products.vegan, retailer_products.vegetarian, retailer_products.warehouse_id, retailer_products.warnings, retailer_products.updated_at, retailer_products.product_category_l1_name, retailer_products.product_category_l2_name, retailer_products.product_category_l3_name, retailer_products.product_category_l4_name, retailer_products.product_category_l5_name, retailer_products.product_category_l6_name, retailer_products.product_category_l1_id, retailer_products.product_category_l2_id, retailer_products.product_category_l3_id, retailer_products.product_category_l4_id, retailer_products.product_category_l5_id, retailer_products.product_category_l6_id, retailer_products.prop65_text, retailer_products.retailer_reference_code, retailer_products.product_category_taxonomy_l4, retailer_products.common_weight, retailer_products.min_picked_weight, retailer_products.suggested_incremental_weight, retailer_products.aisle_names, retailer_products.department_names, retailer_products.antibiotic_free, retailer_products.breed_angus, retailer_products.breed_berkshire, retailer_products.breed_duroc_pork, retailer_products.breed_gloucestershire, retailer_products.breed_kobe_wagyu, retailer_products.breed_kurobata_berkshire, retailer_products.breed_mangalitsapork, retailer_products.breed_piedmontese, retailer_products.breed_veal, retailer_products.breed_wagyu, retailer_products.cooking_method_braise, retailer_products.cooking_method_grill, retailer_products.cooking_method_pan_fry, retailer_products.cooking_method_roast, retailer_products.farmed, retailer_products.free_range, retailer_products.grade, retailer_products.grass_fed, retailer_products.halal, retailer_products.hormone_free, retailer_products.meat_state, retailer_products.non_gmo, retailer_products.origin, retailer_products.packaging, retailer_products.pasture_raised, retailer_products.prep_breaded_crusted, retailer_products.prep_cured, retailer_products.prep_dry_aged, retailer_products.prep_glazed, retailer_products.prep_grilled, retailer_products.prep_marinated, retailer_products.prep_roasted, retailer_products.prep_rubbed, retailer_products.prep_seasoned, retailer_products.prep_smoked, retailer_products.prep_uncured, retailer_products.primal_cut, retailer_products.trim_bone_in, retailer_products.trim_boneless, retailer_products.trim_deveined, retailer_products.trim_dressed, retailer_products.trim_frenched, retailer_products.trim_head_on, retailer_products.trim_peeled, retailer_products.trim_shell_on, retailer_products.trim_shredded, retailer_products.trim_skin_on, retailer_products.trim_skinless, retailer_products.trim_sliced, retailer_products.trim_stuffed, retailer_products.trim_tail_off, retailer_products.trim_thick_cut, retailer_products.trim_thin_cut, retailer_products.wild_caught, retailer_products.product_tags, retailer_products.alcohol_type, retailer_products.alcohol_varietal, retailer_products.full_text_v1, retailer_products.dairy_free, retailer_products.temporary_override_elastic_retailer_product_id, retailer_products.milk_type_cow, retailer_products.milk_type_goat, retailer_products.milk_type_sheep, retailer_products.unit_value, retailer_products.is_catering, retailer_products.usa_snap_eligible, retailer_products.alcohol_rating, retailer_products.full_text_v2, retailer_products.secondary_aisle_ids, retailer_products.secondary_department_ids, retailer_products.variant_group_id, retailer_products.variant_attributes, retailer_products.product_codes_array, retailer_products.color, retailer_products.color_hex_code, retailer_products.flavor, deprecated_items_denormalizeds_0.elastic_item_id, deprecated_items_denormalizeds_0.retailer_id, deprecated_items_denormalizeds_0.product_id, deprecated_items_denormalizeds_0.inventory_area_id, deprecated_items_denormalizeds_0.item_id, deprecated_items_denormalizeds_0.generated_at, deprecated_items_denormalizeds_0.blackout_times, deprecated_items_denormalizeds_0.buy_n_get_n, deprecated_items_denormalizeds_0.combo_promotions, deprecated_items_denormalizeds_0.configurable_products, deprecated_items_denormalizeds_0.disclaimer, deprecated_items_denormalizeds_0.display_size, deprecated_items_denormalizeds_0.in_store_location, deprecated_items_denormalizeds_0.item_tax_data, deprecated_items_denormalizeds_0.retailer_department, deprecated_items_denormalizeds_0.retailer_lookup_code, deprecated_items_denormalizeds_0.retailer_promotion_details, deprecated_items_denormalizeds_0.retailer_reference_code, deprecated_items_denormalizeds_0.retailer_sales_tax_code, deprecated_items_denormalizeds_0.retailer_specific_metadata, deprecated_items_denormalizeds_0.size, deprecated_items_denormalizeds_0.storage_temp, deprecated_items_denormalizeds_0.unata_fulfillment_types, deprecated_items_denormalizeds_0.zone_ids, deprecated_items_denormalizeds_0.elastic_item_id, deprecated_items_denormalizeds_0.temporary_override_elastic_item_id, deprecated_items_denormalizeds_0.vertex_tax_code, deprecated_items_denormalizeds_0.combo_promotions_jsonb, deprecated_retailer_products_mls.data, deprecated_retailer_products_cpgs.data, items_0.retailer_id, items_0.product_id, items_0.inventory_area_id, items_0.item_id, items_0.elastic_item_id, items_0.generated_at, items_0.alcoholic, items_0.available, items_0.balance_on_hand_qty, items_0.bottle_deposit_bank_id, items_0.bottle_deposit_cents, items_0.bottle_deposit_currency, items_0.code_set_version, items_0.cost, items_0.cost_price_per_unit, items_0.cost_unit, items_0.full_price, items_0.has_retailer_promotion, items_0.is_alcoholic, items_0.item_type, items_0.loyalty_allowed, items_0.loyalty_cost_price_cents, items_0.loyalty_cost_price_currency, items_0.loyalty_cost_price_end_at, items_0.loyalty_cost_price_per_unit, items_0.loyalty_cost_price_start_at, items_0.loyalty_price, items_0.loyalty_price_active, items_0.loyalty_price_end_at, items_0.loyalty_price_start_at, items_0.on_sale, items_0.par_weight, items_0.price, items_0.price_multiple, items_0.price_per_unit, items_0.product_type, items_0.recently_shopped_at, items_0.retailer_product_score_browse_ctr_pop_v0, items_0.region_id, items_0.retailer_specified_alcoholic, items_0.retailer_sales_tax_rate, items_0.sale_allowed, items_0.sale_price_cents, items_0.sale_cost_price_per_unit, items_0.sale_end_at, items_0.sale_start_at, items_0.searchable, items_0.taxable, items_0.unata_available, items_0.unit, items_0.unit_count, items_0.unit_price, items_0.unlisted, items_0.use_weight, items_0.visible, items_0.created_at, items_0.updated_at, items_0.promotion_discount_begins_at, items_0.promotion_discount_ends_at, items_0.bogo_buy, items_0.bogo_get, items_0.max_in_cart, items_0.temporary_override_elastic_item_id, items_0.full_text, items_0.max_cart, items_0.cancel_lead_time, items_0.is_catering, items_0.order_lead_time, items_0.usa_snap_eligible, items_0.full_text_v2, items_0.fulfill_pickup, items_0.fulfill_delivery, items_0.fulfill_instore, items_0.loyalty_members_only, items_0.promotion_group_id, items_0.is_hot, items_0.item_id, COALESCE(deprecated_items_denormalizeds_0.retailer_reference_code, retailer_products.retailer_reference_code), 'item'::text, count(*) OVER (?)
4. 0.063 2.422 ↓ 2.4 26 1

Nested Loop Left Join (cost=147.09..667.25 rows=11 width=5,620) (actual time=1.394..2.422 rows=26 loops=1)

  • Output: items_availabilities.availability_score, items_0.full_text_v2, products_conversions.score, retailer_term_v2s.score, generic_term_v2s.score, deprecated_retailer_products_cpgs.cpg_elastic_retailer_product_id, retailer_products.retailer_id, retailer_products.product_id, retailer_products.elastic_retailer_product_id, retailer_products.generated_at, retailer_products._aisle_sort_orders, retailer_products._dept_sort_orders, retailer_products.aisle_id, retailer_products.aisle_ids, retailer_products.aisle_name, retailer_products.aisle_sort_order, retailer_products.alcohol_attributes, retailer_products.alcohol_by_volume, retailer_products.alcoholic, retailer_products.allergens_keywords, retailer_products.bottle, retailer_products.brand_id, retailer_products.brand_name, retailer_products.calories, retailer_products.carbohydrate, retailer_products.canonical_product, retailer_products.cholesterol, retailer_products.cch_group_code, retailer_products.cch_item_code, retailer_products.classified_code, retailer_products.country_id, retailer_products.department_id, retailer_products.department_ids, retailer_products.department_name, retailer_products.dept_sort_order, retailer_products.description, retailer_products.details, retailer_products.directions, retailer_products.display_name, retailer_products.edible, retailer_products.fat, retailer_products.fat_calories, retailer_products.fat_free, retailer_products.fiber, retailer_products.form_factor, retailer_products.form_factor_descriptor, retailer_products.gluten_free, retailer_products.has_image, retailer_products.image_list, retailer_products.image_url, retailer_products.images, retailer_products.in_season, retailer_products.ingredients, retailer_products.is_organic, retailer_products.keywords, retailer_products.kosher, retailer_products.large_image_url, retailer_products.locale_code, retailer_products.low_fat, retailer_products.margin_mix, retailer_products.monounsaturated_fat, retailer_products.name, retailer_products.normalized_weight_g, retailer_products.nutrition_sources, retailer_products.organic, retailer_products.original_name, retailer_products.polyunsaturated_fat, retailer_products.potassium, retailer_products.primary_aisle_id, retailer_products.primary_department_id, retailer_products.primary_image_url, retailer_products.protein, retailer_products.product_category, retailer_products.product_category_taxonomy, retailer_products.product_codes, retailer_products.product_enrichment_keywords, retailer_products.prop65_chemicals, retailer_products.saturated_fat, retailer_products.seasonality_description, retailer_products.serving_size, retailer_products.servings_per_container, retailer_products.should_scan, retailer_products.similar_name, retailer_products.size_uom, retailer_products.size_value, retailer_products.sodium, retailer_products.store_brand, retailer_products.sugars, retailer_products.sugar_free, retailer_products.synthetic_codes, retailer_products.thumb_image_url, retailer_products.trans_fat, retailer_products.unit_attributes, retailer_products.unit_count, retailer_products.vegan, retailer_products.vegetarian, retailer_products.warehouse_id, retailer_products.warnings, retailer_products.updated_at, retailer_products.product_category_l1_name, retailer_products.product_category_l2_name, retailer_products.product_category_l3_name, retailer_products.product_category_l4_name, retailer_products.product_category_l5_name, retailer_products.product_category_l6_name, retailer_products.product_category_l1_id, retailer_products.product_category_l2_id, retailer_products.product_category_l3_id, retailer_products.product_category_l4_id, retailer_products.product_category_l5_id, retailer_products.product_category_l6_id, retailer_products.prop65_text, retailer_products.retailer_reference_code, retailer_products.product_category_taxonomy_l4, retailer_products.common_weight, retailer_products.min_picked_weight, retailer_products.suggested_incremental_weight, retailer_products.aisle_names, retailer_products.department_names, retailer_products.antibiotic_free, retailer_products.breed_angus, retailer_products.breed_berkshire, retailer_products.breed_duroc_pork, retailer_products.breed_gloucestershire, retailer_products.breed_kobe_wagyu, retailer_products.breed_kurobata_berkshire, retailer_products.breed_mangalitsapork, retailer_products.breed_piedmontese, retailer_products.breed_veal, retailer_products.breed_wagyu, retailer_products.cooking_method_braise, retailer_products.cooking_method_grill, retailer_products.cooking_method_pan_fry, retailer_products.cooking_method_roast, retailer_products.farmed, retailer_products.free_range, retailer_products.grade, retailer_products.grass_fed, retailer_products.halal, retailer_products.hormone_free, retailer_products.meat_state, retailer_products.non_gmo, retailer_products.origin, retailer_products.packaging, retailer_products.pasture_raised, retailer_products.prep_breaded_crusted, retailer_products.prep_cured, retailer_products.prep_dry_aged, retailer_products.prep_glazed, retailer_products.prep_grilled, retailer_products.prep_marinated, retailer_products.prep_roasted, retailer_products.prep_rubbed, retailer_products.prep_seasoned, retailer_products.prep_smoked, retailer_products.prep_uncured, retailer_products.primal_cut, retailer_products.trim_bone_in, retailer_products.trim_boneless, retailer_products.trim_deveined, retailer_products.trim_dressed, retailer_products.trim_frenched, retailer_products.trim_head_on, retailer_products.trim_peeled, retailer_products.trim_shell_on, retailer_products.trim_shredded, retailer_products.trim_skin_on, retailer_products.trim_skinless, retailer_products.trim_sliced, retailer_products.trim_stuffed, retailer_products.trim_tail_off, retailer_products.trim_thick_cut, retailer_products.trim_thin_cut, retailer_products.wild_caught, retailer_products.product_tags, retailer_products.alcohol_type, retailer_products.alcohol_varietal, retailer_products.full_text_v1, retailer_products.dairy_free, retailer_products.temporary_override_elastic_retailer_product_id, retailer_products.milk_type_cow, retailer_products.milk_type_goat, retailer_products.milk_type_sheep, retailer_products.unit_value, retailer_products.is_catering, retailer_products.usa_snap_eligible, retailer_products.alcohol_rating, retailer_products.full_text_v2, retailer_products.secondary_aisle_ids, retailer_products.secondary_department_ids, retailer_products.variant_group_id, retailer_products.variant_attributes, retailer_products.product_codes_array, retailer_products.color, retailer_products.color_hex_code, retailer_products.flavor, deprecated_items_denormalizeds_0.elastic_item_id, deprecated_items_denormalizeds_0.retailer_id, deprecated_items_denormalizeds_0.product_id, deprecated_items_denormalizeds_0.inventory_area_id, deprecated_items_denormalizeds_0.item_id, deprecated_items_denormalizeds_0.generated_at, deprecated_items_denormalizeds_0.blackout_times, deprecated_items_denormalizeds_0.buy_n_get_n, deprecated_items_denormalizeds_0.combo_promotions, deprecated_items_denormalizeds_0.configurable_products, deprecated_items_denormalizeds_0.disclaimer, deprecated_items_denormalizeds_0.display_size, deprecated_items_denormalizeds_0.in_store_location, deprecated_items_denormalizeds_0.item_tax_data, deprecated_items_denormalizeds_0.retailer_department, deprecated_items_denormalizeds_0.retailer_lookup_code, deprecated_items_denormalizeds_0.retailer_promotion_details, deprecated_items_denormalizeds_0.retailer_reference_code, deprecated_items_denormalizeds_0.retailer_sales_tax_code, deprecated_items_denormalizeds_0.retailer_specific_metadata, deprecated_items_denormalizeds_0.size, deprecated_items_denormalizeds_0.storage_temp, deprecated_items_denormalizeds_0.unata_fulfillment_types, deprecated_items_denormalizeds_0.zone_ids, deprecated_items_denormalizeds_0.temporary_override_elastic_item_id, deprecated_items_denormalizeds_0.vertex_tax_code, deprecated_items_denormalizeds_0.combo_promotions_jsonb, deprecated_retailer_products_mls.data, deprecated_retailer_products_cpgs.data, items_0.retailer_id, items_0.product_id, items_0.inventory_area_id, items_0.item_id, items_0.elastic_item_id, items_0.generated_at, items_0.alcoholic, items_0.available, items_0.balance_on_hand_qty, items_0.bottle_deposit_bank_id, items_0.bottle_deposit_cents, items_0.bottle_deposit_currency, items_0.code_set_version, items_0.cost, items_0.cost_price_per_unit, items_0.cost_unit, items_0.full_price, items_0.has_retailer_promotion, items_0.is_alcoholic, items_0.item_type, items_0.loyalty_allowed, items_0.loyalty_cost_price_cents, items_0.loyalty_cost_price_currency, items_0.loyalty_cost_price_end_at, items_0.loyalty_cost_price_per_unit, items_0.loyalty_cost_price_start_at, items_0.loyalty_price, items_0.loyalty_price_active, items_0.loyalty_price_end_at, items_0.loyalty_price_start_at, items_0.on_sale, items_0.par_weight, items_0.price, items_0.price_multiple, items_0.price_per_unit, items_0.product_type, items_0.recently_shopped_at, items_0.retailer_product_score_browse_ctr_pop_v0, items_0.region_id, items_0.retailer_specified_alcoholic, items_0.retailer_sales_tax_rate, items_0.sale_allowed, items_0.sale_price_cents, items_0.sale_cost_price_per_unit, items_0.sale_end_at, items_0.sale_start_at, items_0.searchable, items_0.taxable, items_0.unata_available, items_0.unit, items_0.unit_count, items_0.unit_price, items_0.unlisted, items_0.use_weight, items_0.visible, items_0.created_at, items_0.updated_at, items_0.promotion_discount_begins_at, items_0.promotion_discount_ends_at, items_0.bogo_buy, items_0.bogo_get, items_0.max_in_cart, items_0.temporary_override_elastic_item_id, items_0.full_text, items_0.max_cart, items_0.cancel_lead_time, items_0.is_catering, items_0.order_lead_time, items_0.usa_snap_eligible, items_0.fulfill_pickup, items_0.fulfill_delivery, items_0.fulfill_instore, items_0.loyalty_members_only, items_0.promotion_group_id, items_0.is_hot
  • Inner Unique: true
  • Join Filter: (generic_term_v2s.product_id = items_0.product_id)
5. 0.052 2.333 ↓ 2.4 26 1

Nested Loop Left Join (cost=146.94..658.91 rows=11 width=5,612) (actual time=1.355..2.333 rows=26 loops=1)

  • Output: items_0.full_text_v2, items_0.retailer_id, items_0.product_id, items_0.inventory_area_id, items_0.item_id, items_0.elastic_item_id, items_0.generated_at, items_0.alcoholic, items_0.available, items_0.balance_on_hand_qty, items_0.bottle_deposit_bank_id, items_0.bottle_deposit_cents, items_0.bottle_deposit_currency, items_0.code_set_version, items_0.cost, items_0.cost_price_per_unit, items_0.cost_unit, items_0.full_price, items_0.has_retailer_promotion, items_0.is_alcoholic, items_0.item_type, items_0.loyalty_allowed, items_0.loyalty_cost_price_cents, items_0.loyalty_cost_price_currency, items_0.loyalty_cost_price_end_at, items_0.loyalty_cost_price_per_unit, items_0.loyalty_cost_price_start_at, items_0.loyalty_price, items_0.loyalty_price_active, items_0.loyalty_price_end_at, items_0.loyalty_price_start_at, items_0.on_sale, items_0.par_weight, items_0.price, items_0.price_multiple, items_0.price_per_unit, items_0.product_type, items_0.recently_shopped_at, items_0.retailer_product_score_browse_ctr_pop_v0, items_0.region_id, items_0.retailer_specified_alcoholic, items_0.retailer_sales_tax_rate, items_0.sale_allowed, items_0.sale_price_cents, items_0.sale_cost_price_per_unit, items_0.sale_end_at, items_0.sale_start_at, items_0.searchable, items_0.taxable, items_0.unata_available, items_0.unit, items_0.unit_count, items_0.unit_price, items_0.unlisted, items_0.use_weight, items_0.visible, items_0.created_at, items_0.updated_at, items_0.promotion_discount_begins_at, items_0.promotion_discount_ends_at, items_0.bogo_buy, items_0.bogo_get, items_0.max_in_cart, items_0.temporary_override_elastic_item_id, items_0.full_text, items_0.max_cart, items_0.cancel_lead_time, items_0.is_catering, items_0.order_lead_time, items_0.usa_snap_eligible, items_0.fulfill_pickup, items_0.fulfill_delivery, items_0.fulfill_instore, items_0.loyalty_members_only, items_0.promotion_group_id, items_0.is_hot, retailer_products.retailer_id, retailer_products.product_id, retailer_products.elastic_retailer_product_id, retailer_products.generated_at, retailer_products._aisle_sort_orders, retailer_products._dept_sort_orders, retailer_products.aisle_id, retailer_products.aisle_ids, retailer_products.aisle_name, retailer_products.aisle_sort_order, retailer_products.alcohol_attributes, retailer_products.alcohol_by_volume, retailer_products.alcoholic, retailer_products.allergens_keywords, retailer_products.bottle, retailer_products.brand_id, retailer_products.brand_name, retailer_products.calories, retailer_products.carbohydrate, retailer_products.canonical_product, retailer_products.cholesterol, retailer_products.cch_group_code, retailer_products.cch_item_code, retailer_products.classified_code, retailer_products.country_id, retailer_products.department_id, retailer_products.department_ids, retailer_products.department_name, retailer_products.dept_sort_order, retailer_products.description, retailer_products.details, retailer_products.directions, retailer_products.display_name, retailer_products.edible, retailer_products.fat, retailer_products.fat_calories, retailer_products.fat_free, retailer_products.fiber, retailer_products.form_factor, retailer_products.form_factor_descriptor, retailer_products.gluten_free, retailer_products.has_image, retailer_products.image_list, retailer_products.image_url, retailer_products.images, retailer_products.in_season, retailer_products.ingredients, retailer_products.is_organic, retailer_products.keywords, retailer_products.kosher, retailer_products.large_image_url, retailer_products.locale_code, retailer_products.low_fat, retailer_products.margin_mix, retailer_products.monounsaturated_fat, retailer_products.name, retailer_products.normalized_weight_g, retailer_products.nutrition_sources, retailer_products.organic, retailer_products.original_name, retailer_products.polyunsaturated_fat, retailer_products.potassium, retailer_products.primary_aisle_id, retailer_products.primary_department_id, retailer_products.primary_image_url, retailer_products.protein, retailer_products.product_category, retailer_products.product_category_taxonomy, retailer_products.product_codes, retailer_products.product_enrichment_keywords, retailer_products.prop65_chemicals, retailer_products.saturated_fat, retailer_products.seasonality_description, retailer_products.serving_size, retailer_products.servings_per_container, retailer_products.should_scan, retailer_products.similar_name, retailer_products.size_uom, retailer_products.size_value, retailer_products.sodium, retailer_products.store_brand, retailer_products.sugars, retailer_products.sugar_free, retailer_products.synthetic_codes, retailer_products.thumb_image_url, retailer_products.trans_fat, retailer_products.unit_attributes, retailer_products.unit_count, retailer_products.vegan, retailer_products.vegetarian, retailer_products.warehouse_id, retailer_products.warnings, retailer_products.updated_at, retailer_products.product_category_l1_name, retailer_products.product_category_l2_name, retailer_products.product_category_l3_name, retailer_products.product_category_l4_name, retailer_products.product_category_l5_name, retailer_products.product_category_l6_name, retailer_products.product_category_l1_id, retailer_products.product_category_l2_id, retailer_products.product_category_l3_id, retailer_products.product_category_l4_id, retailer_products.product_category_l5_id, retailer_products.product_category_l6_id, retailer_products.prop65_text, retailer_products.retailer_reference_code, retailer_products.product_category_taxonomy_l4, retailer_products.common_weight, retailer_products.min_picked_weight, retailer_products.suggested_incremental_weight, retailer_products.aisle_names, retailer_products.department_names, retailer_products.antibiotic_free, retailer_products.breed_angus, retailer_products.breed_berkshire, retailer_products.breed_duroc_pork, retailer_products.breed_gloucestershire, retailer_products.breed_kobe_wagyu, retailer_products.breed_kurobata_berkshire, retailer_products.breed_mangalitsapork, retailer_products.breed_piedmontese, retailer_products.breed_veal, retailer_products.breed_wagyu, retailer_products.cooking_method_braise, retailer_products.cooking_method_grill, retailer_products.cooking_method_pan_fry, retailer_products.cooking_method_roast, retailer_products.farmed, retailer_products.free_range, retailer_products.grade, retailer_products.grass_fed, retailer_products.halal, retailer_products.hormone_free, retailer_products.meat_state, retailer_products.non_gmo, retailer_products.origin, retailer_products.packaging, retailer_products.pasture_raised, retailer_products.prep_breaded_crusted, retailer_products.prep_cured, retailer_products.prep_dry_aged, retailer_products.prep_glazed, retailer_products.prep_grilled, retailer_products.prep_marinated, retailer_products.prep_roasted, retailer_products.prep_rubbed, retailer_products.prep_seasoned, retailer_products.prep_smoked, retailer_products.prep_uncured, retailer_products.primal_cut, retailer_products.trim_bone_in, retailer_products.trim_boneless, retailer_products.trim_deveined, retailer_products.trim_dressed, retailer_products.trim_frenched, retailer_products.trim_head_on, retailer_products.trim_peeled, retailer_products.trim_shell_on, retailer_products.trim_shredded, retailer_products.trim_skin_on, retailer_products.trim_skinless, retailer_products.trim_sliced, retailer_products.trim_stuffed, retailer_products.trim_tail_off, retailer_products.trim_thick_cut, retailer_products.trim_thin_cut, retailer_products.wild_caught, retailer_products.product_tags, retailer_products.alcohol_type, retailer_products.alcohol_varietal, retailer_products.full_text_v1, retailer_products.dairy_free, retailer_products.temporary_override_elastic_retailer_product_id, retailer_products.milk_type_cow, retailer_products.milk_type_goat, retailer_products.milk_type_sheep, retailer_products.unit_value, retailer_products.is_catering, retailer_products.usa_snap_eligible, retailer_products.alcohol_rating, retailer_products.full_text_v2, retailer_products.secondary_aisle_ids, retailer_products.secondary_department_ids, retailer_products.variant_group_id, retailer_products.variant_attributes, retailer_products.product_codes_array, retailer_products.color, retailer_products.color_hex_code, retailer_products.flavor, items_availabilities.availability_score, products_conversions.score, deprecated_retailer_products_mls.data, deprecated_retailer_products_cpgs.cpg_elastic_retailer_product_id, deprecated_retailer_products_cpgs.data, deprecated_items_denormalizeds_0.elastic_item_id, deprecated_items_denormalizeds_0.retailer_id, deprecated_items_denormalizeds_0.product_id, deprecated_items_denormalizeds_0.inventory_area_id, deprecated_items_denormalizeds_0.item_id, deprecated_items_denormalizeds_0.generated_at, deprecated_items_denormalizeds_0.blackout_times, deprecated_items_denormalizeds_0.buy_n_get_n, deprecated_items_denormalizeds_0.combo_promotions, deprecated_items_denormalizeds_0.configurable_products, deprecated_items_denormalizeds_0.disclaimer, deprecated_items_denormalizeds_0.display_size, deprecated_items_denormalizeds_0.in_store_location, deprecated_items_denormalizeds_0.item_tax_data, deprecated_items_denormalizeds_0.retailer_department, deprecated_items_denormalizeds_0.retailer_lookup_code, deprecated_items_denormalizeds_0.retailer_promotion_details, deprecated_items_denormalizeds_0.retailer_reference_code, deprecated_items_denormalizeds_0.retailer_sales_tax_code, deprecated_items_denormalizeds_0.retailer_specific_metadata, deprecated_items_denormalizeds_0.size, deprecated_items_denormalizeds_0.storage_temp, deprecated_items_denormalizeds_0.unata_fulfillment_types, deprecated_items_denormalizeds_0.zone_ids, deprecated_items_denormalizeds_0.temporary_override_elastic_item_id, deprecated_items_denormalizeds_0.vertex_tax_code, deprecated_items_denormalizeds_0.combo_promotions_jsonb, retailer_term_v2s.score
  • Inner Unique: true
  • Join Filter: ((retailer_term_v2s.product_id = items_0.product_id) AND (retailer_term_v2s.retailer_id = items_0.retailer_id))
6. 0.098 2.255 ↓ 2.4 26 1

Nested Loop Left Join (cost=142.75..645.27 rows=11 width=5,604) (actual time=1.326..2.255 rows=26 loops=1)

  • Output: items_0.full_text_v2, items_0.retailer_id, items_0.product_id, items_0.inventory_area_id, items_0.item_id, items_0.elastic_item_id, items_0.generated_at, items_0.alcoholic, items_0.available, items_0.balance_on_hand_qty, items_0.bottle_deposit_bank_id, items_0.bottle_deposit_cents, items_0.bottle_deposit_currency, items_0.code_set_version, items_0.cost, items_0.cost_price_per_unit, items_0.cost_unit, items_0.full_price, items_0.has_retailer_promotion, items_0.is_alcoholic, items_0.item_type, items_0.loyalty_allowed, items_0.loyalty_cost_price_cents, items_0.loyalty_cost_price_currency, items_0.loyalty_cost_price_end_at, items_0.loyalty_cost_price_per_unit, items_0.loyalty_cost_price_start_at, items_0.loyalty_price, items_0.loyalty_price_active, items_0.loyalty_price_end_at, items_0.loyalty_price_start_at, items_0.on_sale, items_0.par_weight, items_0.price, items_0.price_multiple, items_0.price_per_unit, items_0.product_type, items_0.recently_shopped_at, items_0.retailer_product_score_browse_ctr_pop_v0, items_0.region_id, items_0.retailer_specified_alcoholic, items_0.retailer_sales_tax_rate, items_0.sale_allowed, items_0.sale_price_cents, items_0.sale_cost_price_per_unit, items_0.sale_end_at, items_0.sale_start_at, items_0.searchable, items_0.taxable, items_0.unata_available, items_0.unit, items_0.unit_count, items_0.unit_price, items_0.unlisted, items_0.use_weight, items_0.visible, items_0.created_at, items_0.updated_at, items_0.promotion_discount_begins_at, items_0.promotion_discount_ends_at, items_0.bogo_buy, items_0.bogo_get, items_0.max_in_cart, items_0.temporary_override_elastic_item_id, items_0.full_text, items_0.max_cart, items_0.cancel_lead_time, items_0.is_catering, items_0.order_lead_time, items_0.usa_snap_eligible, items_0.fulfill_pickup, items_0.fulfill_delivery, items_0.fulfill_instore, items_0.loyalty_members_only, items_0.promotion_group_id, items_0.is_hot, retailer_products.retailer_id, retailer_products.product_id, retailer_products.elastic_retailer_product_id, retailer_products.generated_at, retailer_products._aisle_sort_orders, retailer_products._dept_sort_orders, retailer_products.aisle_id, retailer_products.aisle_ids, retailer_products.aisle_name, retailer_products.aisle_sort_order, retailer_products.alcohol_attributes, retailer_products.alcohol_by_volume, retailer_products.alcoholic, retailer_products.allergens_keywords, retailer_products.bottle, retailer_products.brand_id, retailer_products.brand_name, retailer_products.calories, retailer_products.carbohydrate, retailer_products.canonical_product, retailer_products.cholesterol, retailer_products.cch_group_code, retailer_products.cch_item_code, retailer_products.classified_code, retailer_products.country_id, retailer_products.department_id, retailer_products.department_ids, retailer_products.department_name, retailer_products.dept_sort_order, retailer_products.description, retailer_products.details, retailer_products.directions, retailer_products.display_name, retailer_products.edible, retailer_products.fat, retailer_products.fat_calories, retailer_products.fat_free, retailer_products.fiber, retailer_products.form_factor, retailer_products.form_factor_descriptor, retailer_products.gluten_free, retailer_products.has_image, retailer_products.image_list, retailer_products.image_url, retailer_products.images, retailer_products.in_season, retailer_products.ingredients, retailer_products.is_organic, retailer_products.keywords, retailer_products.kosher, retailer_products.large_image_url, retailer_products.locale_code, retailer_products.low_fat, retailer_products.margin_mix, retailer_products.monounsaturated_fat, retailer_products.name, retailer_products.normalized_weight_g, retailer_products.nutrition_sources, retailer_products.organic, retailer_products.original_name, retailer_products.polyunsaturated_fat, retailer_products.potassium, retailer_products.primary_aisle_id, retailer_products.primary_department_id, retailer_products.primary_image_url, retailer_products.protein, retailer_products.product_category, retailer_products.product_category_taxonomy, retailer_products.product_codes, retailer_products.product_enrichment_keywords, retailer_products.prop65_chemicals, retailer_products.saturated_fat, retailer_products.seasonality_description, retailer_products.serving_size, retailer_products.servings_per_container, retailer_products.should_scan, retailer_products.similar_name, retailer_products.size_uom, retailer_products.size_value, retailer_products.sodium, retailer_products.store_brand, retailer_products.sugars, retailer_products.sugar_free, retailer_products.synthetic_codes, retailer_products.thumb_image_url, retailer_products.trans_fat, retailer_products.unit_attributes, retailer_products.unit_count, retailer_products.vegan, retailer_products.vegetarian, retailer_products.warehouse_id, retailer_products.warnings, retailer_products.updated_at, retailer_products.product_category_l1_name, retailer_products.product_category_l2_name, retailer_products.product_category_l3_name, retailer_products.product_category_l4_name, retailer_products.product_category_l5_name, retailer_products.product_category_l6_name, retailer_products.product_category_l1_id, retailer_products.product_category_l2_id, retailer_products.product_category_l3_id, retailer_products.product_category_l4_id, retailer_products.product_category_l5_id, retailer_products.product_category_l6_id, retailer_products.prop65_text, retailer_products.retailer_reference_code, retailer_products.product_category_taxonomy_l4, retailer_products.common_weight, retailer_products.min_picked_weight, retailer_products.suggested_incremental_weight, retailer_products.aisle_names, retailer_products.department_names, retailer_products.antibiotic_free, retailer_products.breed_angus, retailer_products.breed_berkshire, retailer_products.breed_duroc_pork, retailer_products.breed_gloucestershire, retailer_products.breed_kobe_wagyu, retailer_products.breed_kurobata_berkshire, retailer_products.breed_mangalitsapork, retailer_products.breed_piedmontese, retailer_products.breed_veal, retailer_products.breed_wagyu, retailer_products.cooking_method_braise, retailer_products.cooking_method_grill, retailer_products.cooking_method_pan_fry, retailer_products.cooking_method_roast, retailer_products.farmed, retailer_products.free_range, retailer_products.grade, retailer_products.grass_fed, retailer_products.halal, retailer_products.hormone_free, retailer_products.meat_state, retailer_products.non_gmo, retailer_products.origin, retailer_products.packaging, retailer_products.pasture_raised, retailer_products.prep_breaded_crusted, retailer_products.prep_cured, retailer_products.prep_dry_aged, retailer_products.prep_glazed, retailer_products.prep_grilled, retailer_products.prep_marinated, retailer_products.prep_roasted, retailer_products.prep_rubbed, retailer_products.prep_seasoned, retailer_products.prep_smoked, retailer_products.prep_uncured, retailer_products.primal_cut, retailer_products.trim_bone_in, retailer_products.trim_boneless, retailer_products.trim_deveined, retailer_products.trim_dressed, retailer_products.trim_frenched, retailer_products.trim_head_on, retailer_products.trim_peeled, retailer_products.trim_shell_on, retailer_products.trim_shredded, retailer_products.trim_skin_on, retailer_products.trim_skinless, retailer_products.trim_sliced, retailer_products.trim_stuffed, retailer_products.trim_tail_off, retailer_products.trim_thick_cut, retailer_products.trim_thin_cut, retailer_products.wild_caught, retailer_products.product_tags, retailer_products.alcohol_type, retailer_products.alcohol_varietal, retailer_products.full_text_v1, retailer_products.dairy_free, retailer_products.temporary_override_elastic_retailer_product_id, retailer_products.milk_type_cow, retailer_products.milk_type_goat, retailer_products.milk_type_sheep, retailer_products.unit_value, retailer_products.is_catering, retailer_products.usa_snap_eligible, retailer_products.alcohol_rating, retailer_products.full_text_v2, retailer_products.secondary_aisle_ids, retailer_products.secondary_department_ids, retailer_products.variant_group_id, retailer_products.variant_attributes, retailer_products.product_codes_array, retailer_products.color, retailer_products.color_hex_code, retailer_products.flavor, items_availabilities.availability_score, products_conversions.score, deprecated_retailer_products_mls.data, deprecated_retailer_products_cpgs.cpg_elastic_retailer_product_id, deprecated_retailer_products_cpgs.data, deprecated_items_denormalizeds_0.elastic_item_id, deprecated_items_denormalizeds_0.retailer_id, deprecated_items_denormalizeds_0.product_id, deprecated_items_denormalizeds_0.inventory_area_id, deprecated_items_denormalizeds_0.item_id, deprecated_items_denormalizeds_0.generated_at, deprecated_items_denormalizeds_0.blackout_times, deprecated_items_denormalizeds_0.buy_n_get_n, deprecated_items_denormalizeds_0.combo_promotions, deprecated_items_denormalizeds_0.configurable_products, deprecated_items_denormalizeds_0.disclaimer, deprecated_items_denormalizeds_0.display_size, deprecated_items_denormalizeds_0.in_store_location, deprecated_items_denormalizeds_0.item_tax_data, deprecated_items_denormalizeds_0.retailer_department, deprecated_items_denormalizeds_0.retailer_lookup_code, deprecated_items_denormalizeds_0.retailer_promotion_details, deprecated_items_denormalizeds_0.retailer_reference_code, deprecated_items_denormalizeds_0.retailer_sales_tax_code, deprecated_items_denormalizeds_0.retailer_specific_metadata, deprecated_items_denormalizeds_0.size, deprecated_items_denormalizeds_0.storage_temp, deprecated_items_denormalizeds_0.unata_fulfillment_types, deprecated_items_denormalizeds_0.zone_ids, deprecated_items_denormalizeds_0.temporary_override_elastic_item_id, deprecated_items_denormalizeds_0.vertex_tax_code, deprecated_items_denormalizeds_0.combo_promotions_jsonb
  • Inner Unique: true
7. 0.068 2.053 ↓ 2.4 26 1

Nested Loop Left Join (cost=142.46..553.83 rows=11 width=4,703) (actual time=1.281..2.053 rows=26 loops=1)

  • Output: items_0.full_text_v2, items_0.retailer_id, items_0.product_id, items_0.inventory_area_id, items_0.item_id, items_0.elastic_item_id, items_0.generated_at, items_0.alcoholic, items_0.available, items_0.balance_on_hand_qty, items_0.bottle_deposit_bank_id, items_0.bottle_deposit_cents, items_0.bottle_deposit_currency, items_0.code_set_version, items_0.cost, items_0.cost_price_per_unit, items_0.cost_unit, items_0.full_price, items_0.has_retailer_promotion, items_0.is_alcoholic, items_0.item_type, items_0.loyalty_allowed, items_0.loyalty_cost_price_cents, items_0.loyalty_cost_price_currency, items_0.loyalty_cost_price_end_at, items_0.loyalty_cost_price_per_unit, items_0.loyalty_cost_price_start_at, items_0.loyalty_price, items_0.loyalty_price_active, items_0.loyalty_price_end_at, items_0.loyalty_price_start_at, items_0.on_sale, items_0.par_weight, items_0.price, items_0.price_multiple, items_0.price_per_unit, items_0.product_type, items_0.recently_shopped_at, items_0.retailer_product_score_browse_ctr_pop_v0, items_0.region_id, items_0.retailer_specified_alcoholic, items_0.retailer_sales_tax_rate, items_0.sale_allowed, items_0.sale_price_cents, items_0.sale_cost_price_per_unit, items_0.sale_end_at, items_0.sale_start_at, items_0.searchable, items_0.taxable, items_0.unata_available, items_0.unit, items_0.unit_count, items_0.unit_price, items_0.unlisted, items_0.use_weight, items_0.visible, items_0.created_at, items_0.updated_at, items_0.promotion_discount_begins_at, items_0.promotion_discount_ends_at, items_0.bogo_buy, items_0.bogo_get, items_0.max_in_cart, items_0.temporary_override_elastic_item_id, items_0.full_text, items_0.max_cart, items_0.cancel_lead_time, items_0.is_catering, items_0.order_lead_time, items_0.usa_snap_eligible, items_0.fulfill_pickup, items_0.fulfill_delivery, items_0.fulfill_instore, items_0.loyalty_members_only, items_0.promotion_group_id, items_0.is_hot, retailer_products.retailer_id, retailer_products.product_id, retailer_products.elastic_retailer_product_id, retailer_products.generated_at, retailer_products._aisle_sort_orders, retailer_products._dept_sort_orders, retailer_products.aisle_id, retailer_products.aisle_ids, retailer_products.aisle_name, retailer_products.aisle_sort_order, retailer_products.alcohol_attributes, retailer_products.alcohol_by_volume, retailer_products.alcoholic, retailer_products.allergens_keywords, retailer_products.bottle, retailer_products.brand_id, retailer_products.brand_name, retailer_products.calories, retailer_products.carbohydrate, retailer_products.canonical_product, retailer_products.cholesterol, retailer_products.cch_group_code, retailer_products.cch_item_code, retailer_products.classified_code, retailer_products.country_id, retailer_products.department_id, retailer_products.department_ids, retailer_products.department_name, retailer_products.dept_sort_order, retailer_products.description, retailer_products.details, retailer_products.directions, retailer_products.display_name, retailer_products.edible, retailer_products.fat, retailer_products.fat_calories, retailer_products.fat_free, retailer_products.fiber, retailer_products.form_factor, retailer_products.form_factor_descriptor, retailer_products.gluten_free, retailer_products.has_image, retailer_products.image_list, retailer_products.image_url, retailer_products.images, retailer_products.in_season, retailer_products.ingredients, retailer_products.is_organic, retailer_products.keywords, retailer_products.kosher, retailer_products.large_image_url, retailer_products.locale_code, retailer_products.low_fat, retailer_products.margin_mix, retailer_products.monounsaturated_fat, retailer_products.name, retailer_products.normalized_weight_g, retailer_products.nutrition_sources, retailer_products.organic, retailer_products.original_name, retailer_products.polyunsaturated_fat, retailer_products.potassium, retailer_products.primary_aisle_id, retailer_products.primary_department_id, retailer_products.primary_image_url, retailer_products.protein, retailer_products.product_category, retailer_products.product_category_taxonomy, retailer_products.product_codes, retailer_products.product_enrichment_keywords, retailer_products.prop65_chemicals, retailer_products.saturated_fat, retailer_products.seasonality_description, retailer_products.serving_size, retailer_products.servings_per_container, retailer_products.should_scan, retailer_products.similar_name, retailer_products.size_uom, retailer_products.size_value, retailer_products.sodium, retailer_products.store_brand, retailer_products.sugars, retailer_products.sugar_free, retailer_products.synthetic_codes, retailer_products.thumb_image_url, retailer_products.trans_fat, retailer_products.unit_attributes, retailer_products.unit_count, retailer_products.vegan, retailer_products.vegetarian, retailer_products.warehouse_id, retailer_products.warnings, retailer_products.updated_at, retailer_products.product_category_l1_name, retailer_products.product_category_l2_name, retailer_products.product_category_l3_name, retailer_products.product_category_l4_name, retailer_products.product_category_l5_name, retailer_products.product_category_l6_name, retailer_products.product_category_l1_id, retailer_products.product_category_l2_id, retailer_products.product_category_l3_id, retailer_products.product_category_l4_id, retailer_products.product_category_l5_id, retailer_products.product_category_l6_id, retailer_products.prop65_text, retailer_products.retailer_reference_code, retailer_products.product_category_taxonomy_l4, retailer_products.common_weight, retailer_products.min_picked_weight, retailer_products.suggested_incremental_weight, retailer_products.aisle_names, retailer_products.department_names, retailer_products.antibiotic_free, retailer_products.breed_angus, retailer_products.breed_berkshire, retailer_products.breed_duroc_pork, retailer_products.breed_gloucestershire, retailer_products.breed_kobe_wagyu, retailer_products.breed_kurobata_berkshire, retailer_products.breed_mangalitsapork, retailer_products.breed_piedmontese, retailer_products.breed_veal, retailer_products.breed_wagyu, retailer_products.cooking_method_braise, retailer_products.cooking_method_grill, retailer_products.cooking_method_pan_fry, retailer_products.cooking_method_roast, retailer_products.farmed, retailer_products.free_range, retailer_products.grade, retailer_products.grass_fed, retailer_products.halal, retailer_products.hormone_free, retailer_products.meat_state, retailer_products.non_gmo, retailer_products.origin, retailer_products.packaging, retailer_products.pasture_raised, retailer_products.prep_breaded_crusted, retailer_products.prep_cured, retailer_products.prep_dry_aged, retailer_products.prep_glazed, retailer_products.prep_grilled, retailer_products.prep_marinated, retailer_products.prep_roasted, retailer_products.prep_rubbed, retailer_products.prep_seasoned, retailer_products.prep_smoked, retailer_products.prep_uncured, retailer_products.primal_cut, retailer_products.trim_bone_in, retailer_products.trim_boneless, retailer_products.trim_deveined, retailer_products.trim_dressed, retailer_products.trim_frenched, retailer_products.trim_head_on, retailer_products.trim_peeled, retailer_products.trim_shell_on, retailer_products.trim_shredded, retailer_products.trim_skin_on, retailer_products.trim_skinless, retailer_products.trim_sliced, retailer_products.trim_stuffed, retailer_products.trim_tail_off, retailer_products.trim_thick_cut, retailer_products.trim_thin_cut, retailer_products.wild_caught, retailer_products.product_tags, retailer_products.alcohol_type, retailer_products.alcohol_varietal, retailer_products.full_text_v1, retailer_products.dairy_free, retailer_products.temporary_override_elastic_retailer_product_id, retailer_products.milk_type_cow, retailer_products.milk_type_goat, retailer_products.milk_type_sheep, retailer_products.unit_value, retailer_products.is_catering, retailer_products.usa_snap_eligible, retailer_products.alcohol_rating, retailer_products.full_text_v2, retailer_products.secondary_aisle_ids, retailer_products.secondary_department_ids, retailer_products.variant_group_id, retailer_products.variant_attributes, retailer_products.product_codes_array, retailer_products.color, retailer_products.color_hex_code, retailer_products.flavor, items_availabilities.availability_score, products_conversions.score, deprecated_retailer_products_mls.data, deprecated_retailer_products_cpgs.cpg_elastic_retailer_product_id, deprecated_retailer_products_cpgs.data
  • Inner Unique: true
8. 0.071 1.881 ↓ 2.4 26 1

Nested Loop Left Join (cost=142.17..466.48 rows=11 width=4,675) (actual time=1.251..1.881 rows=26 loops=1)

  • Output: items_0.full_text_v2, items_0.retailer_id, items_0.product_id, items_0.inventory_area_id, items_0.item_id, items_0.elastic_item_id, items_0.generated_at, items_0.alcoholic, items_0.available, items_0.balance_on_hand_qty, items_0.bottle_deposit_bank_id, items_0.bottle_deposit_cents, items_0.bottle_deposit_currency, items_0.code_set_version, items_0.cost, items_0.cost_price_per_unit, items_0.cost_unit, items_0.full_price, items_0.has_retailer_promotion, items_0.is_alcoholic, items_0.item_type, items_0.loyalty_allowed, items_0.loyalty_cost_price_cents, items_0.loyalty_cost_price_currency, items_0.loyalty_cost_price_end_at, items_0.loyalty_cost_price_per_unit, items_0.loyalty_cost_price_start_at, items_0.loyalty_price, items_0.loyalty_price_active, items_0.loyalty_price_end_at, items_0.loyalty_price_start_at, items_0.on_sale, items_0.par_weight, items_0.price, items_0.price_multiple, items_0.price_per_unit, items_0.product_type, items_0.recently_shopped_at, items_0.retailer_product_score_browse_ctr_pop_v0, items_0.region_id, items_0.retailer_specified_alcoholic, items_0.retailer_sales_tax_rate, items_0.sale_allowed, items_0.sale_price_cents, items_0.sale_cost_price_per_unit, items_0.sale_end_at, items_0.sale_start_at, items_0.searchable, items_0.taxable, items_0.unata_available, items_0.unit, items_0.unit_count, items_0.unit_price, items_0.unlisted, items_0.use_weight, items_0.visible, items_0.created_at, items_0.updated_at, items_0.promotion_discount_begins_at, items_0.promotion_discount_ends_at, items_0.bogo_buy, items_0.bogo_get, items_0.max_in_cart, items_0.temporary_override_elastic_item_id, items_0.full_text, items_0.max_cart, items_0.cancel_lead_time, items_0.is_catering, items_0.order_lead_time, items_0.usa_snap_eligible, items_0.fulfill_pickup, items_0.fulfill_delivery, items_0.fulfill_instore, items_0.loyalty_members_only, items_0.promotion_group_id, items_0.is_hot, retailer_products.retailer_id, retailer_products.product_id, retailer_products.elastic_retailer_product_id, retailer_products.generated_at, retailer_products._aisle_sort_orders, retailer_products._dept_sort_orders, retailer_products.aisle_id, retailer_products.aisle_ids, retailer_products.aisle_name, retailer_products.aisle_sort_order, retailer_products.alcohol_attributes, retailer_products.alcohol_by_volume, retailer_products.alcoholic, retailer_products.allergens_keywords, retailer_products.bottle, retailer_products.brand_id, retailer_products.brand_name, retailer_products.calories, retailer_products.carbohydrate, retailer_products.canonical_product, retailer_products.cholesterol, retailer_products.cch_group_code, retailer_products.cch_item_code, retailer_products.classified_code, retailer_products.country_id, retailer_products.department_id, retailer_products.department_ids, retailer_products.department_name, retailer_products.dept_sort_order, retailer_products.description, retailer_products.details, retailer_products.directions, retailer_products.display_name, retailer_products.edible, retailer_products.fat, retailer_products.fat_calories, retailer_products.fat_free, retailer_products.fiber, retailer_products.form_factor, retailer_products.form_factor_descriptor, retailer_products.gluten_free, retailer_products.has_image, retailer_products.image_list, retailer_products.image_url, retailer_products.images, retailer_products.in_season, retailer_products.ingredients, retailer_products.is_organic, retailer_products.keywords, retailer_products.kosher, retailer_products.large_image_url, retailer_products.locale_code, retailer_products.low_fat, retailer_products.margin_mix, retailer_products.monounsaturated_fat, retailer_products.name, retailer_products.normalized_weight_g, retailer_products.nutrition_sources, retailer_products.organic, retailer_products.original_name, retailer_products.polyunsaturated_fat, retailer_products.potassium, retailer_products.primary_aisle_id, retailer_products.primary_department_id, retailer_products.primary_image_url, retailer_products.protein, retailer_products.product_category, retailer_products.product_category_taxonomy, retailer_products.product_codes, retailer_products.product_enrichment_keywords, retailer_products.prop65_chemicals, retailer_products.saturated_fat, retailer_products.seasonality_description, retailer_products.serving_size, retailer_products.servings_per_container, retailer_products.should_scan, retailer_products.similar_name, retailer_products.size_uom, retailer_products.size_value, retailer_products.sodium, retailer_products.store_brand, retailer_products.sugars, retailer_products.sugar_free, retailer_products.synthetic_codes, retailer_products.thumb_image_url, retailer_products.trans_fat, retailer_products.unit_attributes, retailer_products.unit_count, retailer_products.vegan, retailer_products.vegetarian, retailer_products.warehouse_id, retailer_products.warnings, retailer_products.updated_at, retailer_products.product_category_l1_name, retailer_products.product_category_l2_name, retailer_products.product_category_l3_name, retailer_products.product_category_l4_name, retailer_products.product_category_l5_name, retailer_products.product_category_l6_name, retailer_products.product_category_l1_id, retailer_products.product_category_l2_id, retailer_products.product_category_l3_id, retailer_products.product_category_l4_id, retailer_products.product_category_l5_id, retailer_products.product_category_l6_id, retailer_products.prop65_text, retailer_products.retailer_reference_code, retailer_products.product_category_taxonomy_l4, retailer_products.common_weight, retailer_products.min_picked_weight, retailer_products.suggested_incremental_weight, retailer_products.aisle_names, retailer_products.department_names, retailer_products.antibiotic_free, retailer_products.breed_angus, retailer_products.breed_berkshire, retailer_products.breed_duroc_pork, retailer_products.breed_gloucestershire, retailer_products.breed_kobe_wagyu, retailer_products.breed_kurobata_berkshire, retailer_products.breed_mangalitsapork, retailer_products.breed_piedmontese, retailer_products.breed_veal, retailer_products.breed_wagyu, retailer_products.cooking_method_braise, retailer_products.cooking_method_grill, retailer_products.cooking_method_pan_fry, retailer_products.cooking_method_roast, retailer_products.farmed, retailer_products.free_range, retailer_products.grade, retailer_products.grass_fed, retailer_products.halal, retailer_products.hormone_free, retailer_products.meat_state, retailer_products.non_gmo, retailer_products.origin, retailer_products.packaging, retailer_products.pasture_raised, retailer_products.prep_breaded_crusted, retailer_products.prep_cured, retailer_products.prep_dry_aged, retailer_products.prep_glazed, retailer_products.prep_grilled, retailer_products.prep_marinated, retailer_products.prep_roasted, retailer_products.prep_rubbed, retailer_products.prep_seasoned, retailer_products.prep_smoked, retailer_products.prep_uncured, retailer_products.primal_cut, retailer_products.trim_bone_in, retailer_products.trim_boneless, retailer_products.trim_deveined, retailer_products.trim_dressed, retailer_products.trim_frenched, retailer_products.trim_head_on, retailer_products.trim_peeled, retailer_products.trim_shell_on, retailer_products.trim_shredded, retailer_products.trim_skin_on, retailer_products.trim_skinless, retailer_products.trim_sliced, retailer_products.trim_stuffed, retailer_products.trim_tail_off, retailer_products.trim_thick_cut, retailer_products.trim_thin_cut, retailer_products.wild_caught, retailer_products.product_tags, retailer_products.alcohol_type, retailer_products.alcohol_varietal, retailer_products.full_text_v1, retailer_products.dairy_free, retailer_products.temporary_override_elastic_retailer_product_id, retailer_products.milk_type_cow, retailer_products.milk_type_goat, retailer_products.milk_type_sheep, retailer_products.unit_value, retailer_products.is_catering, retailer_products.usa_snap_eligible, retailer_products.alcohol_rating, retailer_products.full_text_v2, retailer_products.secondary_aisle_ids, retailer_products.secondary_department_ids, retailer_products.variant_group_id, retailer_products.variant_attributes, retailer_products.product_codes_array, retailer_products.color, retailer_products.color_hex_code, retailer_products.flavor, items_availabilities.availability_score, products_conversions.score, deprecated_retailer_products_mls.data
  • Inner Unique: true
9. 0.127 1.706 ↓ 2.4 26 1

Nested Loop Left Join (cost=141.89..379.12 rows=11 width=4,064) (actual time=1.216..1.706 rows=26 loops=1)

  • Output: items_0.full_text_v2, items_0.retailer_id, items_0.product_id, items_0.inventory_area_id, items_0.item_id, items_0.elastic_item_id, items_0.generated_at, items_0.alcoholic, items_0.available, items_0.balance_on_hand_qty, items_0.bottle_deposit_bank_id, items_0.bottle_deposit_cents, items_0.bottle_deposit_currency, items_0.code_set_version, items_0.cost, items_0.cost_price_per_unit, items_0.cost_unit, items_0.full_price, items_0.has_retailer_promotion, items_0.is_alcoholic, items_0.item_type, items_0.loyalty_allowed, items_0.loyalty_cost_price_cents, items_0.loyalty_cost_price_currency, items_0.loyalty_cost_price_end_at, items_0.loyalty_cost_price_per_unit, items_0.loyalty_cost_price_start_at, items_0.loyalty_price, items_0.loyalty_price_active, items_0.loyalty_price_end_at, items_0.loyalty_price_start_at, items_0.on_sale, items_0.par_weight, items_0.price, items_0.price_multiple, items_0.price_per_unit, items_0.product_type, items_0.recently_shopped_at, items_0.retailer_product_score_browse_ctr_pop_v0, items_0.region_id, items_0.retailer_specified_alcoholic, items_0.retailer_sales_tax_rate, items_0.sale_allowed, items_0.sale_price_cents, items_0.sale_cost_price_per_unit, items_0.sale_end_at, items_0.sale_start_at, items_0.searchable, items_0.taxable, items_0.unata_available, items_0.unit, items_0.unit_count, items_0.unit_price, items_0.unlisted, items_0.use_weight, items_0.visible, items_0.created_at, items_0.updated_at, items_0.promotion_discount_begins_at, items_0.promotion_discount_ends_at, items_0.bogo_buy, items_0.bogo_get, items_0.max_in_cart, items_0.temporary_override_elastic_item_id, items_0.full_text, items_0.max_cart, items_0.cancel_lead_time, items_0.is_catering, items_0.order_lead_time, items_0.usa_snap_eligible, items_0.fulfill_pickup, items_0.fulfill_delivery, items_0.fulfill_instore, items_0.loyalty_members_only, items_0.promotion_group_id, items_0.is_hot, retailer_products.retailer_id, retailer_products.product_id, retailer_products.elastic_retailer_product_id, retailer_products.generated_at, retailer_products._aisle_sort_orders, retailer_products._dept_sort_orders, retailer_products.aisle_id, retailer_products.aisle_ids, retailer_products.aisle_name, retailer_products.aisle_sort_order, retailer_products.alcohol_attributes, retailer_products.alcohol_by_volume, retailer_products.alcoholic, retailer_products.allergens_keywords, retailer_products.bottle, retailer_products.brand_id, retailer_products.brand_name, retailer_products.calories, retailer_products.carbohydrate, retailer_products.canonical_product, retailer_products.cholesterol, retailer_products.cch_group_code, retailer_products.cch_item_code, retailer_products.classified_code, retailer_products.country_id, retailer_products.department_id, retailer_products.department_ids, retailer_products.department_name, retailer_products.dept_sort_order, retailer_products.description, retailer_products.details, retailer_products.directions, retailer_products.display_name, retailer_products.edible, retailer_products.fat, retailer_products.fat_calories, retailer_products.fat_free, retailer_products.fiber, retailer_products.form_factor, retailer_products.form_factor_descriptor, retailer_products.gluten_free, retailer_products.has_image, retailer_products.image_list, retailer_products.image_url, retailer_products.images, retailer_products.in_season, retailer_products.ingredients, retailer_products.is_organic, retailer_products.keywords, retailer_products.kosher, retailer_products.large_image_url, retailer_products.locale_code, retailer_products.low_fat, retailer_products.margin_mix, retailer_products.monounsaturated_fat, retailer_products.name, retailer_products.normalized_weight_g, retailer_products.nutrition_sources, retailer_products.organic, retailer_products.original_name, retailer_products.polyunsaturated_fat, retailer_products.potassium, retailer_products.primary_aisle_id, retailer_products.primary_department_id, retailer_products.primary_image_url, retailer_products.protein, retailer_products.product_category, retailer_products.product_category_taxonomy, retailer_products.product_codes, retailer_products.product_enrichment_keywords, retailer_products.prop65_chemicals, retailer_products.saturated_fat, retailer_products.seasonality_description, retailer_products.serving_size, retailer_products.servings_per_container, retailer_products.should_scan, retailer_products.similar_name, retailer_products.size_uom, retailer_products.size_value, retailer_products.sodium, retailer_products.store_brand, retailer_products.sugars, retailer_products.sugar_free, retailer_products.synthetic_codes, retailer_products.thumb_image_url, retailer_products.trans_fat, retailer_products.unit_attributes, retailer_products.unit_count, retailer_products.vegan, retailer_products.vegetarian, retailer_products.warehouse_id, retailer_products.warnings, retailer_products.updated_at, retailer_products.product_category_l1_name, retailer_products.product_category_l2_name, retailer_products.product_category_l3_name, retailer_products.product_category_l4_name, retailer_products.product_category_l5_name, retailer_products.product_category_l6_name, retailer_products.product_category_l1_id, retailer_products.product_category_l2_id, retailer_products.product_category_l3_id, retailer_products.product_category_l4_id, retailer_products.product_category_l5_id, retailer_products.product_category_l6_id, retailer_products.prop65_text, retailer_products.retailer_reference_code, retailer_products.product_category_taxonomy_l4, retailer_products.common_weight, retailer_products.min_picked_weight, retailer_products.suggested_incremental_weight, retailer_products.aisle_names, retailer_products.department_names, retailer_products.antibiotic_free, retailer_products.breed_angus, retailer_products.breed_berkshire, retailer_products.breed_duroc_pork, retailer_products.breed_gloucestershire, retailer_products.breed_kobe_wagyu, retailer_products.breed_kurobata_berkshire, retailer_products.breed_mangalitsapork, retailer_products.breed_piedmontese, retailer_products.breed_veal, retailer_products.breed_wagyu, retailer_products.cooking_method_braise, retailer_products.cooking_method_grill, retailer_products.cooking_method_pan_fry, retailer_products.cooking_method_roast, retailer_products.farmed, retailer_products.free_range, retailer_products.grade, retailer_products.grass_fed, retailer_products.halal, retailer_products.hormone_free, retailer_products.meat_state, retailer_products.non_gmo, retailer_products.origin, retailer_products.packaging, retailer_products.pasture_raised, retailer_products.prep_breaded_crusted, retailer_products.prep_cured, retailer_products.prep_dry_aged, retailer_products.prep_glazed, retailer_products.prep_grilled, retailer_products.prep_marinated, retailer_products.prep_roasted, retailer_products.prep_rubbed, retailer_products.prep_seasoned, retailer_products.prep_smoked, retailer_products.prep_uncured, retailer_products.primal_cut, retailer_products.trim_bone_in, retailer_products.trim_boneless, retailer_products.trim_deveined, retailer_products.trim_dressed, retailer_products.trim_frenched, retailer_products.trim_head_on, retailer_products.trim_peeled, retailer_products.trim_shell_on, retailer_products.trim_shredded, retailer_products.trim_skin_on, retailer_products.trim_skinless, retailer_products.trim_sliced, retailer_products.trim_stuffed, retailer_products.trim_tail_off, retailer_products.trim_thick_cut, retailer_products.trim_thin_cut, retailer_products.wild_caught, retailer_products.product_tags, retailer_products.alcohol_type, retailer_products.alcohol_varietal, retailer_products.full_text_v1, retailer_products.dairy_free, retailer_products.temporary_override_elastic_retailer_product_id, retailer_products.milk_type_cow, retailer_products.milk_type_goat, retailer_products.milk_type_sheep, retailer_products.unit_value, retailer_products.is_catering, retailer_products.usa_snap_eligible, retailer_products.alcohol_rating, retailer_products.full_text_v2, retailer_products.secondary_aisle_ids, retailer_products.secondary_department_ids, retailer_products.variant_group_id, retailer_products.variant_attributes, retailer_products.product_codes_array, retailer_products.color, retailer_products.color_hex_code, retailer_products.flavor, items_availabilities.availability_score, products_conversions.score
  • Inner Unique: true
10. 0.068 1.475 ↓ 2.4 26 1

Nested Loop Left Join (cost=141.47..285.81 rows=11 width=4,056) (actual time=1.120..1.475 rows=26 loops=1)

  • Output: items_0.full_text_v2, items_0.retailer_id, items_0.product_id, items_0.inventory_area_id, items_0.item_id, items_0.elastic_item_id, items_0.generated_at, items_0.alcoholic, items_0.available, items_0.balance_on_hand_qty, items_0.bottle_deposit_bank_id, items_0.bottle_deposit_cents, items_0.bottle_deposit_currency, items_0.code_set_version, items_0.cost, items_0.cost_price_per_unit, items_0.cost_unit, items_0.full_price, items_0.has_retailer_promotion, items_0.is_alcoholic, items_0.item_type, items_0.loyalty_allowed, items_0.loyalty_cost_price_cents, items_0.loyalty_cost_price_currency, items_0.loyalty_cost_price_end_at, items_0.loyalty_cost_price_per_unit, items_0.loyalty_cost_price_start_at, items_0.loyalty_price, items_0.loyalty_price_active, items_0.loyalty_price_end_at, items_0.loyalty_price_start_at, items_0.on_sale, items_0.par_weight, items_0.price, items_0.price_multiple, items_0.price_per_unit, items_0.product_type, items_0.recently_shopped_at, items_0.retailer_product_score_browse_ctr_pop_v0, items_0.region_id, items_0.retailer_specified_alcoholic, items_0.retailer_sales_tax_rate, items_0.sale_allowed, items_0.sale_price_cents, items_0.sale_cost_price_per_unit, items_0.sale_end_at, items_0.sale_start_at, items_0.searchable, items_0.taxable, items_0.unata_available, items_0.unit, items_0.unit_count, items_0.unit_price, items_0.unlisted, items_0.use_weight, items_0.visible, items_0.created_at, items_0.updated_at, items_0.promotion_discount_begins_at, items_0.promotion_discount_ends_at, items_0.bogo_buy, items_0.bogo_get, items_0.max_in_cart, items_0.temporary_override_elastic_item_id, items_0.full_text, items_0.max_cart, items_0.cancel_lead_time, items_0.is_catering, items_0.order_lead_time, items_0.usa_snap_eligible, items_0.fulfill_pickup, items_0.fulfill_delivery, items_0.fulfill_instore, items_0.loyalty_members_only, items_0.promotion_group_id, items_0.is_hot, retailer_products.retailer_id, retailer_products.product_id, retailer_products.elastic_retailer_product_id, retailer_products.generated_at, retailer_products._aisle_sort_orders, retailer_products._dept_sort_orders, retailer_products.aisle_id, retailer_products.aisle_ids, retailer_products.aisle_name, retailer_products.aisle_sort_order, retailer_products.alcohol_attributes, retailer_products.alcohol_by_volume, retailer_products.alcoholic, retailer_products.allergens_keywords, retailer_products.bottle, retailer_products.brand_id, retailer_products.brand_name, retailer_products.calories, retailer_products.carbohydrate, retailer_products.canonical_product, retailer_products.cholesterol, retailer_products.cch_group_code, retailer_products.cch_item_code, retailer_products.classified_code, retailer_products.country_id, retailer_products.department_id, retailer_products.department_ids, retailer_products.department_name, retailer_products.dept_sort_order, retailer_products.description, retailer_products.details, retailer_products.directions, retailer_products.display_name, retailer_products.edible, retailer_products.fat, retailer_products.fat_calories, retailer_products.fat_free, retailer_products.fiber, retailer_products.form_factor, retailer_products.form_factor_descriptor, retailer_products.gluten_free, retailer_products.has_image, retailer_products.image_list, retailer_products.image_url, retailer_products.images, retailer_products.in_season, retailer_products.ingredients, retailer_products.is_organic, retailer_products.keywords, retailer_products.kosher, retailer_products.large_image_url, retailer_products.locale_code, retailer_products.low_fat, retailer_products.margin_mix, retailer_products.monounsaturated_fat, retailer_products.name, retailer_products.normalized_weight_g, retailer_products.nutrition_sources, retailer_products.organic, retailer_products.original_name, retailer_products.polyunsaturated_fat, retailer_products.potassium, retailer_products.primary_aisle_id, retailer_products.primary_department_id, retailer_products.primary_image_url, retailer_products.protein, retailer_products.product_category, retailer_products.product_category_taxonomy, retailer_products.product_codes, retailer_products.product_enrichment_keywords, retailer_products.prop65_chemicals, retailer_products.saturated_fat, retailer_products.seasonality_description, retailer_products.serving_size, retailer_products.servings_per_container, retailer_products.should_scan, retailer_products.similar_name, retailer_products.size_uom, retailer_products.size_value, retailer_products.sodium, retailer_products.store_brand, retailer_products.sugars, retailer_products.sugar_free, retailer_products.synthetic_codes, retailer_products.thumb_image_url, retailer_products.trans_fat, retailer_products.unit_attributes, retailer_products.unit_count, retailer_products.vegan, retailer_products.vegetarian, retailer_products.warehouse_id, retailer_products.warnings, retailer_products.updated_at, retailer_products.product_category_l1_name, retailer_products.product_category_l2_name, retailer_products.product_category_l3_name, retailer_products.product_category_l4_name, retailer_products.product_category_l5_name, retailer_products.product_category_l6_name, retailer_products.product_category_l1_id, retailer_products.product_category_l2_id, retailer_products.product_category_l3_id, retailer_products.product_category_l4_id, retailer_products.product_category_l5_id, retailer_products.product_category_l6_id, retailer_products.prop65_text, retailer_products.retailer_reference_code, retailer_products.product_category_taxonomy_l4, retailer_products.common_weight, retailer_products.min_picked_weight, retailer_products.suggested_incremental_weight, retailer_products.aisle_names, retailer_products.department_names, retailer_products.antibiotic_free, retailer_products.breed_angus, retailer_products.breed_berkshire, retailer_products.breed_duroc_pork, retailer_products.breed_gloucestershire, retailer_products.breed_kobe_wagyu, retailer_products.breed_kurobata_berkshire, retailer_products.breed_mangalitsapork, retailer_products.breed_piedmontese, retailer_products.breed_veal, retailer_products.breed_wagyu, retailer_products.cooking_method_braise, retailer_products.cooking_method_grill, retailer_products.cooking_method_pan_fry, retailer_products.cooking_method_roast, retailer_products.farmed, retailer_products.free_range, retailer_products.grade, retailer_products.grass_fed, retailer_products.halal, retailer_products.hormone_free, retailer_products.meat_state, retailer_products.non_gmo, retailer_products.origin, retailer_products.packaging, retailer_products.pasture_raised, retailer_products.prep_breaded_crusted, retailer_products.prep_cured, retailer_products.prep_dry_aged, retailer_products.prep_glazed, retailer_products.prep_grilled, retailer_products.prep_marinated, retailer_products.prep_roasted, retailer_products.prep_rubbed, retailer_products.prep_seasoned, retailer_products.prep_smoked, retailer_products.prep_uncured, retailer_products.primal_cut, retailer_products.trim_bone_in, retailer_products.trim_boneless, retailer_products.trim_deveined, retailer_products.trim_dressed, retailer_products.trim_frenched, retailer_products.trim_head_on, retailer_products.trim_peeled, retailer_products.trim_shell_on, retailer_products.trim_shredded, retailer_products.trim_skin_on, retailer_products.trim_skinless, retailer_products.trim_sliced, retailer_products.trim_stuffed, retailer_products.trim_tail_off, retailer_products.trim_thick_cut, retailer_products.trim_thin_cut, retailer_products.wild_caught, retailer_products.product_tags, retailer_products.alcohol_type, retailer_products.alcohol_varietal, retailer_products.full_text_v1, retailer_products.dairy_free, retailer_products.temporary_override_elastic_retailer_product_id, retailer_products.milk_type_cow, retailer_products.milk_type_goat, retailer_products.milk_type_sheep, retailer_products.unit_value, retailer_products.is_catering, retailer_products.usa_snap_eligible, retailer_products.alcohol_rating, retailer_products.full_text_v2, retailer_products.secondary_aisle_ids, retailer_products.secondary_department_ids, retailer_products.variant_group_id, retailer_products.variant_attributes, retailer_products.product_codes_array, retailer_products.color, retailer_products.color_hex_code, retailer_products.flavor, items_availabilities.availability_score
  • Inner Unique: true
  • Join Filter: ((items_availabilities.inventory_area_id = items_0.inventory_area_id) AND (items_availabilities.item_id = items_0.item_id))
11. 0.082 1.407 ↓ 2.4 26 1

Nested Loop (cost=137.26..270.09 rows=11 width=4,048) (actual time=1.099..1.407 rows=26 loops=1)

  • Output: items_0.full_text_v2, items_0.retailer_id, items_0.product_id, items_0.inventory_area_id, items_0.item_id, items_0.elastic_item_id, items_0.generated_at, items_0.alcoholic, items_0.available, items_0.balance_on_hand_qty, items_0.bottle_deposit_bank_id, items_0.bottle_deposit_cents, items_0.bottle_deposit_currency, items_0.code_set_version, items_0.cost, items_0.cost_price_per_unit, items_0.cost_unit, items_0.full_price, items_0.has_retailer_promotion, items_0.is_alcoholic, items_0.item_type, items_0.loyalty_allowed, items_0.loyalty_cost_price_cents, items_0.loyalty_cost_price_currency, items_0.loyalty_cost_price_end_at, items_0.loyalty_cost_price_per_unit, items_0.loyalty_cost_price_start_at, items_0.loyalty_price, items_0.loyalty_price_active, items_0.loyalty_price_end_at, items_0.loyalty_price_start_at, items_0.on_sale, items_0.par_weight, items_0.price, items_0.price_multiple, items_0.price_per_unit, items_0.product_type, items_0.recently_shopped_at, items_0.retailer_product_score_browse_ctr_pop_v0, items_0.region_id, items_0.retailer_specified_alcoholic, items_0.retailer_sales_tax_rate, items_0.sale_allowed, items_0.sale_price_cents, items_0.sale_cost_price_per_unit, items_0.sale_end_at, items_0.sale_start_at, items_0.searchable, items_0.taxable, items_0.unata_available, items_0.unit, items_0.unit_count, items_0.unit_price, items_0.unlisted, items_0.use_weight, items_0.visible, items_0.created_at, items_0.updated_at, items_0.promotion_discount_begins_at, items_0.promotion_discount_ends_at, items_0.bogo_buy, items_0.bogo_get, items_0.max_in_cart, items_0.temporary_override_elastic_item_id, items_0.full_text, items_0.max_cart, items_0.cancel_lead_time, items_0.is_catering, items_0.order_lead_time, items_0.usa_snap_eligible, items_0.fulfill_pickup, items_0.fulfill_delivery, items_0.fulfill_instore, items_0.loyalty_members_only, items_0.promotion_group_id, items_0.is_hot, retailer_products.retailer_id, retailer_products.product_id, retailer_products.elastic_retailer_product_id, retailer_products.generated_at, retailer_products._aisle_sort_orders, retailer_products._dept_sort_orders, retailer_products.aisle_id, retailer_products.aisle_ids, retailer_products.aisle_name, retailer_products.aisle_sort_order, retailer_products.alcohol_attributes, retailer_products.alcohol_by_volume, retailer_products.alcoholic, retailer_products.allergens_keywords, retailer_products.bottle, retailer_products.brand_id, retailer_products.brand_name, retailer_products.calories, retailer_products.carbohydrate, retailer_products.canonical_product, retailer_products.cholesterol, retailer_products.cch_group_code, retailer_products.cch_item_code, retailer_products.classified_code, retailer_products.country_id, retailer_products.department_id, retailer_products.department_ids, retailer_products.department_name, retailer_products.dept_sort_order, retailer_products.description, retailer_products.details, retailer_products.directions, retailer_products.display_name, retailer_products.edible, retailer_products.fat, retailer_products.fat_calories, retailer_products.fat_free, retailer_products.fiber, retailer_products.form_factor, retailer_products.form_factor_descriptor, retailer_products.gluten_free, retailer_products.has_image, retailer_products.image_list, retailer_products.image_url, retailer_products.images, retailer_products.in_season, retailer_products.ingredients, retailer_products.is_organic, retailer_products.keywords, retailer_products.kosher, retailer_products.large_image_url, retailer_products.locale_code, retailer_products.low_fat, retailer_products.margin_mix, retailer_products.monounsaturated_fat, retailer_products.name, retailer_products.normalized_weight_g, retailer_products.nutrition_sources, retailer_products.organic, retailer_products.original_name, retailer_products.polyunsaturated_fat, retailer_products.potassium, retailer_products.primary_aisle_id, retailer_products.primary_department_id, retailer_products.primary_image_url, retailer_products.protein, retailer_products.product_category, retailer_products.product_category_taxonomy, retailer_products.product_codes, retailer_products.product_enrichment_keywords, retailer_products.prop65_chemicals, retailer_products.saturated_fat, retailer_products.seasonality_description, retailer_products.serving_size, retailer_products.servings_per_container, retailer_products.should_scan, retailer_products.similar_name, retailer_products.size_uom, retailer_products.size_value, retailer_products.sodium, retailer_products.store_brand, retailer_products.sugars, retailer_products.sugar_free, retailer_products.synthetic_codes, retailer_products.thumb_image_url, retailer_products.trans_fat, retailer_products.unit_attributes, retailer_products.unit_count, retailer_products.vegan, retailer_products.vegetarian, retailer_products.warehouse_id, retailer_products.warnings, retailer_products.updated_at, retailer_products.product_category_l1_name, retailer_products.product_category_l2_name, retailer_products.product_category_l3_name, retailer_products.product_category_l4_name, retailer_products.product_category_l5_name, retailer_products.product_category_l6_name, retailer_products.product_category_l1_id, retailer_products.product_category_l2_id, retailer_products.product_category_l3_id, retailer_products.product_category_l4_id, retailer_products.product_category_l5_id, retailer_products.product_category_l6_id, retailer_products.prop65_text, retailer_products.retailer_reference_code, retailer_products.product_category_taxonomy_l4, retailer_products.common_weight, retailer_products.min_picked_weight, retailer_products.suggested_incremental_weight, retailer_products.aisle_names, retailer_products.department_names, retailer_products.antibiotic_free, retailer_products.breed_angus, retailer_products.breed_berkshire, retailer_products.breed_duroc_pork, retailer_products.breed_gloucestershire, retailer_products.breed_kobe_wagyu, retailer_products.breed_kurobata_berkshire, retailer_products.breed_mangalitsapork, retailer_products.breed_piedmontese, retailer_products.breed_veal, retailer_products.breed_wagyu, retailer_products.cooking_method_braise, retailer_products.cooking_method_grill, retailer_products.cooking_method_pan_fry, retailer_products.cooking_method_roast, retailer_products.farmed, retailer_products.free_range, retailer_products.grade, retailer_products.grass_fed, retailer_products.halal, retailer_products.hormone_free, retailer_products.meat_state, retailer_products.non_gmo, retailer_products.origin, retailer_products.packaging, retailer_products.pasture_raised, retailer_products.prep_breaded_crusted, retailer_products.prep_cured, retailer_products.prep_dry_aged, retailer_products.prep_glazed, retailer_products.prep_grilled, retailer_products.prep_marinated, retailer_products.prep_roasted, retailer_products.prep_rubbed, retailer_products.prep_seasoned, retailer_products.prep_smoked, retailer_products.prep_uncured, retailer_products.primal_cut, retailer_products.trim_bone_in, retailer_products.trim_boneless, retailer_products.trim_deveined, retailer_products.trim_dressed, retailer_products.trim_frenched, retailer_products.trim_head_on, retailer_products.trim_peeled, retailer_products.trim_shell_on, retailer_products.trim_shredded, retailer_products.trim_skin_on, retailer_products.trim_skinless, retailer_products.trim_sliced, retailer_products.trim_stuffed, retailer_products.trim_tail_off, retailer_products.trim_thick_cut, retailer_products.trim_thin_cut, retailer_products.wild_caught, retailer_products.product_tags, retailer_products.alcohol_type, retailer_products.alcohol_varietal, retailer_products.full_text_v1, retailer_products.dairy_free, retailer_products.temporary_override_elastic_retailer_product_id, retailer_products.milk_type_cow, retailer_products.milk_type_goat, retailer_products.milk_type_sheep, retailer_products.unit_value, retailer_products.is_catering, retailer_products.usa_snap_eligible, retailer_products.alcohol_rating, retailer_products.full_text_v2, retailer_products.secondary_aisle_ids, retailer_products.secondary_department_ids, retailer_products.variant_group_id, retailer_products.variant_attributes, retailer_products.product_codes_array, retailer_products.color, retailer_products.color_hex_code, retailer_products.flavor
  • Inner Unique: true
12. 0.095 1.143 ↓ 2.4 26 1

Bitmap Heap Scan on public.items_0 (cost=136.98..182.73 rows=11 width=1,107) (actual time=1.064..1.143 rows=26 loops=1)

  • Output: items_0.full_text_v2, items_0.retailer_id, items_0.product_id, items_0.inventory_area_id, items_0.item_id, items_0.elastic_item_id, items_0.generated_at, items_0.alcoholic, items_0.available, items_0.balance_on_hand_qty, items_0.bottle_deposit_bank_id, items_0.bottle_deposit_cents, items_0.bottle_deposit_currency, items_0.code_set_version, items_0.cost, items_0.cost_price_per_unit, items_0.cost_unit, items_0.full_price, items_0.has_retailer_promotion, items_0.is_alcoholic, items_0.item_type, items_0.loyalty_allowed, items_0.loyalty_cost_price_cents, items_0.loyalty_cost_price_currency, items_0.loyalty_cost_price_end_at, items_0.loyalty_cost_price_per_unit, items_0.loyalty_cost_price_start_at, items_0.loyalty_price, items_0.loyalty_price_active, items_0.loyalty_price_end_at, items_0.loyalty_price_start_at, items_0.on_sale, items_0.par_weight, items_0.price, items_0.price_multiple, items_0.price_per_unit, items_0.product_type, items_0.recently_shopped_at, items_0.retailer_product_score_browse_ctr_pop_v0, items_0.region_id, items_0.retailer_specified_alcoholic, items_0.retailer_sales_tax_rate, items_0.sale_allowed, items_0.sale_price_cents, items_0.sale_cost_price_per_unit, items_0.sale_end_at, items_0.sale_start_at, items_0.searchable, items_0.taxable, items_0.unata_available, items_0.unit, items_0.unit_count, items_0.unit_price, items_0.unlisted, items_0.use_weight, items_0.visible, items_0.created_at, items_0.updated_at, items_0.promotion_discount_begins_at, items_0.promotion_discount_ends_at, items_0.bogo_buy, items_0.bogo_get, items_0.max_in_cart, items_0.temporary_override_elastic_item_id, items_0.full_text, items_0.max_cart, items_0.cancel_lead_time, items_0.is_catering, items_0.order_lead_time, items_0.usa_snap_eligible, items_0.fulfill_pickup, items_0.fulfill_delivery, items_0.fulfill_instore, items_0.loyalty_members_only, items_0.promotion_group_id, items_0.is_hot
  • Recheck Cond: ((items_0.full_text_v2 @@ '''appl'''::tsquery) AND (items_0.inventory_area_id = 20,636))
  • Filter: (items_0.visible AND items_0.available AND (NOT items_0.alcoholic))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=27
13. 0.013 1.048 ↓ 0.0 0 1

BitmapAnd (cost=136.98..136.98 rows=12 width=0) (actual time=1.047..1.048 rows=0 loops=1)

14. 0.220 0.220 ↑ 1.0 80 1

Bitmap Index Scan on search_index_v2_items_0 (cost=0.00..20.80 rows=80 width=0) (actual time=0.220..0.220 rows=80 loops=1)

  • Index Cond: ((items_0.full_text_v2 @@ '''appl'''::tsquery) AND (items_0.inventory_area_id = 20,636))
15. 0.815 0.815 ↓ 1.5 4,223 1

Bitmap Index Scan on items_0_available_visible_idx (cost=0.00..115.93 rows=2,764 width=0) (actual time=0.814..0.815 rows=4,223 loops=1)

  • Index Cond: ((items_0.available = true) AND (items_0.visible = true))
16. 0.182 0.182 ↑ 1.0 1 26

Index Scan using index_retailer_products_on_retailer_id_and_product_id on public.retailer_products (cost=0.29..7.94 rows=1 width=2,941) (actual time=0.007..0.007 rows=1 loops=26)

  • Output: retailer_products.retailer_id, retailer_products.product_id, retailer_products.elastic_retailer_product_id, retailer_products.generated_at, retailer_products._aisle_sort_orders, retailer_products._dept_sort_orders, retailer_products.aisle_id, retailer_products.aisle_ids, retailer_products.aisle_name, retailer_products.aisle_sort_order, retailer_products.alcohol_attributes, retailer_products.alcohol_by_volume, retailer_products.alcoholic, retailer_products.allergens_keywords, retailer_products.bottle, retailer_products.brand_id, retailer_products.brand_name, retailer_products.calories, retailer_products.carbohydrate, retailer_products.canonical_product, retailer_products.cholesterol, retailer_products.cch_group_code, retailer_products.cch_item_code, retailer_products.classified_code, retailer_products.country_id, retailer_products.department_id, retailer_products.department_ids, retailer_products.department_name, retailer_products.dept_sort_order, retailer_products.description, retailer_products.details, retailer_products.directions, retailer_products.display_name, retailer_products.edible, retailer_products.fat, retailer_products.fat_calories, retailer_products.fat_free, retailer_products.fiber, retailer_products.form_factor, retailer_products.form_factor_descriptor, retailer_products.gluten_free, retailer_products.has_image, retailer_products.image_list, retailer_products.image_url, retailer_products.images, retailer_products.in_season, retailer_products.ingredients, retailer_products.is_organic, retailer_products.keywords, retailer_products.kosher, retailer_products.large_image_url, retailer_products.locale_code, retailer_products.low_fat, retailer_products.margin_mix, retailer_products.monounsaturated_fat, retailer_products.name, retailer_products.normalized_weight_g, retailer_products.nutrition_sources, retailer_products.organic, retailer_products.original_name, retailer_products.polyunsaturated_fat, retailer_products.potassium, retailer_products.primary_aisle_id, retailer_products.primary_department_id, retailer_products.primary_image_url, retailer_products.protein, retailer_products.product_category, retailer_products.product_category_taxonomy, retailer_products.product_codes, retailer_products.product_enrichment_keywords, retailer_products.prop65_chemicals, retailer_products.saturated_fat, retailer_products.seasonality_description, retailer_products.serving_size, retailer_products.servings_per_container, retailer_products.should_scan, retailer_products.similar_name, retailer_products.size_uom, retailer_products.size_value, retailer_products.sodium, retailer_products.store_brand, retailer_products.sugars, retailer_products.sugar_free, retailer_products.synthetic_codes, retailer_products.thumb_image_url, retailer_products.trans_fat, retailer_products.unit_attributes, retailer_products.unit_count, retailer_products.vegan, retailer_products.vegetarian, retailer_products.warehouse_id, retailer_products.warnings, retailer_products.updated_at, retailer_products.product_category_l1_name, retailer_products.product_category_l2_name, retailer_products.product_category_l3_name, retailer_products.product_category_l4_name, retailer_products.product_category_l5_name, retailer_products.product_category_l6_name, retailer_products.product_category_l1_id, retailer_products.product_category_l2_id, retailer_products.product_category_l3_id, retailer_products.product_category_l4_id, retailer_products.product_category_l5_id, retailer_products.product_category_l6_id, retailer_products.prop65_text, retailer_products.retailer_reference_code, retailer_products.product_category_taxonomy_l4, retailer_products.common_weight, retailer_products.min_picked_weight, retailer_products.suggested_incremental_weight, retailer_products.aisle_names, retailer_products.department_names, retailer_products.antibiotic_free, retailer_products.breed_angus, retailer_products.breed_berkshire, retailer_products.breed_duroc_pork, retailer_products.breed_gloucestershire, retailer_products.breed_kobe_wagyu, retailer_products.breed_kurobata_berkshire, retailer_products.breed_mangalitsapork, retailer_products.breed_piedmontese, retailer_products.breed_veal, retailer_products.breed_wagyu, retailer_products.cooking_method_braise, retailer_products.cooking_method_grill, retailer_products.cooking_method_pan_fry, retailer_products.cooking_method_roast, retailer_products.farmed, retailer_products.free_range, retailer_products.grade, retailer_products.grass_fed, retailer_products.halal, retailer_products.hormone_free, retailer_products.meat_state, retailer_products.non_gmo, retailer_products.origin, retailer_products.packaging, retailer_products.pasture_raised, retailer_products.prep_breaded_crusted, retailer_products.prep_cured, retailer_products.prep_dry_aged, retailer_products.prep_glazed, retailer_products.prep_grilled, retailer_products.prep_marinated, retailer_products.prep_roasted, retailer_products.prep_rubbed, retailer_products.prep_seasoned, retailer_products.prep_smoked, retailer_products.prep_uncured, retailer_products.primal_cut, retailer_products.trim_bone_in, retailer_products.trim_boneless, retailer_products.trim_deveined, retailer_products.trim_dressed, retailer_products.trim_frenched, retailer_products.trim_head_on, retailer_products.trim_peeled, retailer_products.trim_shell_on, retailer_products.trim_shredded, retailer_products.trim_skin_on, retailer_products.trim_skinless, retailer_products.trim_sliced, retailer_products.trim_stuffed, retailer_products.trim_tail_off, retailer_products.trim_thick_cut, retailer_products.trim_thin_cut, retailer_products.wild_caught, retailer_products.product_tags, retailer_products.alcohol_type, retailer_products.alcohol_varietal, retailer_products.full_text_v1, retailer_products.dairy_free, retailer_products.temporary_override_elastic_retailer_product_id, retailer_products.milk_type_cow, retailer_products.milk_type_goat, retailer_products.milk_type_sheep, retailer_products.unit_value, retailer_products.is_catering, retailer_products.usa_snap_eligible, retailer_products.alcohol_rating, retailer_products.full_text_v2, retailer_products.secondary_aisle_ids, retailer_products.secondary_department_ids, retailer_products.variant_group_id, retailer_products.variant_attributes, retailer_products.product_codes_array, retailer_products.color, retailer_products.color_hex_code, retailer_products.flavor
  • Index Cond: ((retailer_products.retailer_id = items_0.retailer_id) AND (retailer_products.product_id = items_0.product_id))
17. 0.000 0.000 ↓ 0.0 0 26

Materialize (cost=4.21..14.39 rows=7 width=20) (actual time=0.000..0.000 rows=0 loops=26)

  • Output: items_availabilities.availability_score, items_availabilities.item_id, items_availabilities.inventory_area_id
18. 0.002 0.004 ↓ 0.0 0 1

Bitmap Heap Scan on public.items_availabilities_0 items_availabilities (cost=4.21..14.35 rows=7 width=20) (actual time=0.003..0.004 rows=0 loops=1)

  • Output: items_availabilities.availability_score, items_availabilities.item_id, items_availabilities.inventory_area_id
  • Recheck Cond: (items_availabilities.inventory_area_id = 20,636)
19. 0.002 0.002 ↓ 0.0 0 1

Bitmap Index Scan on items_availabilities_0_inventory_area_id_idx (cost=0.00..4.21 rows=7 width=0) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (items_availabilities.inventory_area_id = 20,636)
20. 0.104 0.104 ↓ 0.0 0 26

Index Scan using index_products_conversions_on_cpk on public.products_conversions (cost=0.42..8.44 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=26)

  • Output: products_conversions.version, products_conversions.product_id, products_conversions.query, products_conversions.score
  • Index Cond: ((products_conversions.version = 3) AND (products_conversions.product_id = items_0.product_id) AND ((products_conversions.query)::text = 'apple'::text))
21. 0.104 0.104 ↑ 1.0 1 26

Index Scan using index_deprecated_retailer_products_mls_cpk on public.deprecated_retailer_products_mls (cost=0.29..7.94 rows=1 width=619) (actual time=0.004..0.004 rows=1 loops=26)

  • Output: deprecated_retailer_products_mls.retailer_id, deprecated_retailer_products_mls.product_id, deprecated_retailer_products_mls.machine_learning_elastic_retailer_product_id, deprecated_retailer_products_mls.updated_at, deprecated_retailer_products_mls.data
  • Index Cond: ((deprecated_retailer_products_mls.retailer_id = items_0.retailer_id) AND (deprecated_retailer_products_mls.product_id = items_0.product_id))
22. 0.104 0.104 ↑ 1.0 1 26

Index Scan using index_deprecated_retailer_products_cpgs_cpk on public.deprecated_retailer_products_cpgs (cost=0.29..7.94 rows=1 width=36) (actual time=0.004..0.004 rows=1 loops=26)

  • Output: deprecated_retailer_products_cpgs.retailer_id, deprecated_retailer_products_cpgs.product_id, deprecated_retailer_products_cpgs.cpg_elastic_retailer_product_id, deprecated_retailer_products_cpgs.updated_at, deprecated_retailer_products_cpgs.data
  • Index Cond: ((deprecated_retailer_products_cpgs.retailer_id = items_0.retailer_id) AND (deprecated_retailer_products_cpgs.product_id = items_0.product_id))
23. 0.104 0.104 ↑ 1.0 1 26

Index Scan using deprecated_items_denormalized_retailer_id_product_id_invent_idx on public.deprecated_items_denormalizeds_0 (cost=0.29..8.31 rows=1 width=901) (actual time=0.004..0.004 rows=1 loops=26)

  • Output: deprecated_items_denormalizeds_0.retailer_id, deprecated_items_denormalizeds_0.product_id, deprecated_items_denormalizeds_0.inventory_area_id, deprecated_items_denormalizeds_0.item_id, deprecated_items_denormalizeds_0.generated_at, deprecated_items_denormalizeds_0.blackout_times, deprecated_items_denormalizeds_0.buy_n_get_n, deprecated_items_denormalizeds_0.combo_promotions, deprecated_items_denormalizeds_0.configurable_products, deprecated_items_denormalizeds_0.disclaimer, deprecated_items_denormalizeds_0.display_size, deprecated_items_denormalizeds_0.in_store_location, deprecated_items_denormalizeds_0.item_tax_data, deprecated_items_denormalizeds_0.retailer_department, deprecated_items_denormalizeds_0.retailer_lookup_code, deprecated_items_denormalizeds_0.retailer_promotion_details, deprecated_items_denormalizeds_0.retailer_reference_code, deprecated_items_denormalizeds_0.retailer_sales_tax_code, deprecated_items_denormalizeds_0.retailer_specific_metadata, deprecated_items_denormalizeds_0.size, deprecated_items_denormalizeds_0.storage_temp, deprecated_items_denormalizeds_0.unata_fulfillment_types, deprecated_items_denormalizeds_0.zone_ids, deprecated_items_denormalizeds_0.elastic_item_id, deprecated_items_denormalizeds_0.temporary_override_elastic_item_id, deprecated_items_denormalizeds_0.vertex_tax_code, deprecated_items_denormalizeds_0.combo_promotions_jsonb
  • Index Cond: ((deprecated_items_denormalizeds_0.retailer_id = items_0.retailer_id) AND (deprecated_items_denormalizeds_0.product_id = items_0.product_id) AND (deprecated_items_denormalizeds_0.inventory_area_id = items_0.inventory_area_id) AND (deprecated_items_denormalizeds_0.inventory_area_id = 20,636))
24. 0.023 0.026 ↓ 0.0 0 26

Materialize (cost=4.19..12.69 rows=5 width=16) (actual time=0.001..0.001 rows=0 loops=26)

  • Output: retailer_term_v2s.score, retailer_term_v2s.product_id, retailer_term_v2s.retailer_id
25. 0.001 0.003 ↓ 0.0 0 1

Bitmap Heap Scan on public.retailer_products_term_v2s retailer_term_v2s (cost=4.19..12.66 rows=5 width=16) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: retailer_term_v2s.score, retailer_term_v2s.product_id, retailer_term_v2s.retailer_id
  • Recheck Cond: (retailer_term_v2s.term = 'apple'::text)
26. 0.002 0.002 ↓ 0.0 0 1

Bitmap Index Scan on index_retailer_products_term_v2s_on_natural_key (cost=0.00..4.19 rows=5 width=0) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (retailer_term_v2s.term = 'apple'::text)
27. 0.012 0.026 ↓ 0.0 0 26

Materialize (cost=0.15..8.18 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=26)

  • Output: generic_term_v2s.score, generic_term_v2s.product_id
28. 0.014 0.014 ↓ 0.0 0 1

Index Scan using index_retailer_products_term_v2s_on_natural_key on public.retailer_products_term_v2s generic_term_v2s (cost=0.15..8.17 rows=1 width=12) (actual time=0.014..0.014 rows=0 loops=1)

  • Output: generic_term_v2s.score, generic_term_v2s.product_id
  • Index Cond: ((generic_term_v2s.term = 'apple'::text) AND (generic_term_v2s.retailer_id = '-1'::integer))
Planning time : 21.592 ms