On Fri, Jun 21, 2013 at 5:39 PM, Erik Rijkers <er@xs4all.nl> wrote:
On Fri, June 21, 2013 15:11, Alexander Korotkov wrote: > On Fri, Jun 21, 2013 at 2:40 PM, Erik Rijkers <er@xs4all.nl> wrote: > >> On Fri, June 21, 2013 05:25, Tom Lane wrote: >> > "Erik Rijkers" <er@xs4all.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.