主頁 > 知識庫 > postgresql查看表和索引的情況,判斷是否膨脹的操作

postgresql查看表和索引的情況,判斷是否膨脹的操作

熱門標簽:電銷機器人能補救房產(chǎn)中介嗎 天津開發(fā)區(qū)地圖標注app 電話機器人怎么換人工座席 400電話申請客服 廣州電銷機器人公司招聘 江蘇400電話辦理官方 地圖標注要花多少錢 移動外呼系統(tǒng)模擬題 濟南外呼網(wǎng)絡(luò)電話線路

索引膨脹的幾個來源:

1 大量刪除發(fā)生后,導(dǎo)致索引頁面稀疏,降低了索引使用效率。

2 PostgresQL 9.0之前的版本,vacuum full 會同樣導(dǎo)致索引頁面稀疏。

3 長時間運行的事務(wù),禁止vacuum對表的清理工作,因而導(dǎo)致頁面稀疏狀態(tài)一直保持。

查看重復(fù)索引

SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE,
  (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2,
  (array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4
FROM (
 SELECT indexrelid::regclass AS idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'||
           COALESCE(indexprs::text,'')||E'\n' || COALESCE(indpred::text,'')) AS KEY
 FROM pg_index) sub
GROUP BY KEY HAVING COUNT(*)>1
ORDER BY SUM(pg_relation_size(idx)) DESC;

表的大小和表中索引個數(shù)

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;

獲取每個表的行數(shù),索引和一些關(guān)于這些索引的信息(比較詳細)

SELECT
 pg_class.relname,
 pg_size_pretty(pg_class.reltuples::BIGINT) AS rows_in_bytes,
 pg_class.reltuples AS num_rows,
 COUNT(indexname) AS number_of_indexes,
 CASE WHEN x.is_unique = 1 THEN 'Y'
  ELSE 'N'
 END AS UNIQUE,
 SUM(CASE WHEN number_of_columns = 1 THEN 1
    ELSE 0
   END) AS single_column,
 SUM(CASE WHEN number_of_columns IS NULL THEN 0
    WHEN number_of_columns = 1 THEN 0
    ELSE 1
   END) AS multi_column
FROM pg_namespace 
LEFT OUTER JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
LEFT OUTER JOIN
  (SELECT indrelid,
   MAX(CAST(indisunique AS INTEGER)) AS is_unique
  FROM pg_index
  GROUP BY indrelid) x
  ON pg_class.oid = x.indrelid
LEFT OUTER JOIN
 ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns FROM pg_index x
   JOIN pg_class c ON c.oid = x.indrelid
   JOIN pg_class ipg ON ipg.oid = x.indexrelid )
 AS foo
 ON pg_class.relname = foo.ctablename
WHERE 
  pg_namespace.nspname='public'
AND pg_class.relkind = 'r'
GROUP BY pg_class.relname, pg_class.reltuples, x.is_unique
ORDER BY 2;

補充:postgresql查看表膨脹

查看表膨脹(對所有表產(chǎn)進行膨脹率排序)

SQL文如下:

SELECT
 schemaname||'.'||relname as table_name,
 pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as table_size,
 n_dead_tup,
 n_live_tup,
 round(n_dead_tup * 100 / (n_live_tup + n_dead_tup),2) AS dead_tup_ratio
FROM
 pg_stat_all_tables
WHERE
 n_dead_tup >= 1000
ORDER BY dead_tup_ratio DESC
LIMIT 10;

以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方,望不吝賜教。

您可能感興趣的文章:
  • PostgreSQL之INDEX 索引詳解
  • PostgreSql 重建索引的操作
  • PostgreSQL模糊匹配走索引的操作
  • PostgreSQL的B-tree索引用法詳解
  • postgresql通過索引優(yōu)化查詢速度操作
  • postgresql 索引之 hash的使用詳解

標簽:海西 濮陽 寶雞 杭州 辛集 溫州 昭通 榆林

巨人網(wǎng)絡(luò)通訊聲明:本文標題《postgresql查看表和索引的情況,判斷是否膨脹的操作》,本文關(guān)鍵詞  postgresql,查,看表,和,索引,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《postgresql查看表和索引的情況,判斷是否膨脹的操作》相關(guān)的同類信息!
  • 本頁收集關(guān)于postgresql查看表和索引的情況,判斷是否膨脹的操作的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章