RPC function always returns max 40 results

[nameisxi]

Hi, I've created an rpc function for similarity search, but no matter what match_threshold or limit value (as long as it's > 40) I use, I get a maximum of 40 results in a table of >27k rows. I tried to check the documentation for possible pagination, but couldn't find anything. I also checked that my max row limit is the default 1000, so that's not the issue either. I even tried dropping the match_count limit, to no avail.

My rpc function:

create or replace function my_function (
  query_embedding vector(1024),
  match_threshold float,
  match_count int
)
returns table (
...columns...
)
language sql stable
as $$
  select
    ...columns...
    1 - (my_table.embedding <=> query_embedding) as similarity
  from my_table
  where 1 - (my_table.embedding <=> query_embedding) > match_threshold
  order by (my_table.embedding <=> query_embedding) asc
  limit match_count;
$$;
SET statement_timeout TO '10min';

My index:
CREATE INDEX my_table_embedding_idx ON public.my_table USING hnsw (embedding vector_cosine_ops)

My JS code:

const { data, error } = await supabaseClient.rpc(
    `my_function`,
    {
        query_embedding: embedding,
        match_threshold: 0.7,
        match_count: 100,
    }
);

console.log('Data:: ', data); // returns 40 results

When I run this query, I get >25k results from the count:

SELECT COUNT(*)
FROM companies
WHERE 1 - (embedding <=> 'my_embedding'::vector(1024)) > 0.7;

and when I run this query both from psql & SQL editor on the web app, I still get 40 results:

SELECT COUNT(*) FROM my_function(
  'my_embedding'::vector(1024),
  0.7,
  1000000
);

Any help would be much appreciated!

[GaryAustin1]

RLS would be my first guess... except for your last test implies you ran your function also from the SQL editor which would not get impacted by RLS for the default postgres user role. There is no pagination.

If you change your function to just do the hard coded select does it work?

[sebastian-weisshaar]

I had the same issue, maybe its already fixed but hnsw.ef_search has a default value of 40. Increasing this to a higher number will return more values. The max is 1000 after which we switched to IVFFlat indexes.