Skip site navigation (1) Skip section navigation (2)

Re: Regexps - never completing join.

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: "Rusty Conover" <rconover(at)infogears(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Regexps - never completing join.
Date: 2008-05-16 20:35:24
Message-ID: dcc563d10805161335m73a938e0t4b1a6578b2d19999@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.

In response to

Responses

pgsql-performance by date

Next:From: Alvaro HerreraDate: 2008-05-16 21:00:50
Subject: Re: I/O on select count(*)
Previous:From: Alvaro HerreraDate: 2008-05-16 20:15:28
Subject: Re: I/O on select count(*)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group