Re: Using POSIX Regular Expressions on xml type fields gives inconsistent results

> >> 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.