lab-indexes query options

[ains-arch]

I think you said in lab that your implementation used like, lower, and reverse. The query I came up with that used those is

SELECT * FROM accounts WHERE reverse(lower(name)) LIKE reverse(lower('%management'));

but I couldn't figure out what index to create that that query would actually use. I also heard you say we shouldn't use a trigram index unless we could explain how it worked...

Anyway I ended up with select statement that uses a sub query

SELECT *
FROM (
    SELECT *, substring(name from '\S+$') AS last_part
    FROM accounts
) AS subquery
WHERE lower(last_part) = 'management';

and an index for the substring part

CREATE INDEX ON accounts ((lower(substring(name from '\S+$'))));

which does lead the query to use a bitmap heap scan and a bitmap index scan.

Bitmap Heap Scan on accounts  (cost=4.82..198.67 rows=69 width=313)
  Recheck Cond: (lower("substring"(name, '\S+$'::text)) = 'management'::text)
  ->  Bitmap Index Scan on accounts_lower_idx1  (cost=0.00..4.80 rows=69 width=0)
        Index Cond: (lower("substring"(name, '\S+$'::text)) = 'management'::text)

I doubt this was the intended solution. Should I keep fiddling until I find an index that works with the original reverse(lower(name)) query? Or was that query not the intended solution and I should find something else? Did anyone else solve it this way (or with my original query)? Thanks.