Hybrid queries

Example hybrid table

CREATE TABLE app.docs (
  id text PRIMARY KEY,
  tenant text,
  category text,
  title text,
  body text,
  doc document,
  embedding vector<float, 2>
);

Base descriptors

CREATE INDEX docs_tenant_idx
ON app.docs (tenant)
USING 'sis'
WITH OPTIONS = {'family':'auto'};

CREATE INDEX docs_doc_category_idx
ON app.docs (doc_get_text(doc, '$.category'))
USING 'sis'
WITH OPTIONS = {'family':'auto'};

CREATE ACCESS PATH docs_title_trigram
ON app.docs
USING TRIGRAM_TEXT_SIS(title)
WITH (
  case_mode = 'sensitive',
  normalization_contract = 'raw_utf8_bytes_v1',
  regex_dialect = 'mts_regex_v1'
);

CREATE INDEX docs_body_fulltext
ON app.docs (body AS body)
USING 'sis'
WITH OPTIONS = {
  'mode':'FULLTEXT',
  'analyzer':'english_v1',
  'positions':'true',
  'max_indexed_value_bytes':'256'
};

CREATE INDEX docs_embedding_idx
ON app.docs (embedding)
USING 'sis'
WITH OPTIONS = {'search_mode':'exact','budget':'64'};

Hybrid descriptors

CREATE ACCESS PATH docs_scalar_doc
ON app.docs
USING HYBRID
WITH (
  driver_candidates = 'scalar(tenant)',
  filters = 'doc_get_text(doc, ''$.category'')',
  budget = 'bounded'
);

CREATE ACCESS PATH docs_raw_tenant_doc
ON app.docs
USING HYBRID
WITH (
  driver_candidates = 'text(title)',
  filters = 'tenant,doc_get_text(doc, ''$.category'')',
  budget = 'bounded'
);

CREATE ACCESS PATH docs_vec_tenant_doc
ON app.docs
USING HYBRID
WITH (
  driver_candidates = 'vector(embedding)',
  filters = 'tenant,doc_get_text(doc, ''$.category'')',
  budget = 'bounded',
  search_mode = 'exact'
);

CREATE ACCESS PATH docs_text_tenant_doc
ON app.docs
USING HYBRID
WITH (
  driver_candidates = 'fulltext(body)',
  filters = 'tenant,doc_get_text(doc, ''$.category'')',
  fulltext.analyzer = 'english_v1',
  fulltext.ranking = 'bm25_v1',
  budget = 'bounded'
);

CREATE ACCESS PATH docs_mixed_raw_tenant_doc
ON app.docs
USING HYBRID
WITH (
  driver_candidates = 'vector(embedding),fulltext(body),text(title)',
  filters = 'tenant,doc_get_text(doc, ''$.category'')',
  fulltext.analyzer = 'english_v1',
  fulltext.ranking = 'bm25_v1',
  budget = 'bounded',
  search_mode = 'exact'
);

Hybrid query shapes

Full hybrid:

SELECT tenant, id, category, title, body
FROM app.docs
WHERE tenant = 'tenant-alpha'
  AND doc_get_text(doc, '$.category') = 'returns'
  AND title LIKE '%refund%'
  AND body MATCH 'refund'
ORDER BY embedding ANN OF [0.0, 0.0]
WITH (topk = 10)
LIMIT 10;

Prepared MTS SQL full hybrid:

SELECT tenant, id, category, title, body
FROM app.docs
WHERE tenant = $1
  AND doc_get_text(doc, '$.category') = $2
  AND title LIKE $3
  AND body MATCH $4
ORDER BY embedding ANN OF $5
WITH (topk = 10)
LIMIT 10;

Hybrid refusal examples