官方wiki： @H_404_0@ https://wiki.postgresql.org/wiki/Using_EXPLAIN @H_404_0@ https://wiki.postgresql.org/wiki/Performance_Optimization @H_404_0@ @H_404_0@ （1）通过统计信息pg_catalog查看当前运行状况 @H_404_0@①数据库大小 @H_404_0@

SELECT pg_size_pretty(pg_database_size('data_base_name'));

@H_404_0@ @H_404_0@各数据库大小： @H_404_0@

SELECT d.datname AS Name,pg_catalog.pg_get_userbyid(d.datdba) AS Owner,CASE WHEN pg_catalog.has_database_privilege(d.datname,'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS SIZE
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname,'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC
    LIMIT 20

@H_404_0@ @H_404_0@②表大小 @H_404_0@

SELECT pg_size_pretty(pg_relation_size('table_name'));

@H_404_0@ @H_404_0@前20的大表（包含index和toast）： @H_404_0@

SELECT nspname || '.' || relname AS "relation",pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog','information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 20;

@H_404_0@ @H_404_0@各表大小： @H_404_0@

SELECT *,pg_size_pretty(total_bytes) AS total,pg_size_pretty(index_bytes) AS INDEX,pg_size_pretty(toast_bytes) AS toast,pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *,total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema,relname AS TABLE_NAME,c.reltuples AS row_estimate,pg_total_relation_size(c.oid) AS total_bytes,pg_indexes_size(c.oid) AS index_bytes,pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a;

@H_404_0@ @H_404_0@各表数据行数: @H_404_0@

SELECT
    relname as table_name,reltuples as rowcounts
FROM
    pg_class
WHERE
    relkind = 'r'
ORDER BY
    rowCounts desc;

@H_404_0@ @H_404_0@③当前链接数 @H_404_0@

SELECT count(*) FROM pg_stat_activity;

@H_404_0@ @H_404_0@④整体缓存命中率（不低于99%） @H_404_0@

SELECT
    sum(heap_blks_read) as heap_read,sum(heap_blks_hit)  as heap_hit,sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM
    pg_statio_user_tables;

@H_404_0@ @H_404_0@⑤整体索引缓存命中率（不低于99%） @H_404_0@

SELECT
    sum(idx_blks_read) as idx_read,sum(idx_blks_hit)  as idx_hit,(sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM
    pg_statio_user_indexes;

@H_404_0@ @H_404_0@⑥IO次数/缓存命中率 @H_404_0@

SELECT
    *,(heap_blks_hit*100) / (heap_blks_read+heap_blks_hit) as ratio
FROM
    pg_statio_all_tables
WHERE
    heap_blks_hit >= 1 AND
    schemaname = 'public'
ORDER BY
    ratio;

@H_404_0@ @H_404_0@⑦频繁被访问的表 @H_404_0@

SELECT
    relname,coalesce(seq_tup_read,0)+coalesce(idx_tup_fetch,0)+coalesce(n_tup_ins,0)+coalesce(n_tup_upd,0)+coalesce(n_tup_del,0) as total,0) as select,coalesce(n_tup_ins,0) as insert,coalesce(n_tup_upd,0) as update,coalesce(n_tup_del,0) as delete
FROM
    pg_stat_user_tables
ORDER BY
    total desc;

@H_404_0@ @H_404_0@⑧索引的使用（「idx_scan」「idx_tup_read」「inx_tup_fetch」为0的索引就没有必要） @H_404_0@

SELECT  * FROM pg_stat_user_indexes;

@H_404_0@ @H_404_0@index的详细信息： @H_404_0@

SELECT
    t.tablename,indexname,c.reltuples AS num_rows,pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,idx_scan AS number_of_scans,idx_tup_read AS tuples_read,idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename,ipg.relname AS indexname,x.indnatts AS number_of_columns,idx_scan,idx_tup_read,idx_tup_fetch,indexrelname,indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;

@H_404_0@ @H_404_0@⑨清除所有统计信息 @H_404_0@

SELECT pg_stat_reset();

@H_404_0@ @H_404_0@ （2）配置信息postgresql.conf @H_404_0@①连接 @H_404_0@max_connections 最大链接数 @H_404_0@ @H_404_0@②内存 @H_404_0@shared_buffers 不要少于物理内存的1/4 @H_404_0@work_mem 每个进程可以使用的工作内存空间，数据库的排序操作和哈希表使用的内存缓冲区的大小，如何work_mem指定的内存被耗尽，数据库将使用磁盘文件进行完成操作，速度会慢很多。 @H_404_0@maintenance_work_mem VACUUM或者REINDEX处理可以使用的内存空间。 @H_404_0@ @H_404_0@③WAL @H_404_0@max_fsm_pages FSM最大页数 @H_404_0@wal_sync_method 向磁盘强制更新 WAL 数据的方法 @H_404_0@checkpoint_segments 定量，写入大小。一个是16MB，默认是3，所以就是48MB。到了这个量就会调用写入处理。 @H_404_0@checkpoint_timeout 定时，写入间隔。默认是5min（五分种）。过了5分钟就调入写入处理。 @H_404_0@ @H_404_0@④查询 @H_404_0@effective_cache_size 最大缓存 @H_404_0@random_page_cost @H_404_0@ @H_404_0@⑤日志 @H_404_0@log_min_duration_statement = 3s 会将执行时间超过3秒的sql文输出到Postgresql的Log @H_404_0@ @H_404_0@⑥垃圾清理 @H_404_0@autovacuum = on @H_404_0@ @H_404_0@***具体数据应该设置成多少，应该根据服务器的配置多次尝试后设置。 @H_404_0@ @H_404_0@ （3）其他 @H_404_0@ @H_404_0@①VACUUM @H_404_0@postgresql中用于维护数据库磁盘空间的工具是VACUUM，其重要的作用是删除那些已经标示为删除的数据并释放空间。 @H_404_0@ @H_404_0@②vacuumdb @H_404_0@vacuumdb是VACUUM 的封装，用户切换到postgres用户，在postgres\bin下执行： @H_404_0@ @H_404_0@ @H_404_0@常用参数 @H_404_0@-a/--all vacuum所有的数据库 @H_404_0@-d dbname 只vacuum dbname这个数据库 @H_404_0@-f/--full 执行full的vacuum @H_404_0@-t table 只vacuum table这个数据表 @H_404_0@-z/--analyze 生成用于查询优化器的内部统计数据 @H_404_0@ @H_404_0@Postgresql的更新处理是先DELETE后INSERT，所以大量UPDATE会生成垃圾，所以每天执行一回。 @H_404_0@ @H_404_0@ @H_404_0@③sql语句执行计划分析 @H_404_0@ @H_404_0@ @H_404_0@④重建索引REINDEX @H_404_0@ @H_404_0@ @H_404_0@⑤定期做Dump和Restore @H_404_0@参考： PostgreSQL常用命令 @H_404_0@ @H_404_0@Postgresql Benchmarking Toolset ： pgbench-tools @H_404_0@Postgresql configuration wizard ： pgtune