查看执行计划可以使用explain命令。如下
mytest=> select * from test;
id | name
----+------------
1 | bai
2 | xiao
(2 行记录)
mytest=> explain select * from test;
QUERY PLAN
---------------------------------------------------------
Seq Scan on test (cost=0.00..20.40 rows=1040 width=48)
(1 行记录)
括号中的显示值的解析(从左到右)
1启动的估计花费。这个是输出开始前的时间,例如排序的时间
2总花费的估计
3输出行的估计
4输出行的平均值的估计
使用explain analyze来显示真是的执行计划,会实际执行语句,返回行和运行时间。explain有一个buffers选项来提供更多的运行时信息,buffers来帮助标识哪部分是最消耗i/o的。
explain (analyze true,buffers true)select * from test;
"Seq Scan on test (cost=0.00..19.14 rows=314 width=219) (actual time=0.021..0.146 rows=314 loops=1)"
" Buffers: shared hit=16"
"Planning time: 0.059 ms"
"Execution time: 0.186 ms"
如果不想修改数据,也要看到实际的执行计划,按下面的方式使用
begin:
explain analyze update ....;
rollback;
查看表和索引的行和占用的磁盘块数量
select relnam,relkind,reltuples,relpages from pg_class where relname like 'tenk1%';
上面的值可能是旧的,要使用vacuum,analyze来更新。analyze是手工更新统计信息
analyze test(relname);更新test上的relname的统计信息
analyze test;更新test表全部列的统计信息
select * from pg_stats where tablename='test';
analyze对很大的表的统计信息收集的也只是一部分的数据,这样就有个问题,大表的统计信息可能是不准确的,需要手工的指定这些指标的值,比如唯一值得个数,有个指标是设置抽取多少的数据来进行统计的,default_statistics_target
查看统计信息可以查看pg_stats例如
select attname,inherited,n_distinct,array_to_string(most_common_vals,E'\n') as most_common_vals from pg_stats where tablename='road';
操作数据库
当第一次操作数据库的时候,可能需要插入大量的数据,这部分是建议如何操作来加快这个步骤
1禁用自动提交
在大量的插入操作中关掉自动提交。在插入完的最后提交一次。
2使用copy,因为copy是一个命令,所以没有必要关闭自动提交
3移除索引
4移除外键约束
5增大maintenance_work_mem
6增大checkpoint_segments
临时增大该值也会加快数据加载的速度,因为在加载大量数据到PostgreSQL中的时候会引起检查点频繁发生。当检查点发生的时候,所有的脏页必须要刷新到磁盘上,通过增加checkpoint_segments,检查点的数量会减少。
7禁止wal归档和流复制
为了在加载的时候阻止增大的wal日志,禁用归档和流复制,设置下面的参数wal_level=minimal,archive_mode=off,max_wal_senders=zero修改这些参数需要重启
8事后运行analyze