From: Scott Marlowe <scott.marlowe@gmail.com>
To: Rusty Conover <rconover@infogears.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Regexps - never completing join.
Date: Fri, 16 May 2008 15:44:26 -0600
Message-ID: <dcc563d10805161444u2101ac4bj7aa3a60a1841f065@mail.gmail.com> (raw)
In-Reply-To: <F1524B41-DC3D-4E61-B4ED-C2D16318C0E5@infogears.com>
References: <FE4C9EB4-6599-460E-82D3-D040C93F202D@infogears.com>
<DEA15B6C-2C0B-4139-964E-2BC6FFFA0F12@infogears.com>
<8103338D-08B9-43A9-A2DC-C7CEDC0994AB@infogears.com>
<dcc563d10805161335m73a938e0t4b1a6578b2d19999@mail.gmail.com>
<F1524B41-DC3D-4E61-B4ED-C2D16318C0E5@infogears.com>
On Fri, May 16, 2008 at 3:37 PM, Rusty Conover <rconover@infogears.com> wrote:
>
> On May 16, 2008, at 2:35 PM, Scott Marlowe wrote:
>
>> On Wed, May 14, 2008 at 9:33 AM, Rusty Conover <rconover@infogears.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.
That's not what I meant. What I meant was it seems like a good
candidate for a hash aggregate solution. I'm pretty sure pgsql can't
use hashagg for something like this right now.
If you hashagged each regexp and each column fed through it, you could
probably get good performance. but that's a backend hacker thing, not
something I'd know how to do.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scott.marlowe@gmail.com, rconover@infogears.com
Subject: Re: Regexps - never completing join.
In-Reply-To: <dcc563d10805161444u2101ac4bj7aa3a60a1841f065@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox