Обсуждение: [GENERAL] Seq scan X Index scan
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
Why is SEQ SCAN faster than index scan?
This is an environment t
est but i'm running the same test on a production environment and also seq scan is cheaper than index.