Regexps - never completing join.

Hi Guys,

I'm using postgresql 8.3.1 and I'm seeing weird behavior between what
I expect and what's happening when the query is executed

I'm trying to match a table that contains regexps against another
table that is full of the text to match against so my query is:

select wc_rule.id from classifications, wc_rule where
classifications.classification ~* wc_rule.regexp;

When I run that the query takes a very very long time (never ending so
far 20 minutes or so) to execute.

But if I loop through all of the rules and a query for each rule:

select wc_rule.id from classifications, wc_rule where
classifications.classification ~* wc_rule.regexp and wc_rule.id = ?

All of the rules when run individually can be matched in a little
under then 3 minutes. I'd assume postgres would be equal to or faster
with the single row execution method.

The table schema:

CREATE TABLE wc_rule (
id integer NOT NULL,
regexp text,
);

CREATE TABLE classifications (
id integer NOT NULL,
classification text NOT NULL
);

gb_render_1_db=# explain select wc_rule.id from classifications,
wc_rule where classifications.classification ~* wc_rule.regexp;
QUERY PLAN
-----------------------------------------------------------------------------
Nested Loop (cost=13.71..891401.71 rows=197843 width=4)
Join Filter: (classifications.classification ~* wc_rule.regexp)
-> Seq Scan on classifications (cost=0.00..1093.46 rows=56446
width=42)
-> Materialize (cost=13.71..20.72 rows=701 width=22)
-> Seq Scan on wc_rule (cost=0.00..13.01 rows=701 width=22)
(5 rows)

gb_render_1_db=# select count(*) from classifications;
count
-------
56446
(1 row)

gb_render_1_db=# select count(*) from wc_rule;
count
-------
701
(1 row)

I have exports of the tables up at so you can try it if you'd like.

http://rusty.devel.infogears.com/regexp-tables.tar.bz2

Any insight is greatly appreciated, even if it's just showing me how I
made a mistake in the query.

Thanks,

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

An example script that shows how each rule was run individually in perl.

$dbh->begin_work();
eval {
my $all_rules = $dbh->selectall_arrayref("select id from wc_rule");
foreach my $row (@$all_rules) {
print "Doing rule: $row->[0]\n";
eval {
local $SIG{ALRM} = sub { die("Alarm") };
alarm(5);
my $results = $dbh->selectall_arrayref("select wc_rule.id from
classifications, wc_rule where classifications.classification ~*
wc_rule.regexp and wc_rule.id = ?", undef, $row->[0]);
alarm(0);
};
if ($@) {
alarm(0);
print "Got bad rule id of : $row->[0]\n";
exit(0);
}
alarm(0);
print "ok rule: $row->[0]\n";
}
};
if ($@) {
print "Failed to run rules:\n$(at)\n";
$dbh->rollback();
$dbh->disconnect();
exit(-1);
}

$dbh->commit();
$dbh->disconnect();
exit(0);