> >> There's a much easier fix to this problem though, which is to invent > >> a "reverse ~" operator that does POSIX comparison with the pattern on > >> the left. The hardest part of doing that for yourself is choosing a > >> name for the reverse operator --- it just goes like > >> > >> create function reverse_regexeq(text, text) returns bool as 'select > >> $2 ~ $1' language sql strict immutable; > >> > >> create operator ~~~ (procedure = reverse_regexeq, leftarg = text, > >> rightarg = text); > >> > >> and similarly for the case-insensitive version, and there you go: > >> pattern ~~~ ANY (whatever) solves the problem. > >> > > Hi Tom, > > while it might work to reverse the order in a regex, I have experienced > severe slowdown when the pattern comes from table data > > Regards > Wolfgang Hamann
You at least have to provide a query that you feel is "too slow". Since you claim a "slowdown" you should also provide a query the better performing query you are referring to. For all queries provided you also need to provide comparable timing information. It is also unclear whether you experience this slowdown when using the "reverse" expression only or if it occurs in any situation where the expression comes from a table column rather than a string constant. If it is indeed related to the custom reverse query the fact that you are going through an SQL wrapper to access a c-level function is inevitably going to degrade performance. In short you need to provide much more detail and clarity regarding what exactly made you draw this conclusion so that others can repeat and verify and then provide meaningful explanations or solutions. If you have not done so please read the content at http://wiki.postgresql.org/wiki/Slow_Query_Questions and http://wiki.postgresql.org/wiki/Guide_to_reporting_problems David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
