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

Re: Regexps - never completing join.

From: Rusty Conover <rconover(at)infogears(dot)com>
To: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Regexps - never completing join.
Date: 2008-05-16 21:37:29
Message-ID: F1524B41-DC3D-4E61-B4ED-C2D16318C0E5@infogears.com (view raw or flat)
Thread:
Lists: pgsql-performance
On May 16, 2008, at 2:35 PM, Scott Marlowe wrote:

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

Hi Scott,

It's not really a hash agg problem really just a looping inside/ 
outside table selection problem.

The slowdown is really the compilation of the regexp repeatedly by  
RE_compile_and_cache() because the regexps are being run on the inside  
of the loop rather then the outside.  And since the regexp cache is  
only 32 items big, the every match is resulting in a recompilation of  
the regexp since I have about 700 regexps.

Thanks,

Rusty
--
Rusty Conover
InfoGears Inc.
http://www.infogears.com



In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2008-05-16 21:44:26
Subject: Re: Regexps - never completing join.
Previous:From: Alvaro HerreraDate: 2008-05-16 21:03:15
Subject: Re: Please ignore ...

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