PostgreSQL执行计划

查看执行计划可以使用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