Indexing very similar character varying field in postgres I have a table with a column &quot;name&quot;, 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 &quot;count&quot; 
from &quot;upload_file&quot; 
where (&quot;upload_file&quot;.&quot;name&quot;::text ILIKE '%some_string%' 
       or &quot;upload_file&quot;.&quot;id&quot;::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).
What a terrible query! Consider using different software.
The only thing that could help are trigram indexes on both columns:
CREATE EXTENSION pg_trgm;

CREATE INDEX ON upload_file USING gin ((name::text) gin_trgm_ops);
CREATE INDEX ON upload_file USING gin ((id::text) gin_trgm_ops);

Then you might get two bitmap index scans and a &quot;bitmap or&quot;.
If not, the condition is probably not selective enough, and a sequential scan is the most efficient way to handle it.