HNSW 인덱스 만들기와 검색 시 조정

create index on documents
using hnsw (embedding vector_cosine_ops)
with (m = 16, ef_construction = 64);

begin;
set local hnsw.ef_search = 100;
select id, content from documents
order by embedding <=> $1 limit 10;
commit;

tsvector 컬럼을 자동으로 유지하기

create table documents (
  id bigint primary key generated always as identity,
  content text,
  fts tsvector generated always as (to_tsvector('english', content)) stored,
  embedding extensions.vector(512)
);

create index on documents using gin (fts);

본문이 바뀔 때마다 fts 컬럼이 자동으로 갱신된다.

Supabase 공식 hybrid_search 함수

create or replace function hybrid_search(
  query_text text, query_embedding extensions.vector(512),
  match_count int, full_text_weight float default 1.0,
  semantic_weight float default 1.0, rrf_k int default 50
) returns setof documents language sql as $$
with full_text as (
  select id, row_number() over (
    order by ts_rank_cd(fts, websearch_to_tsquery(query_text)) desc) as rank_ix
  from documents where fts @@ websearch_to_tsquery(query_text)
  limit least(match_count, 30) * 2
),
semantic as (
  select id, row_number() over (order by embedding <#> query_embedding) as rank_ix
  from documents limit least(match_count, 30) * 2
)
select documents.* from full_text
full outer join semantic on full_text.id = semantic.id
join documents on documents.id = coalesce(full_text.id, semantic.id)
order by coalesce(1.0/(rrf_k+full_text.rank_ix),0)*full_text_weight
       + coalesce(1.0/(rrf_k+semantic.rank_ix),0)*semantic_weight desc
limit least(match_count, 30);
$$;