0

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).

7
  • 3
    Does this answer your question? Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL – bbaird Feb 24 at 17:23
  • The id is the primary key, and it's an auto incrementing positive integer Then what's the intention behind or "upload_file"."id"::text ILIKE '%some_string%'? Doesn't seem to make sense. – Erwin Brandstetter Feb 24 at 19:32
  • XYZ12345-Q2.png, where XYZ is the same for about 80% of the files. is "XYZ" actually much longer than the (significant) rest of the string? What about the other 20%? Does some_string only match at the end or also on "XYZ"? – Erwin Brandstetter Feb 24 at 19:35
  • @ErwinBrandstetter The SQL generated by this CMS are generally horrible and I can't do much about that, I've managed to change one query where it was defaulting to ILIKE matching every string and numeric field in tables but that was a merge request and Strapi is written in React which isn't my strong suit. I agree that it makes no sense – Jon Feb 25 at 9:01
  • The problematic CMS shouldn't keep you from answering the other questions. – Erwin Brandstetter Feb 25 at 12:49
2

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 "bitmap or".

If not, the condition is probably not selective enough, and a sequential scan is the most efficient way to handle it.

Not the answer you're looking for? Browse other questions tagged or ask your own question.