tencent/hy3 · d2_pgvector · rep 2

env | grep -i supabase; echo "----"; which psql psql; psql --version 2>/dev/null; which curl

SUPABASE_DB_PASSWORD=<redacted:SUPABASE_DB_PASSWORD>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=<redacted:SUPABASE_SERVICE_ROLE_KEY>
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=<redacted:SUPABASE_ANON_KEY>
----
/usr/bin/psql
/usr/bin/psql
psql (PostgreSQL) 18.4 (Debian 18.4-1.pgdg12+1)
/usr/bin/curl

psql "$SUPABASE_DB_URL" -c "\d public.documents" -c "SELECT id, title, embedding_json FROM public.documents ORDER BY id;"

                 Table "public.documents"
     Column     |  Type   | Collation | Nullable | Default 
----------------+---------+-----------+----------+---------
 id             | integer |           | not null | 
 title          | text    |           | not null | 
 embedding_json | jsonb   |           | not null | 
Indexes:
    "documents_pkey" PRIMARY KEY, btree (id)

 id |  title  |  embedding_json  
----+---------+------------------
  1 | alpha   | [10, 0.5, 0, 0]
  2 | bravo   | [0.9, 0.3, 0, 0]
  3 | delta   | [20, 60, 0, 0]
  4 | echo    | [0, 1, 0, 0]
  5 | foxtrot | [-5, 0.1, 0, 0]
  6 | golf    | [0.1, 0.1, 3, 4]
(6 rows)

psql "$SUPABASE_DB_URL" <<'SQL'
-- 1) Enable pgvector
CREATE EXTENSION IF NOT EXISTS vector;

-- 2) Add embedding column (vector(4)) populated from embedding_json
ALTER TABLE public.documents
  ADD COLUMN IF NOT EXISTS embedding vector(4);

UPDATE public.documents
SET embedding = (
  SELECT array_to_vector(array_agg(v::float8 ORDER BY i))
  FROM jsonb_array_elements_text(embedding_json) WITH ORDINALITY AS t(v, i)
);
SQL
echo "---- verify ----"
psql "$SUPABASE_DB_URL" -c "SELECT id, title, embedding FROM public.documents ORDER BY id;"

CREATE EXTENSION
ALTER TABLE
---- verify ----
 id |  title  | embedding 
----+---------+-----------
  1 | alpha   | 
  2 | bravo   | 
  3 | delta   | 
  4 | echo    | 
  5 | foxtrot | 
  6 | golf    | 
(6 rows)

ERROR:  function array_to_vector(double precision[]) does not exist
LINE 3:   SELECT array_to_vector(array_agg(v::float8 ORDER BY i))
                 ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

psql "$SUPABASE_DB_URL" -tAc "SELECT extversion FROM pg_extension WHERE extname='vector';"
echo "---- available vector functions ----"
psql "$SUPABASE_DB_URL" -tAc "SELECT proname||'('||pg_get_function_arguments(oid)||')' FROM pg_proc WHERE pronamespace='vector'::regnamespace ORDER BY 1;"

0.8.2
---- available vector functions ----

ERROR:  schema "vector" does not exist
LINE 1: ...guments(oid)||')' FROM pg_proc WHERE pronamespace='vector'::...
                                                             ^

psql "$SUPABASE_DB_URL" <<'SQL'
UPDATE public.documents
SET embedding = (
  SELECT ('[' || string_agg(v::text, ',' ORDER BY i) || ']')::vector
  FROM jsonb_array_elements_text(embedding_json) WITH ORDINALITY AS t(v, i)
);
SQL
echo "---- verify ----"
psql "$SUPABASE_DB_URL" -c "SELECT id, title, embedding FROM public.documents ORDER BY id;"

UPDATE 6
---- verify ----
 id |  title  |   embedding   
----+---------+---------------
  1 | alpha   | [10,0.5,0,0]
  2 | bravo   | [0.9,0.3,0,0]
  3 | delta   | [20,60,0,0]
  4 | echo    | [0,1,0,0]
  5 | foxtrot | [-5,0.1,0,0]
  6 | golf    | [0.1,0.1,3,4]
(6 rows)

psql "$SUPABASE_DB_URL" <<'SQL'
-- 3) HNSW index on embedding using cosine distance
CREATE INDEX IF NOT EXISTS documents_embedding_hnsw_idx
  ON public.documents
  USING hnsw (embedding vector_cosine_ops);

-- 4) match_documents SQL function
CREATE OR REPLACE FUNCTION public.match_documents(
  query_embedding vector(4),
  match_count int DEFAULT 5
)
RETURNS TABLE (id int, title text, distance float8)
LANGUAGE sql
STABLE
AS $$
  SELECT
    d.id,
    d.title,
    d.embedding <=> query_embedding AS distance
  FROM public.documents d
  ORDER BY d.embedding <=> query_embedding
  LIMIT match_count;
$$;

-- Grant execute to anon (and authenticated) so RPC works with anon key
GRANT EXECUTE ON FUNCTION public.match_documents(vector(4), int) TO anon, authenticated;
SQL
echo "---- index + function check ----"
psql "$SUPABASE_DB_URL" -c "\di+ public.documents_embedding_hnsw_idx"
psql "$SUPABASE_DB_URL" -c "\df public.match_documents"

CREATE INDEX
CREATE FUNCTION
GRANT
---- index + function check ----
                                                     List of indexes
 Schema |             Name             | Type  |  Owner   |   Table   | Persistence | Access method | Size  | Description 
--------+------------------------------+-------+----------+-----------+-------------+---------------+-------+-------------
 public | documents_embedding_hnsw_idx | index | postgres | documents | permanent   | hnsw          | 16 kB | 
(1 row)

                                                                 List of functions
 Schema |      Name       |                     Result data type                     |                  Argument data types                  | Type 
--------+-----------------+----------------------------------------------------------+-------------------------------------------------------+------
 public | match_documents | TABLE(id integer, title text, distance double precision) | query_embedding vector, match_count integer DEFAULT 5 | func
(1 row)

# Test the function directly via psql as postgres (and as anon role)
psql "$SUPABASE_DB_URL" -c "SET ROLE anon; SELECT * FROM public.match_documents('[20,60,0,0]'::vector, 3); RESET ROLE;"

SET
 id | title |      distance      
----+-------+--------------------
  3 | delta |                  0
  4 | echo  | 0.0513167019494862
  2 | bravo |  0.399999992052714
(3 rows)

RESET

BASE="https://<ref>.supabase.co"
ANON="$SUPABASE_ANON_KEY"

echo "==== Test 1: query [20,60,0,0] (should match delta first) ===="
curl -sS -X POST "$BASE/rest/v1/rpc/match_documents" \
  -H "apikey: $ANON" \
  -H "Authorization: Bearer $ANON" \
  -H "Content-Type: application/json" \
  -d '{"query_embedding":[20,60,0,0], "match_count":3}'
echo

echo "==== Test 2: query [0,1,0,0] (should match echo first) ===="
curl -sS -X POST "$BASE/rest/v1/rpc/match_documents" \
  -H "apikey: $ANON" \
  -H "Authorization: Bearer $ANON" \
  -H "Content-Type: application/json" \
  -d '{"query_embedding":[0,1,0,0], "match_count":3}'
echo

==== Test 1: query [20,60,0,0] (should match delta first) ====
[{"id":3,"title":"delta","distance":0}, 
 {"id":4,"title":"echo","distance":0.0513167019494862}, 
 {"id":2,"title":"bravo","distance":0.399999992052714}]
==== Test 2: query [0,1,0,0] (should match echo first) ====
[{"id":4,"title":"echo","distance":0}, 
 {"id":3,"title":"delta","distance":0.0513167019494862}, 
 {"id":2,"title":"bravo","distance":0.683772217228812}]

BASE="https://<ref>.supabase.co"
ANON="$SUPABASE_ANON_KEY"

echo "==== Test 3: default match_count (should return up to 5) ===="
curl -sS -X POST "$BASE/rest/v1/rpc/match_documents" \
  -H "apikey: $ANON" -H "Authorization: Bearer $ANON" -H "Content-Type: application/json" \
  -d '{"query_embedding":[0.1,0.1,3,4]}' | python3 -m json.tool
echo

echo "==== EXPLAIN: confirm HNSW index is used ===="
psql "$SUPABASE_DB_URL" -c "EXPLAIN (COSTS OFF) SELECT id, title, embedding <=> '[20,60,0,0]'::vector AS distance FROM public.documents ORDER BY embedding <=> '[20,60,0,0]'::vector LIMIT 3;"

==== Test 3: default match_count (should return up to 5) ====
[
    {
        "id": 6,
        "title": "golf",
        "distance": 0
    },
    {
        "id": 3,
        "title": "delta",
        "distance": 0.974711892170972
    },
    {
        "id": 2,
        "title": "bravo",
        "distance": 0.974711892401252
    },
    {
        "id": 1,
        "title": "alpha",
        "distance": 0.979034586516748
    },
    {
        "id": 4,
        "title": "echo",
        "distance": 0.98000799508818
    }
]

==== EXPLAIN: confirm HNSW index is used ====
                        QUERY PLAN                         
-----------------------------------------------------------
 Limit
   ->  Sort
         Sort Key: ((embedding <=> '[20,60,0,0]'::vector))
         ->  Seq Scan on documents
(4 rows)

psql "$SUPABASE_DB_URL" -tAc "SELECT indexrelname, idx_scan, indisvalid, indisready FROM pg_stat_user_indexes JOIN pg_index ON indexrelid=indexoid WHERE indexrelname='documents_embedding_hnsw_idx';"

ERROR:  column reference "indexrelid" is ambiguous
LINE 1: ...sready FROM pg_stat_user_indexes JOIN pg_index ON indexrelid...
                                                             ^

psql "$SUPABASE_DB_URL" -tAc "SELECT s.indexrelname, s.idx_scan, i.indisvalid, i.indisready FROM pg_stat_user_indexes s JOIN pg_index i ON i.indexrelid = s.indexrelid WHERE s.indexrelname='documents_embedding_hnsw_idx';"

documents_embedding_hnsw_idx|0|t|t