hamann.w@t-online.de 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