Hello,
  We recently upgraded a project from 7.2 to 7.3.1 to make use of some of
the cool new features in 7.3.  The installed version is CVS stable from
yesterday.  However, we noticed a major performance hit in POSIX regular
expression matches against columns using the ~* operator.  
http://arch.wavefire.com/badregex73.txt has explain analyze output from 7.2
and 7.3.1 respectively.  Both of these tables have only 101 rows.  The
7.3.1 install is using the default settings from postgresql.conf.

  Any ideas as to why this should be happening?

  Should anyone require additional information, please let me know.

-Wade Klaver

Fri, 31 Jan 2003 14:37:00 -0800
wade <wade@wavefire.com>
POSIX regex performance bug in 7.3 Vs. 7.2
wade <wade@wavefire.com> writes:
>   We recently upgraded a project from 7.2 to 7.3.1 to make use of some of
> the cool new features in 7.3.  The installed version is CVS stable from
> yesterday.  However, we noticed a major performance hit in POSIX regular
> expression matches against columns using the ~* operator.  

The only thought that comes to mind is that multibyte character sets are
supported in 7.3 whereas they were optional (and not default) in 7.2.
I'd not have expected a factor-of-150 performance hit from that, though.

Could you rebuild your backend with profiling enabled and get a gprof
summary of where the time is going?

			regards, tom lane

Fri, 31 Jan 2003 22:52:53 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: POSIX regex performance bug in 7.3 Vs. 7.2 
Why on earth are you using a CVS version!?!?!?!

Chris

On Fri, 31 Jan 2003, wade wrote:

> Hello,
>   We recently upgraded a project from 7.2 to 7.3.1 to make use of some of
> the cool new features in 7.3.  The installed version is CVS stable from
> yesterday.  However, we noticed a major performance hit in POSIX regular
> expression matches against columns using the ~* operator.
> http://arch.wavefire.com/badregex73.txt has explain analyze output from 7.2
> and 7.3.1 respectively.  Both of these tables have only 101 rows.  The
> 7.3.1 install is using the default settings from postgresql.conf.
>
>   Any ideas as to why this should be happening?
>
>   Should anyone require additional information, please let me know.
>
> -Wade Klaver
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>


Sat, 1 Feb 2003 20:31:05 +0800 (WST)
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: POSIX regex performance bug in 7.3 Vs. 7.2
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Why on earth are you using a CVS version!?!?!?!

I assume he meant tip of REL7_3 branch --- which is a perfectly
reasonable thing to install, even if there are still a few fixes
to go before we call it 7.3.2.

			regards, tom lane

Sat, 01 Feb 2003 11:41:32 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: POSIX regex performance bug in 7.3 Vs. 7.2 