Arvados

reformatted

https://api.arkau.roche.com/arvados/v1/groups/contents?
limit=50&offset=0&include_trash=false&include_old_versions=false
&filters=[[%22any%22,%22ilike%22,%22%258458829%25%22],[%22uuid%22,%22is_a%22,[%22arvados%23collection%22]],
[%22uuid%22,%22is_a%22,[%22arvados%23group%22,%22arvados%23collection%22]],
[%22collections.properties.type%22,%22not+in%22,[%22log%22,%22intermediate%22]]]
&order=collections.modified_at+desc,container_requests.modified_at+desc,groups.modified_at+desc,container_requests.created_at+desc

As discussed at today's meeting, I believe this is a limitation of the search index we are using (pg_trgm)

The trigram index works by breaking the search string into 3-character substrings (trigrams) and looking those up in an index. That yields a set of candidate rows that are scanned for exact substring match.

I believe the problem with searches involving only strings of numeric characters is that a 6 digit identifier is broken into two 3-digit trigams, and those individual trigrams are likely to show up a large number of times, resulting in a large number of false positives (0.1% of the table is a lot when there are millions of rows).

If you are able to run an "explain analyze" on the query, that would be extremely helpful in confirming this theory.

This is what the full search indexes on (the operation is building a string with all the fields separated by spaces and then indexing on that):

CREATE INDEX collections_trgm_text_search_idx ON public.collections USING gin (((((((((((((((((((COALESCE)::text || ' '::text) || (COALESCE)::text) || ' '::text) || (COALESCE)::text) || ' '::text) || (COALESCE)::text) || ' '::text) || (COALESCE)::text) || ' '::text) || (COALESCE)::text) || ' '::text) || (COALESCE)::text) || ' '::text) || COALESCE::text, ''::text)) || ' '::text) || COALESCE)) public.gin_trgm_ops);

Looking at this, I think it would be much better if all uuid fields and the portable data hash were excluded.

The reasoning is that uuids and the PDH are a string of random alphanumeric characters, generating a lot of trigrams which become potential matches, but not actual matches.

container_requests_trgm_text_search_idx has the same problem, in addition the "mounts" field probably needs to be excluded because it is likely to contain a bunch of portable data hashes as well. (For provenance, we could separately index mounts in a way that makes it easy to find input collections).