postgresql 关于索引的简单测试

postgres=# create database c enCoding 'sql_ascii' template template0 LC_collate='C' LC_ctype='C';
CREATE DATABASE
postgres=# \c c
You are Now connected to database "c" as user "postgres".

c=# create table t1(ID int,name text);
CREATE table
c=# insert into t1 select t::int,t::text from generate_serIEs(1,1000000) as t;
INSERT 0 1000000
c=# create index t1_name on t1(name);
CREATE INDEX
c=# vacuum ANALYZE t1;
VACUUM
c=#
c=#
c=# explain select * from t1 where name like '888%';
query PLAN
------------------------------------------------------------------------
Index Scan using t1_name on t1 (cost=0.42..1540.99 rows=100 wIDth=10)
Index Cond: ((name >= '888'::text) AND (name < '889'::text))
Filter: (name ~~ '888%'::text)
(3 rows)

c=# explain select * from t1 where name like '%888';
query PLAN
---------------------------------------------------------
Seq Scan on t1 (cost=0.00..17905.00 rows=100 wIDth=10)
Filter: (name ~~ '%888'::text)
(2 rows)

c=# create database d enCoding 'sql_ascii' template template0 LC_collate='zh_CN.UTF8' LC_ctype='zh_CN.UTF8';
CREATE DATABASE
c=#
c=#
c=# \c d
You are Now connected to database "d" as user "postgres".
d=# create table t1(ID int,name text);
CREATE table
d=# insert into t1 select t::int,1000000) as t;
INSERT 0 1000000
d=# create index t1_name on t1(name);
CREATE INDEX
d=# vacuum ANALYZE t1 ;
VACUUM
d=# explain select * from t1 where name like '888%';
query PLAN
---------------------------------------------------------
Seq Scan on t1 (cost=0.00..17905.00 rows=100 wIDth=10)
Filter: (name ~~ '888%'::text)
(2 rows)

用opclass建立索引，告诉数据库这列上要走索引，对于生产环境中有些情况很有效率

d=# drop index t1_name ;DROP INDEXd=# create index t1_name on t1 (name varchar_pattern_ops);CREATE INDEXd=# explain select * from t1 where name like '888%';query PLAN --------------------------------------------------------------------------Bitmap Heap Scan on t1 (cost=22.80..2630.32 rows=100 wIDth=10)Filter: (name ~~ '888%'::text)-> Bitmap Index Scan on t1_name (cost=0.00..22.78 rows=1035 wIDth=0)Index Cond: ((name ~>=~ '888'::text) AND (name ~<~ '889'::text))(4 rows)d=# set enable_bitmapscan = off;SETd=# explain select * from t1 where name like '888%';query PLAN ------------------------------------------------------------------------Index Scan using t1_name on t1 (cost=0.42..3815.71 rows=100 wIDth=10)Index Cond: ((name ~>=~ '888'::text) AND (name ~<~ '889'::text))Filter: (name ~~ '888%'::text)(3 rows)