Simultaneous prefix + suffix LIKE '%abc%'
can be sped up with gin
+ pg_trgm
Usage:
CREATE EXTENSION pg_trgm;
CREATE TABLE "mytable" ("col1" TEXT);
CREATE INDEX "mytable_col1_gin" ON "mytable" USING gin("col1" gin_trgm_ops);
EXPLAIN SELECT * FROM "mytable" WHERE "col1" LIKE '%abc%';
which produces:
QUERY PLAN
---------------------------------------------------------------------------
Bitmap Heap Scan on mytable (cost=15.10..104.10 rows=400 width=72)
Recheck Cond: (col1 ~~ '%abc%'::text)
-> Bitmap Index Scan on mytable_col1_gin (cost=0.00..15.00 rows=400 width=0)
Index Cond: (col1 ~~ '%abc%'::text)
which means that the query was sped up.
Notes:
abc
has three or more indices (thus trigram): I want to search for 2 characters, is there any solution? (trigram index only works for minimum 3 characters)