Assuming it only loops once in the query yes, that's 5 seconds. But (and I'm guessing here) having it in the where directly leads to something like a n+1 issue

Assuming it only loops once in the query yes, that's 5 seconds. But (and I'm guessing here) having it in the where directly leads to something like a n+1 issue for full tablescan/non indexed queries as a right or non anchored i/like quickly leads to without the propper index. What I'm meaning to say is that, the issue is not solved, it is simply worked around by narrowing down the set queried, not really postgres magic but usage of own data knowhow. I believe a Trigram index whould solve the issue at its root.