CREATE TABLE IF NOT EXISTS tab_vacuum_record
(sqltext text);

CREATE OR REPLACE FUNCTION f_vacuum_tables()
RETURNS void AS
$FUNCTION$
 DECLARE
 v_tablename text;
 v_dead_cond bigint;
 v_sql    text;
 cur_tablename REFCURSOR;
 v_vacuum_record text;
 BEGIN
 v_vacuum_record := 'tab_vacuum_record';
 OPEN cur_tablename FOR SELECT tablename FROM pg_tables WHERE tablename !~ '^pg|^sql';
 LOOP
  FETCH cur_tablename INTO v_tablename;
   SELECT n_dead_tup INTO v_dead_cond FROM pg_stat_user_tables WHERE relname = v_tablename;
     IF v_dead_cond > 0 THEN
      v_sql := 'INSERT INTO ' || v_vacuum_record || ' VALUES(' || chr(39) ||'VACUUM FULL ' || v_tablename ||';'|| chr(39) ||')';
    EXECUTE v_sql;
     END IF;
   EXIT WHEN NOT FOUND;
 END LOOP;
 CLOSE cur_tablename;
 END;
$FUNCTION$
LANGUAGE PLPGSQL;

#!/bin/bash
#獲取環(huán)境變量
CURRDIR=$(cd "$(dirname $0)";pwd)
TOPDIR=$(cd $CURRDIR/..;pwd)
CONFIG=$TOPDIR/conf/host.ini
CT_FILE=${TOPDIR}/sql/CREATE_VACCUM_TABLE_RECORD.sql
CT_FUNCTION=${TOPDIR}/sql/CHECK_NEEDS_VACUUM_TABLE_FUNCTION.sql
source $CONFIG
CONNINFO="psql -U $USER -d $DBNAME -h $HOSTADDR -p $PORT"
function check_status()
{
    echo "檢查數(shù)據(jù)庫服務(wù)器狀態(tài)是否正常 !"
    stat=`$CONNINFO -Aqt -c 'SELECT 1'`
    if [ "${stat}" == "1" ];then
        echo "服務(wù)器連接正常"
    else
        echo "服務(wù)器連接異常,退出"
        exit -1;
    fi
}
function create_table()
{
    echo "創(chuàng)建收集需要vacuum的表"
    $CONNINFO -f $CT_FILE
}
function create_function()
{
    echo "創(chuàng)建收集需要 vacuum 表的函數(shù)"
    $CONNINFO -f $CT_FUNCTION
}
check_status
create_table
create_function

postgres=# SELECT * FROM f_vacuum_tables();
 f_vacuum_tables 
-----------------
 
(1 row)
--創(chuàng)建測(cè)試表
postgres=# CREATE TABLE tab_test(id int);
--插入數(shù)據(jù)
postgres=# INSERT INTO tab_test SELECT id FROM generate_series(1,100000) as id;
INSERT 0 100000
--刪除數(shù)據(jù)
postgres=# DELETE FROM tab_Test WHERE id <= 10000;
DELETE 10002
postgres=# SELECT * FROM tab_vacuum_record ;
    sqltext    
-----------------------
 VACUUM FULL tab_test;
(1 row)

temp_file_limit = -1 #默認(rèn)-1表示不限制每個(gè)進(jìn)程可使用的最大臨時(shí)文件限制，單位kb
#max_files_per_process = 1000 #每個(gè)子進(jìn)程允許同時(shí)打開文件的最大數(shù)量

vacuum_cost_limit = 200 
vacuum_cost_delay = 0 # 單位微秒，默認(rèn)為 0 沒有開啟

vacuum_cost_page_hit = 1 # 代表從緩存池查找共享的hash table并掃描 該`頁`的內(nèi)容
             #的估計(jì)值
vacuum_cost_page_miss = 10   # 0-10000 credits
vacuum_cost_page_dirty = 20

default_statistics_target = 100 #提高查詢的 析計(jì)劃