On Fri, March 28, 2014 09:31, Heikki Linnakangas wrote: > I went back and tried Erik's original test > (http://www.postgresql.org/message-id/dafad644f268ce1503e1b8b682aae38a.squir...@webmail.xs4all.nl). > With a fresh checkout from master, the difference between the slow and > fast queries is much less dramatic than Erik reported. The reason is > that Alexander's GIN "fast scan" patch is very effective on that query. > Erik reported that the '^abcd' query took 140ms, vs 5ms for 'abcd'. On > my laptop, the numbers with a fresh checkout are about 2.5 ms vs. 1 ms, > and with fast scan disabled (by modifying the source code), 40ms vs 1ms. > > So thanks to the fast scan patch, I don't think this patch is worth > pursuing anymore. Unless there are some other test case where this patch > helps, but the fast scan patch doesn't. >
for the same 2 statements of my original test: explain (analyze,buffers) select txt from azjunk6 where txt ~ '^abcd'; -- slow (140 ms) explain (analyze,buffers) select txt from azjunk6 where txt ~ 'abcd' and substr(txt,1,4) = 'abcd'; -- fast (5 ms) You mention (from HEAD, I suppose?) a difference of 2.5 ms vs. 1 ms. FWIW, for me the difference (from HEAD) remains quite a bit larger: for n in `seq 1 10`; do ./trgm_peculiarity.sh ; done | grep runtime Total runtime: 16.167 ms Total runtime: 2.188 ms Total runtime: 16.902 ms Total runtime: 2.203 ms Total runtime: 17.486 ms Total runtime: 2.201 ms Total runtime: 17.663 ms Total runtime: 2.441 ms Total runtime: 13.555 ms Total runtime: 2.204 ms Total runtime: 16.862 ms Total runtime: 2.225 ms Total runtime: 13.207 ms Total runtime: 2.550 ms Total runtime: 16.768 ms Total runtime: 2.172 ms Total runtime: 19.259 ms Total runtime: 2.180 ms Total runtime: 12.934 ms Total runtime: 2.198 ms That's a lot better than the original 140ms vs 5ms but your laptop's 2.5 ms vs. 1 ms is perhaps not representative. (for the full plans see below) Erik Rijkers ---------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on azjunk6 (cost=56.77..432.93 rows=100 width=81) (actual time=15.898..15.925 rows=2 loops=1) Recheck Cond: (txt ~ '^abcd'::text) Rows Removed by Index Recheck: 11 Heap Blocks: exact=13 Buffers: shared hit=105 -> Bitmap Index Scan on azjunk6_trgm_re_idx (cost=0.00..56.75 rows=100 width=0) (actual time=15.834..15.834 rows=13 loops=1) Index Cond: (txt ~ '^abcd'::text) Buffers: shared hit=92 Planning time: 3.304 ms Total runtime: 16.179 ms (10 rows) Time: 21.103 ms explain (analyze,buffers) select txt from azjunk6 where txt ~ 'abcd' and substr(txt,1,4) = 'abcd'; -- fast (5 ms) QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on azjunk6 (cost=28.75..405.40 rows=1 width=81) (actual time=1.681..2.164 rows=2 loops=1) Recheck Cond: (txt ~ 'abcd'::text) Rows Removed by Index Recheck: 11 Filter: (substr(txt, 1, 4) = 'abcd'::text) Rows Removed by Filter: 101 Heap Blocks: exact=113 Buffers: shared hit=120 -> Bitmap Index Scan on azjunk6_trgm_re_idx (cost=0.00..28.75 rows=100 width=0) (actual time=1.171..1.171 rows=114 loops=1) Index Cond: (txt ~ 'abcd'::text) Buffers: shared hit=7 Planning time: 0.516 ms Total runtime: 2.183 ms (12 rows) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers