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 > > Hm, could you provide a self-contained test case? >
yes, sorry. I tested on a 1M row table: #!/bin/sh # create table: for power in 6; do table=azjunk${power} index=${table}_trgm_re_idx perl -E' sub ss{ join"",@_[ map{rand @_} 1 .. shift ] }; say(ss(80,"a".."g"," ","h".."m"," ","n".."s"," ","t".."z")) for 1 .. 1e'"${power};" \ | psql -aqXc " drop table if exists $table; create table $table(txt text); copy $table from stdin;"; echo "set session maintenance_work_mem='1GB'; create index $index on $table using gin (txt gin_trgm_ops); analyze $table;" | psql -qtAX; done # test: echo " \\timing on explain analyze select txt from azjunk6 where txt ~ '^abcd'; -- slow (140 ms) explain analyze select txt from azjunk6 where txt ~ 'abcd' and substr(txt,1,4) = 'abcd'; -- fast (5 ms) " | psql -Xqa -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers