23 不用搜索引擎，也能搜索

select lab.prove('english stems deploys, failing, rolled, and deployment and drops the stop words',
  to_tsvector('english', 'The deploys were failing, so we rolled back the deployment')
    = $$'back':8 'deploy':2,10 'fail':4 'roll':7$$::tsvector);
select lab.prove('the english stop-word list has 127 words, and a query of only stop words has no lexemes left',
  numnode(plainto_tsquery('english', 'to be or not to be')) = 0);
select lab.prove('stemming collides: organization matches organ, university matches universe',
  to_tsvector('english', 'organization') @@ to_tsquery('english', 'organ')
  and to_tsvector('english', 'university') @@ to_tsquery('english', 'universe'));
select lab.prove('plain english keeps the accent, so a search for zurich misses Zürich',
  not (to_tsvector('english', 'Requests from Zürich') @@ websearch_to_tsquery('english', 'zurich'))
  and to_tsvector('app_english', 'Requests from Zürich') @@ websearch_to_tsquery('app_english', 'zurich'));

select lab.prove('the stored tsvector roughly doubles the heap (3,347 pages without it, 6,766 with)',
  pg_relation_size('docs') / pg_relation_size('docs_expr')::numeric between 1.8 and 2.6);
select lab.prove('the average tsvector is bigger than the text it came from',
  (select avg(pg_column_size(search)) > avg(pg_column_size(body)) from docs));
select lab.prove('loading the table with the stored column writes more WAL',
  (select s.wal > e.wal * 1.15 from cost s, cost e where s.what = 'stored' and e.what = 'expression'));
select lab.ms(:'rank_stored') as stored_ms, lab.ms(:'rank_expr') as expr_ms \gset
select lab.prove('ranking through the expression index is more than 5 times slower: it re-parses every match',
  :expr_ms > 5 * :stored_ms);

do $$ begin
  perform to_tsquery('english', 'failed payment');
  raise exception 'no error';
exception when syntax_error then
  if sqlerrm not like 'syntax error in tsquery%' then raise; end if;
end $$;
select lab.prove('to_tsquery rejects two plain words with a syntax error', true);
select lab.prove('garbage input never raises an error',
  websearch_to_tsquery('english', 'payment & ) ( !')::text = $$'payment'$$);
select lab.prove('phraseto_tsquery counts the dropped stop word as a gap of two',
  phraseto_tsquery('english', 'roll back the deploy')::text = $$'roll' <-> 'back' <2> 'deploy'$$
  and to_tsvector('english', 'roll back a deploy') @@ phraseto_tsquery('english', 'roll back the deploy'));
select lab.prove('the phrase matches far fewer articles than the two words anywhere (about 4,200 against 7,800)',
  :phrase between 3800 and 4600 and :anded between 7400 and 8300);

select lab.prove('ts_rank_cd puts the article with four adjacent "failed payment"s first',
  (select r2.cd > r1.cd from rank_scores r1, rank_scores r2 where r1.id = 1 and r2.id = 2));
select lab.prove('dividing by length (normalization 2) cuts the long article to less than a quarter of the short one',
  (select r2.rank_len < r1.rank_len / 4 from rank_scores r1, rank_scores r2 where r1.id = 1 and r2.id = 2));

select lab.ms(:'search') as search_ms, lab.ms(:'page') as page_ms, lab.ms(:'every') as every_ms \gset
select lab.prove('snippets for every match cost more than 10 times a page of 20 with snippets',
  :every_ms > 10 * :page_ms);
select lab.prove('the planner computes ts_headline after the sort and limit, only for the 20 rows shown',
  (select (p -> 'Plan' ->> 'Node Type') = 'Limit'
      and (p -> 'Plan' -> 'Plans' -> 0 ->> 'Node Type') = 'Result'
      and (p -> 'Plan' -> 'Plans' -> 0 -> 'Plans' -> 0 ->> 'Node Type') = 'Sort'
     from lab.plan(:'page') p));

select lab.prove('a vector(1536) is 4 x 1,536 + 8 = 6,152 bytes',
  (select pg_column_size(l2_normalize(embedding)) = 6152 from wide limit 1));
select lab.prove('2,000 vectors: 15 heap pages and one TOAST page per vector',
  pg_relation_size('wide') / 8192 < 20
  and (select pg_relation_size(reltoastrelid) / 8192 between 1990 and 2010 from pg_class where relname = 'wide'));
select lab.prove('counting rows never touches the vectors: under 20 pages',
  lab.buffers('select count(*) from wide') < 20);
select lab.prove('exact search reads about six buffers per toasted vector, five times the plain layout',
  :toasted between 11000 and 13000 and :plain between 1990 and 2100);
select lab.prove('a binary-quantized bit(1536) is 200 bytes and stays in the heap: the table has no TOAST table at all',
  (select pg_column_size(embedding) = 200 from wide_bit limit 1)
  and (select reltoastrelid = 0 from pg_class where relname = 'wide_bit'));

build=$(sql -c "set client_min_messages = notice" -c "show maintenance_work_mem" \
  -c "create index doc_embeddings_hnsw on doc_embeddings using hnsw (embedding vector_cosine_ops)" 2>&1)
prove "with 64 MB the build warns: hnsw graph no longer fits into maintenance_work_mem" \
  has "hnsw graph no longer fits into maintenance_work_mem" "$build"

select lab.prove('the HNSW index (98 MB here) is bigger than the table it indexes',
  pg_relation_size('doc_embeddings_hnsw') > pg_relation_size('doc_embeddings'));
select lab.prove('at the default ef_search of 40, recall@10 is 0.9 or better',
  (select recall >= 0.9 from sweep where ef = 40));
select lab.prove('raising ef_search raises recall, to 0.98 or better at 400',
  (select max(recall) filter (where ef >= 200) > min(recall) filter (where ef = 10)
      and max(recall) filter (where ef = 400) >= 0.98 from sweep));
select lab.prove('ef_search 400 reads more than 4 times the pages of ef_search 40',
  (select b.buffers > 4 * a.buffers from sweep a, sweep b where a.ef = 40 and b.ef = 400));

select lab.prove('with 50 lists and the default single probe, recall@10 is under 0.8',
  (select recall < 0.8 from probes where probes = 1));
select lab.prove('the halfvec index is under two thirds the size, the bit index under a fifth',
  pg_relation_size('doc_embeddings_half') < 0.67 * pg_relation_size('doc_embeddings_hnsw')
  and pg_relation_size('doc_embeddings_bit') < 0.2 * pg_relation_size('doc_embeddings_hnsw'));
select lab.prove('halfvec keeps recall above 0.95', :half >= 0.95);
select lab.prove('re-ranking 400 bit candidates by the full vectors lifts recall above 0.85',
  :rerank400 > 0.85 and :rerank400 > :rerank100 and :rerank100 > :bits);

select lab.prove('with a 10% filter and ef_search 40, limit 10 returns about 4 rows on average',
  (select avg_rows between 2.5 and 5.5 and min_rows = 0 from filtered where mode = 'off'));
select lab.prove('hnsw.iterative_scan = relaxed_order returns all 10 rows for every query',
  (select avg_rows = 10 and min_rows = 10 from filtered where mode = 'relaxed_order'));
select lab.prove('a tenant filter (46 rows) uses the B-tree and sorts exactly, ignoring HNSW',
  (select count(*) = 46 from doc_embeddings where account_id = 42)
  and lab.plan($$ select doc_id from doc_embeddings where account_id = 42
     order by embedding <=> (select embedding from queries where id = 1) limit 10 $$)::text
     like '%doc_embeddings_account_idx%'
  and lab.plan($$ select doc_id from doc_embeddings where account_id = 42
     order by embedding <=> (select embedding from queries where id = 1) limit 10 $$)::text
     not like '%doc_embeddings_hnsw%');

select lab.prove('fusion returns 10 rows from both lists, and any article both lists found ranks above every article only one found',
  (select count(*) = 10 and count(fts_rank) > 0 and count(vec_rank) > 0
     from hybrid_search('failed payment', (select embedding from query_embeddings where name = 'billing')))
  and (select coalesce(min(score) filter (where fts_rank is not null and vec_rank is not null), 1)
              > coalesce(max(score) filter (where fts_rank is null or vec_rank is null), 0)
         from hybrid_search('failed payment', (select embedding from query_embeddings where name = 'billing'), 100)));
select lab.prove('card declined matches no text at all; the vector side still answers',
  (select count(*) = 0 from docs where search @@ websearch_to_tsquery('english', 'card declined'))
  and (select count(*) = 10 and bool_and(vec_rank is not null)
         from hybrid_search('card declined', (select embedding from query_embeddings where name = 'billing'))));
select lab.prove('ordering by distance to a vector from a join can''t use the HNSW index',
  'Seq Scan' = any (lab.nodes($$ select e.doc_id from doc_embeddings e, query_embeddings q
                                 where q.name = 'billing'
                                 order by e.embedding <=> q.embedding limit 10 $$)));