At 05:51 PM 2/3/03 -0500, Tom Lane wrote:
>wade <wade@wavefire.com> writes:
>> Here is the profile information.  I included a log of the session that
>> generated it at the top of the gprof  output.  If there is any other info I
>> can help you with, please let me know.
>
>A four-second test isn't long enough to gather any statistically
>meaningful profile info.  On most machines, gprof samples 100 times per
>second, so realistically you need a minute or two of runtime to have
>trustworthy numbers.
>
>Please replicate the rows in the table by a factor of ten or twenty or
>so and try again.
>
>			regards, tom lane
OK, here goes again.
I have tried a different table, this one with 27444 rows.
In this case, the query with the regex of the form "row ~* 'regex'"
runs in 1.113 seconds and the other runs in 600.
The session idled for a while after the query completed if that makes a
difference.
Queries and explain output are included at the top of the gprof output.
http://arch.wavefire.com/pgregexgmon.txt
  -Wade Klaver


Mon, 03 Feb 2003 16:37:28 -0800
wade <wade@wavefire.com>
Re: POSIX regex performance bug in 7.3 Vs. 7.2 
Sigh.  It seems that somebody broke caching of compiled regexes,
so that your regex is recompiled each time it's used.  I haven't
dug into the logic yet, but I think it must have been a mistake
in Thomas' change to make the regex cache be searched circularly:

2002-06-14 22:49  thomas

	* src/backend/utils/adt/regexp.c: Search the existing regular
	expression cache as a ring buffer.  Will optimize the case for
	repeated calls for the same expression,  which seems to be the most
	common case. Formerly, always searched	from the first entry.  May
	want to look at the least-recently-used algorithm to make sure it 
	is identifying the right slots to reclaim. Seems silly to do math
	when  it seems that we could simply use an incrementing counter...

Considering that we now know that this is a factor-of-150 performance
hit, I wonder if this is a "must fix" for 7.3.2?  We already wrapped
the tarball, but ...

			regards, tom lane

Mon, 03 Feb 2003 20:07:03 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: POSIX regex performance bug in 7.3 Vs. 7.2 
Next question: may I guess that you weren't using MULTIBYTE in 7.2?

After still more digging, I'm coming round to the opinion that the
problem is that MULTIBYTE is forced on in 7.3, and this imposes a
factor-of-256 overhead in a bunch of the operations in regcomp.c.
In particular, compiling a case-independent regex is now hugely
more expensive than it used to be.

The parties who wanted to force MULTIBYTE on promised that there 
would be no such penalties :-(

			regards, tom lane

Mon, 03 Feb 2003 23:08:14 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: POSIX regex performance bug in 7.3 Vs. 7.2 