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;

查看表的索引信息

selectA.SCHEMANAME,
A.TABLENAME,
A.INDEXNAME,
A.TABLESPACE,
A.INDEXDEF,
B.AMNAME,
C.INDEXRELID,
C.INDNATTS,
C.INDISUNIQUE,
C.INDISPRIMARY,
C.INDISCLUSTERED,
D.DESCRIPTION
fromPG_AM B left join PG_CLASS F onB.OID = F.RELAM left join PG_STAT_ALL_INDEXES E onF.OID = E.INDEXRELID left join PG_INDEX C onE.INDEXRELID = C.INDEXRELID left outer join PG_DESCRIPTION D onC.INDEXRELID =D.OBJOID,
PG_INDEXES A
whereA.SCHEMANAME =E.SCHEMANAME
and A.TABLENAME =E.RELNAME
and A.INDEXNAME =E.INDEXRELNAME
and E.SCHEMANAME = 'dbscheme'
and E.RELNAME = 'test0001';