79258575

Date: 2024-12-06 15:44:47
Score: 3.5
Natty:
Report link

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:

Reasons:
  • Blacklisted phrase (1.5): any solution
  • Blacklisted phrase (1): is there any
  • RegEx Blacklisted phrase (1): I want
  • RegEx Blacklisted phrase (2): any solution?
  • Probably link only (1):
  • Long answer (-0.5):
  • Has code block (-0.5):
  • High reputation (-2):
Posted by: Ciro Santilli OurBigBook.com