015.PGSQL-索引

索引大法好

1.需要使用的列：

经常查询的列

排序的列

连接的列

where条件的列

经常出现order by 、group by 、distinct的列

注意：定期删除无用的索引，减少数据操作的负荷

 

2.索引的创建

唯一索引

create index index_name_id  on dbscheme.test0001(name_id);

多字段索引
create index indexs_name_traff on dbscheme.test0001(name_id,traffic);

部分索引

create index index_par_name_id  on dbscheme.test0001(name_id) where name_id = 'kk';

表达式索引

select trim(nameid) from dbscheme.test0001 where trim(nameid) = 'll'
create index index_name_id  on  dbscheme.test0001(trim(name_id)) ; 

创建指定的btree索引

create index index_name_id  on dbscheme.test0001 using btree (name_id);

 修改索引名称

 alter index  index_name_id rename to  index_name_id_new

删除索引

DROP INDEX index_name_id_new;

 

查看表的索引信息

select
A.SCHEMANAME,
A.TABLENAME,
A.INDEXNAME,
A.TABLESPACE,
A.INDEXDEF,
B.AMNAME,
C.INDEXRELID,
C.INDNATTS,
C.INDISUNIQUE,
C.INDISPRIMARY,
C.INDISCLUSTERED,
D.DESCRIPTION
from
PG_AM B left join PG_CLASS F on
B.OID = F.RELAM left join PG_STAT_ALL_INDEXES E on
F.OID = E.INDEXRELID left join PG_INDEX C on
E.INDEXRELID = C.INDEXRELID left outer join PG_DESCRIPTION D on
C.INDEXRELID = D.OBJOID,
PG_INDEXES A
where
A.SCHEMANAME = E.SCHEMANAME
and A.TABLENAME = E.RELNAME
and A.INDEXNAME = E.INDEXRELNAME
and E.SCHEMANAME = 'dbscheme'
and E.RELNAME = 'test0001';