postgresql通过索引优化查询速度

当数据量比较大的时候，提升查询效率就是需要去考虑的事情了。一个百万级别的表格，如果不做任何优化的话，即使是最简单的查询语句执行起来也是慢的让人难以接受；当然“优化”本身是一个比较复杂的工程，从设计表、字段到查询语句的写法都有很多讲究，这里只考虑索引的方式，且是最普通的索引；

下面的操作中对应数据库表w008_execrise_info（8000数据量）， w008_wf02_info（4000数据量）

1 任务表数据

SELECT
	w.*
FROM
	w008_wf02_info w 
WHERE
	w.is_removed =0
	AND w.wfno = 'WF02' 
	AND EXISTS (
SELECT
	1 
FROM
	w008_execrise_info info
	JOIN w008_privilege_allocation P ON (
	info.subjecttyp = P.subjecttyp 
	AND info.gradetyp = P.gradetyp 
	AND P.loginname = w.create_by 
	AND P.verifyusers ='yixi_li'
	AND P.wftype = 20 
	) 
WHERE
	info.is_removed =0 
	AND info.wfid = w.wfid 
	) 

ORDER BY
	create_date DESC

执行结果：耗时在3秒左右（这个数据量，这个速度就比较慢了）

顺带说一句，这里把w.* 改成对应的字段也会稍微的提高一些查询速度（毕竟少了一步把*转成对应字段的操作），在标准的查询规范中也不会写成table.*这种方式的。

2 添加索引：虽说索引可以提高查询速度，但是不代表加了索引就一定会加快查询速度，有时甚至会适得其反。一般来说索引会加在where 后面的查询字段，尤其是关联字段上面，这里w008_execrise_info 表数据量最大，暂时针对这个表进行处理；w008_execrise_info 表涉及四个字段：subjecttyp，gradetyp，wfid和is_removed。其中is_removed先不考虑，subjecttyp和gradetyp保存的是字典表的数据（数据内容比较少，类似10 20 30 40），wfid保存的是数字类型的字符串。一个索引起到的效果还取决于这样一个条件，一般来说添加索引的字段的值"唯一性"越明显越好，在这里，subjecttyp和gradetyp包含大量的重复值，索引效果会“不明显”一些，而wfid 更像是“主键”，相对应的效果会好很多。

2.1 subjecttyp 和gradetyp添加索引

CREATE INDEX w008_execrise_info_gradetyp_index ON w008_execrise_info (gradetyp);

CREATE INDEX w008_execrise_info_subjecttyp_index ON w008_execrise_info (subjecttyp);

添加后执行时间大约2秒，快了一点点

2.2 对wfid添加索引

CREATE INDEX w008_execrise_info_wfid_index ON w008_execrise_info (wfid);

再次执行，0.2秒左右，快了很多

再补充一句，有很多情况下索引是不起作用的，比如 like后面跟的字段，还有条件语句or关联的字段，这种情况就是要考虑查询策略了。

3 查看当前表的索引内容；

select * from pg_indexes where tablename='w008_execrise_info';     

select * from pg_statio_all_indexes where relname='w008_execrise_info';

4 删除索引

DROP INDEX indexName;  

5 重置索引

对于一些经常改动的表，如果时间长了发现查询效率变慢，可以考虑重置一下索引；

因为如果表的内容被频繁的修改的话会产生许多类似'索引碎片'的东西，会导致查询索引本身的时间变长；

 REINDEX INDEX index_name;//重置单个索引
 REINDEX TABLE table_name;//重置整个表的索引
 REINDEX DATABASE db_name;//终止整个数据库的所以你