On Fri, Jun 21, 2013 at 5:39 PM, Erik Rijkers <e...@xs4all.nl> wrote:
> 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. > Revised version of patch with necessary comments. ------ With best regards, Alexander Korotkov.
trgm-regex-optimize.2.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
