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
