postgresql 索引状态_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;//终止整个数据库的所以你

补充：PostgreSql查询优化之根据执行计划优化SQL

1、执行计划路径选择

postgresql查询规划过程中，查询请求的不同执行方案是通过建立不同的路径来表达的，在生成许多符合条件的路径之后，要从中选择出代价最小的路径(基于成本运算)，把它转化为一个计划，传递给执行器执行，规划器的核心工作就是生成多条路径，然后从中找出最优的那一条。

1.1代价评估

评估路径优劣的依据是用系统表pg_statistic中的统计信息估算出来的不同路径的代价(cost)，PostgreSQL估计计划成本的方式：基于统计信息估计计划中各个节点的成本。PostgreSQL会分析各个表来获取一个统计信息样本(这个操作通常是由autovacuum这个守护进程周期性的执行analyze，来收集这些统计信息，然后保存到pg_statistic和pg_class里面)。

1.2用于估算代价的参数postgresql.conf

# - Planner Cost Constants -

#seq_page_cost = 1.0 # measured on an arbitrary scale 顺序磁盘扫描时单个页面的开销

#random_page_cost = 4.0 # same scale as above 随机磁盘访问时单页面的读取开销

#cpu_tuple_cost = 0.01 # same scale as above cpu处理每一行的开销

#cpu_index_tuple_cost = 0.005 # same scale as above cpu处理每个索引行的开销

#cpu_operator_cost = 0.0025 # same scale as above cpu处理每个运算符或者函数调用的开销

#parallel_tuple_cost = 0.1 # same scale as above 计算并行处理的成本，如果成本高于非并行，则不会开启并行处理。

#parallel_setup_cost = 1000.0 # same scale as above

#min_parallel_relation_size = 8MB

#effective_cache_size = 4GB 再一次索引扫描中可用的文件系统内核缓冲区有效大小

也可以使用 show all的方式查看

1.3 路径的选择

--查看表