On Fri, June 21, 2013 15:11, Alexander Korotkov wrote: > On Fri, Jun 21, 2013 at 2:40 PM, Erik Rijkers <e...@xs4all.nl> wrote: > >> On Fri, June 21, 2013 05:25, Tom Lane wrote: >> > "Erik Rijkers" <e...@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.
> [trgm_regex_optimize.1.patch ] Yes, that fixes the problem, thanks. Erik Rijkers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
