compound_index.sql

-- Denormalize keywords onto items
CREATE FUNCTION denormalize_product_keywords_to_items() RETURNS trigger
  LANGUAGE plpgsql
AS $$
  begin
    select keywords into NEW.keywords from products where NEW.product_id = products.product_id;
    return NEW;
  end
$$;

-- Whenever an item is inserted or updated
CREATE TRIGGER items_search_index 
BEFORE INSERT OR UPDATE ON items 
FOR EACH ROW EXECUTE FUNCTION denormalize_product_keywords_to_items();

-- You'll need to enable this extension to mix btree and gin indexes
CREATE EXTENSION IF NOT EXISTS btree_gin WITH SCHEMA public;

-- Create the compound index
CREATE INDEX items_search_index ON items USING gin(keywords, retail_location_id);