Seq scan X Index scan

[Patrick B <patrickbakerbr@xxxxxxxxx>]

Hi all.

I'm testing GIN indexes on a wildcard search.

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%';

                                               QUERY PLAN                                                

---------------------------------------------------------------------------------------------------------

 Seq Scan on test_gin_index  (cost=0.00..1.04 rows=1 width=19) (actual time=0.009..0.012 rows=1 loops=1)

   Filter: ((name_first)::text ~~* '%on%'::text)

   Rows Removed by Filter: 2

 Planning time: 0.075 ms

 Execution time: 0.027 ms

(5 rows)

Explain analyze with INDEX scan:

explain analyze select * from test_gin_index where name_first ILIKE '%on%';

                                                              QUERY PLAN                                                               

---------------------------------------------------------------------------------------------------------------------------------------

 Bitmap Heap Scan on test_gin_index  (cost=92.00..96.02 rows=1 width=19) (actual time=0.020..0.022 rows=1 loops=1)

   Recheck Cond: ((name_first)::text ~~* '%on%'::text)

   Rows Removed by Index Recheck: 2

   Heap Blocks: exact=1

   ->  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.

Thanks

Patrick