hamann.w@xxxxxxxxxxx writes: > Tom Lane wrote: > If you want it to be bulletproof, what I'd think about is something like > WHERE second.path LIKE quote_like(first.path)||'%' > Just out of curiosity: wouldn't that (as well as using non-static like) > be an enormous performance problem? Well, it won't be free, but I think you've already doomed yourself to a not-very-bright plan by using LIKE in this way at all. In any case, as a wise man once said, you can make it run arbitrarily fast if it doesn't have to give the right answer. Correctness trumps any micro-optimization questions, so if you have to have prefix matching of this sort, it's gonna cost ya somehow. Actually, if the only case you're worried about is prefix match, you could do it in substring style: WHERE second.path = substring(first.path, 1, length(second.path)) (better double-check the substring syntax, I'm too lazy to). This is still going to completely suck on a macro level: there's still no way to perform the join except by tediously iterating through every combination of rows. But it'll likely outrun any LIKE-based solution by some percentage. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
