On May 13, 2008, at 11:45 PM, Rusty Conover wrote:
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)
As a followup I did some digging:
by editing:
src/backend/utils/adt/regexp.c
and increasing the cache size for regular expressions to an
arbitrarily large number
#define MAX_CACHED_RES 3200
Rather then the default of
#define MAX_CACHED_RES 32
I was able to get the query to complete in a respectable amount of time:
gb_render_1_db=# explain analyze 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) (actual
time=72.714..366899.913 rows=55052 loops=1)
Join Filter: (classifications.classification ~* wc_rule.regexp)
-> Seq Scan on classifications (cost=0.00..1093.46 rows=56446
width=42) (actual time=28.820..109.895 rows=56446 loops=1)
-> Materialize (cost=13.71..20.72 rows=701 width=22) (actual
time=0.000..0.193 rows=701 loops=56446)
-> Seq Scan on wc_rule (cost=0.00..13.01 rows=701
width=22) (actual time=0.030..0.593 rows=701 loops=1)
Total runtime: 366916.632 ms
(6 rows)
Which is still > 6 minutes, but at least it completed.
I'll keep digging into what is causing this bad performance.
Thanks,
Rusty
--
Rusty Conover
InfoGears Inc.
http://www.infogears.com
