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)