Re: trgm regex index peculiarity

On Fri, Jun 21, 2013 at 5:39 PM, Erik Rijkers <er(at)xs4all(dot)nl> wrote:

> On Fri, June 21, 2013 15:11, Alexander Korotkov wrote:
> > On Fri, Jun 21, 2013 at 2:40 PM, Erik Rijkers <er(at)xs4all(dot)nl> wrote:
> >
> >> On Fri, June 21, 2013 05:25, Tom Lane wrote:
> >> > "Erik Rijkers" <er(at)xs4all(dot)nl> writes:
> >> >> In a 112 MB test table (containing random generated text) with a trgm
> >> index (gin_trgm_ops), I consistently get these
> >> >> timings:
> >> >> select txt from azjunk6 where txt ~ '^abcd';
> >> >> 130 ms
> >> >> select txt from azjunk6
> >> >> where txt ~ 'abcd' and substr(txt,1,4) = 'abcd';
> >> >> 3 ms
> >> >
> >
> > Regex '^abcd' will be expanded into trigrams '__a', '_ab', 'abc' and
> 'bcd'.
> > However trigrams '__a' is much more frequent than '_ab' which in turn is
> > much more frequent than 'abc' and 'bcd'. Ommiting of ^ leads to ommiting
> of
> > '__a' and '_ab' and that gives so significant speedup.
>
> > [trgm_regex_optimize.1.patch ]
>
> Yes, that fixes the problem, thanks.
>

Revised version of patch with necessary comments.

------
With best regards,
Alexander Korotkov.