Re: Regexps - never completing join.

["Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>]

On Wed, May 14, 2008 at 9:33 AM, Rusty Conover <rconover(at)infogears(dot)com> wrote:
> Returning to this problem this morning, I made some more insight.
>
> One way I did find that worked to control the loop (but doesn't yield the
> same results because its a left join)
>
> select wc_rule.id from wc_rule left join classifications on
> classifications.classification ~* wc_rule.regexp;

If you do that and exclude the extra rows added to the right with somthing like

and wc_rule.somefield IS NOT NULL

does it run fast and give you the same answers as the regular join?

I'm guessing that this could be optimized to use a hash agg method of
joining for text, but I'm no expert on the subject.