Feature #21815
Updated by Brett Smith 29 days ago
Inspired by #21737 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): <pre>CREATE CREATE INDEX collections_trgm_text_search_idx ON public.collections USING gin (((((((((((((((((((COALESCE(owner_uuid, ''::character varying))::text (((((((((((((((((((COALESCE)::text || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) (COALESCE)::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) (COALESCE)::text) || ' '::text) || (COALESCE(portable_data_hash, ''::character varying))::text) (COALESCE)::text) || ' '::text) || (COALESCE(uuid, ''::character varying))::text) (COALESCE)::text) || ' '::text) || (COALESCE(name, ''::character varying))::text) (COALESCE)::text) || ' '::text) || (COALESCE(de scription, ''::character varying))::text) (COALESCE)::text) || ' '::text) || COALESCE((properties)::text, COALESCE::text, ''::text)) || ' '::text) || COALESCE(file_names, ''::text))) gin_trgm_ops)</pre> 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. Task: # exclude @portable_data_hash@ and any field ending @_uuid@ from @full_text_searchable_columns@ # add a migration that recreates the trigram indexes for each table with the new @full_text_coalesce@