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