On Sat, Jun 27, 2015 at 5:17 PM, Jeff Janes <jeff.ja...@gmail.com> wrote: > This patch implements version 1.2 of contrib module pg_trgm. > > This supports the triconsistent function, introduced in version 9.4 of the > server, to make it faster to implement indexed queries where some keys are > common and some are rare. > > I've included the paths to both upgrade and downgrade between 1.1 and 1.2, > although after doing so you must close and restart the session before you > can be sure the change has taken effect. There is no change to the on-disk > index structure > > This shows the difference it can make in some cases: > > create extension pg_trgm version "1.1"; > > create table foo as select > > md5(random()::text)|| case when random()<0.000005 then 'lmnop' else '123' > end || > > md5(random()::text) as bar > > from generate_series(1,10000000); > > create index on foo using gin (bar gin_trgm_ops); > > --some queries > > alter extension pg_trgm update to "1.2"; > > --close, reopen, more queries > > > select count(*) from foo where bar like '%12344321lmnabcddd%'; > > > > V1.1: Time: 1743.691 ms --- after repeated execution to warm the cache > > V1.2: Time: 2.839 ms --- after repeated execution to warm the cache
Wow! I'm going to test this. I have some data sets for which trigram searching isn't really practical...if the search string touches trigrams with a lot of duplication the algorithm can have trouble beating brute force searches. trigram searching is important: it's the only way currently to search string encoded structures for partial strings quickly. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
