>> >> 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. >> Hi Tom, I just stumbled across this question because I regularly come across problems that, at first, look like they should be solved with non-static LIKE or REGEX patterns I actually have two situations where I would need a better plan. One is, fortunately, fairly static (mostly lookups, hardly inserts) for name matches. Many famous people appear in different spellings, say these two musicians Franz|Ferenc Liszt Fr(e|y)der(ic|yk) Chopin So the first plan would be to regex-compare the sought name against the first name (or last name) regexes. Run-time is astronomical, though My current approach is to a) keep the regexes in a separate table/column, so names with a regex entry are handled in a smaller query b) reverse the query: for every regex (they are well-behaved in this context) I pre-create a pattern so that my actual query becomes where pre-made-pattern ~ searched_name c) while preparing the pattern, a common initial character (the "F" for Franz and Ferenc) is identified to build an index. In the rare case that the first letter is already different, there would be two entries in the table. So the actual query can check for first letter before it does the regex. The other situation, unfortionately, is ad-hoc queries where I cannot do that kind of preparation typically, the DB would contain strings like XY4711A, XY271, XY17321AAA, and I want to check whether an input like XY17321 matches a database entry up to the end of the numerals. So I add [^0-9]*$ to the end of my candidates, select where candidate ~ entry-in-table and go for a coffee or two Of course I would prefer to see a pre-built solution do all that mess for me... Regards Wolfgang Hamann -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general