I found you committed GiST index implementation. That's cool. I found an easy way to optimize it. We can also use trigramsMatchGraph for signatures. Attached patch contains implementation. Simple example in order to demonstrate it:
Before the patch:
test=# explain (analyze, buffers) select * from words where s ~ '[abc]def';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on words (cost=4.36..40.24 rows=10 width=9) (actual
time=17.189..17.193 rows=3 loops=1)
Recheck Cond: (s ~ '[abc]def'::text)
Buffers: shared hit=858
-> Bitmap Index Scan on words_trgm_idx (cost=0.00..4.36 rows=10
width=0) (actual time=17.172..17.172 rows=3 loops=1)
Index Cond: (s ~ '[abc]def'::text)
Buffers: shared hit=*857*
Total runtime: 17.224 ms
(7 rows)
After the patch:
test=# explain (analyze, buffers) select * from words where s ~ '[abc]def';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on words (cost=4.36..40.24 rows=10 width=9) (actual
time=13.718..13.721 rows=3 loops=1)
Recheck Cond: (s ~ '[abc]def'::text)
Buffers: shared hit=498
-> Bitmap Index Scan on words_trgm_idx (cost=0.00..4.36 rows=10
width=0) (actual time=13.701..13.701 rows=3 loops=1)
Index Cond: (s ~ '[abc]def'::text)
Buffers: shared hit=*497*
Total runtime: 13.786 ms
(7 rows)
------
With best regards,
Alexander Korotkov.
trgm-regexp-gist-optimize.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
