I have a table with a column "name", the table is describing a media asset and the name is a character varying field containing the file name. The table is generated and used by a CMS (Strapi) and I can't really tweak how the columns are used nor the SQL being executed. What I'm hoping to do is slap on an index (or two) and get a bit better performance.
The file names of our files are very similar, pretty much XYZ12345-Q2.png, where XYZ is the same for about 80% of the files. So what I'm wondering is what kind of index (if any) would help speeding up a query such as:
select count(*) as "count" from "upload_file" where ("upload_file"."name"::text ILIKE '%some_string%' or "upload_file"."id"::text ILIKE '%some_string%'
The id is the primary key, and it's an auto incrementing positive integer.
My concern regarding the actual string is that an index won't do much when the file names are so similar? Or would it actually make a difference? In that case what would be the best index type to use? My understanding of Gin is that it wouldn't really suit this case because there are no words (none of the file names contain space).