The search query uses the LIKE operator which requires a sequential scan (it can't use the already existing B-tree index). This new index (trigram) avoids a sequential scan of the builds table when the LIKE operator is used.
Here is the analyze of a request on the builds table with this index:
explain analyze select * from builds where drvpath like '%k3r71gz0gv16ld8rhcp2bb8gb5w1xc4b%';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on builds (cost=128.00..132.01 rows=1 width=492) (actual time=0.070..0.077 rows=1 loops=1)
Recheck Cond: (drvpath ~~ '%k3r71gz0gv16ld8rhcp2bb8gb5w1xc4b%'::text)
-> Bitmap Index Scan on indextrgmbuildsondrvpath (cost=0.00..128.00 rows=1 width=0) (actual time=0.047..0.047 rows=3 loops=1)
Index Cond: (drvpath ~~ '%k3r71gz0gv16ld8rhcp2bb8gb5w1xc4b%'::text)
Total runtime: 0.206 ms
(5 rows)
6COLDXKSOIZXICAUY2MUKSW6ZKXLV6MBIJ3ENAU74SUQDRFGPPEQC
#ifdef POSTGRESQL
-- Provide an index used by LIKE operator on builds.drvpath (search query)
CREATE EXTENSION pg_trgm;
CREATE INDEX IndexTrgmBuildsOnDrvpath ON builds USING gin (drvpath gin_trgm_ops);
#endif
CREATE EXTENSION pg_trgm;
CREATE INDEX IndexTrgmBuildsOnDrvpath ON builds USING gin (drvpath gin_trgm_ops);