Basically I've created this on my test environment:
create table test_gin_index ( name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING, name_last CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING );
insert into test_gin_index VALUES ('jhon','backer'); insert into test_gin_index VALUES ('paul','min'); insert into test_gin_index VALUES ('emily','foo');
CREATE EXTENSION pg_trgm; create index on test_gin_index using gin (name_first gin_trgm_ops);
analyze test_gin_index;
Explain analyze with SEQ scans:
explain analyze select * from test_gin_index where name_first ILIKE '%on%';
-> Bitmap Index Scan on test_gin_index_name_first_idx (cost=0.00..92.00 rows=1 width=0) (actual time=0.010..0.010 rows=3 loops=1)
Index Cond: ((name_first)::text ~~* '%on%'::text)
Planning time: 0.122 ms
Execution time: 0.042 ms
(8 rows)
Why is SEQ SCAN faster than index scan? This is an environment test but i'm running the same test on a production environment and also seq scan is cheaper than index.