postgresql测试题_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,t::text from generate_series(1,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 INDEX

d=# create index t1_name on t1 (name varchar_pattern_ops)

;

CREATE INDEX

d=# 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 ~

(4 rows)

d=# set enable_bitmapscan = off;

SET

d=# 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 ~

Filter: (name ~~ '888%'::text)

(3 rows)