Spaces:
Running
Running
File size: 3,858 Bytes
baaa888 2e096a8 baaa888 2e096a8 baaa888 2e096a8 baaa888 2e096a8 baaa888 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 | BEGIN;
SET TRANSACTION READ ONLY;
DO $$
DECLARE
embedding_type TEXT;
embedding_count BIGINT;
active_count BIGINT;
profile_count BIGINT;
invalid_norm_count BIGINT;
BEGIN
SELECT format_type(attribute.atttypid, attribute.atttypmod)
INTO embedding_type
FROM pg_attribute AS attribute
WHERE attribute.attrelid = 'public.place_embeddings_semantic_v1'::regclass
AND attribute.attname = 'embedding'
AND NOT attribute.attisdropped;
IF embedding_type IS DISTINCT FROM 'vector(768)' THEN
RAISE EXCEPTION 'Expected place_embeddings_semantic_v1.embedding vector(768), got %', embedding_type;
END IF;
IF to_regprocedure('public.match_places_semantic_v1(vector,integer,jsonb)') IS NULL THEN
RAISE EXCEPTION 'match_places_semantic_v1 is missing';
END IF;
IF to_regprocedure('public.search_places_semantic_v1(text,vector,integer,jsonb)') IS NULL THEN
RAISE EXCEPTION 'search_places_semantic_v1 is missing';
END IF;
IF NOT EXISTS (
SELECT 1
FROM pg_indexes
WHERE schemaname = 'public'
AND tablename = 'place_embeddings_semantic_v1'
AND indexdef ILIKE '%USING hnsw%'
) THEN
RAISE EXCEPTION 'Places semantic HNSW index is missing';
END IF;
SELECT count(*)
INTO embedding_count
FROM public.place_embeddings_semantic_v1;
IF embedding_count = 0 THEN
RAISE EXCEPTION 'place_embeddings_semantic_v1 is empty; run the backfill first';
END IF;
SELECT count(*) FILTER (WHERE place.is_active)
INTO active_count
FROM public.place_embeddings_semantic_v1 AS place;
IF active_count = 0 THEN
RAISE EXCEPTION 'place_embeddings_semantic_v1 has no active rows';
END IF;
SELECT count(DISTINCT (
place.embedding_model,
place.embedding_version,
place.metadata->>'semantic_document_version'
))
INTO profile_count
FROM public.place_embeddings_semantic_v1 AS place;
IF profile_count <> 1 OR EXISTS (
SELECT 1
FROM public.place_embeddings_semantic_v1 AS place
WHERE place.metadata->>'semantic_document_version' IS NULL
) THEN
RAISE EXCEPTION 'Expected exactly one model/version/document profile, got %', profile_count;
END IF;
SELECT count(*)
INTO invalid_norm_count
FROM public.place_embeddings_semantic_v1 AS place
WHERE abs(1.0 + (place.embedding <#> place.embedding)) > 0.02;
IF invalid_norm_count > 0 THEN
RAISE EXCEPTION 'Found % embeddings outside the expected unit-norm tolerance', invalid_norm_count;
END IF;
END
$$;
-- Every provider vector is L2-normalized before it is written. The negative
-- inner product of a vector with itself is its squared norm.
SELECT
count(*) AS rows,
count(DISTINCT place.external_id) AS unique_ids,
count(*) FILTER (WHERE place.is_active) AS active_rows,
min(sqrt(GREATEST(0.0, -(place.embedding <#> place.embedding)))) AS min_norm,
max(sqrt(GREATEST(0.0, -(place.embedding <#> place.embedding)))) AS max_norm
FROM public.place_embeddings_semantic_v1 AS place;
SELECT
place.embedding_model,
place.embedding_version,
place.metadata->>'semantic_document_version' AS document_version,
count(*) AS rows
FROM public.place_embeddings_semantic_v1 AS place
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;
-- Inspect this plan after a representative backfill. With enough rows, the
-- nearest-neighbor branch should use the HNSW index rather than materializing
-- the entire eligible corpus before ordering.
EXPLAIN (ANALYZE, BUFFERS, COSTS, VERBOSE)
SELECT place.external_id
FROM public.place_embeddings_semantic_v1 AS place
WHERE place.is_active = true
ORDER BY place.embedding <=> (
SELECT sample.embedding
FROM public.place_embeddings_semantic_v1 AS sample
LIMIT 1
)
LIMIT 20;
ROLLBACK;
|