收集索引和資料表使用統計

想像一下，你的資料庫就像一個超大的倉庫。索引就是目錄和清單，幫你快速找到要的東西。資料表就像貨架上的商品。如果索引沒被好好利用，就像目錄被丟在角落沒人翻。如果資料表很常被用，但結構爛或資料太多，會讓我們的倉庫（資料庫）很吃力，速度也會變慢。

分析的主要任務：

1. 評估索引使用效率。 比如說，你那個超貴的索引都沒人用？直接砍掉吧！
2. 判斷讀寫操作的頻率。 這可以幫你知道哪些資料表最常被用。
3. 查詢優化。 統計數據可以幫你找出哪裡可以加速資料處理，加或改索引都行。

View pg_stat_user_indexes 跟 pg_stat_user_tables

在 PostgreSQL 裡有兩個超實用的 view 可以收集統計：pg_stat_user_indexes 跟 pg_stat_user_tables。我們來細看一下。

pg_stat_user_indexes：索引怎麼被用？

主要欄位：

• relname — 這個索引屬於哪個資料表的名字。
• indexrelname — 索引的名字。
• idx_scan — 這個索引被用來查找的次數。
• idx_tup_read — 用索引讀到的資料列數。
• idx_tup_fetch — 實際回傳的資料列數（過濾後）。

查詢範例：

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS index_tuples_read, 
       idx_tup_fetch AS index_tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

這裡我們：

• 用索引被呼叫的次數（idx_scan）來排序，這樣可以看到哪些索引最熱門。
• 如果某個索引幾乎沒被用（idx_scan = 0），就要想想：這個索引真的有必要嗎？

實戰應用：

你部署了新版 app，加了一個新索引。用 pg_stat_user_indexes 就能檢查你的查詢到底有沒有用到新索引，還是 PostgreSQL 還是走舊路，完全無視你的優化神作。

pg_stat_user_tables：看資料表的狀況

主要欄位：

• relname — 資料表名稱。
• seq_scan — 資料表被 sequential scan（沒用索引）掃過幾次。
• seq_tup_read — sequential scan 回傳的資料列數。
• idx_scan — 這個資料表被索引掃描的次數。
• n_tup_ins — 插入的資料列數。
• n_tup_upd — 更新的資料列數。
• n_tup_del — 刪除的資料列數。

查詢範例：

SELECT relname AS table_name, 
       seq_scan AS sequential_scans, 
       idx_scan AS index_scans, 
       n_tup_ins AS rows_inserted, 
       n_tup_upd AS rows_updated, 
       n_tup_del AS rows_deleted
FROM pg_stat_user_tables
ORDER BY sequential_scans DESC;

這裡我們看到什麼？

• 如果某些資料表的 sequential scan（seq_scan）超多，代表可能該加個索引了。
• 插入、更新、刪除的次數可以讓你知道這個表的資料變動有多頻繁。

實戰應用： 你在處理 users 這個表，裡面存了所有用戶資料。用 pg_stat_user_tables 發現這個表的 sequential scan（seq_scan）爆表，這就是提醒你：該在常用欄位上加索引，查詢才會快！

範例：真實資料庫的索引和資料表分析

假設我們有一個資料庫，裡面有 orders（訂單）和 products（商品）兩個表。我們想知道這些表和索引用得好不好。

索引分析：

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS tuples_read, 
       idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY index_scans DESC;

你會看到 orders_customer_id_idx 這個索引被叫了五萬次，但 orders_date_idx 只被用過五次。也許 orders_date_idx 根本沒必要。

資料表分析：

SELECT relname AS table_name, 
       seq_scan AS sequential_scans, 
       seq_tup_read AS tuples_read, 
       idx_scan AS index_scans, 
       n_tup_ins AS rows_inserted, 
       n_tup_upd AS rows_updated, 
       n_tup_del AS rows_deleted
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'products')
ORDER BY seq_scan DESC;

products 這個表一直被 sequential scan，這就是提醒你：商品目錄缺索引啦！

常見錯誤和怎麼避免

新手最常掉的坑就是不看統計。例如你加了一個新索引，心想：「這下查詢一定飛快！」結果 PostgreSQL 根本沒用，因為統計沒自動更新。只要你對表做了大改動，記得手動跑一下 ANALYZE 指令來更新統計。

還有一個常見錯誤就是瘋狂加索引。記住，每個索引都會佔硬碟空間，還會拖慢 insert、update、delete。用 pg_stat_user_indexes 的統計來確認索引真的有被用，不要讓它們變成資料庫的廢物。

這些知識有什麼用？

實際開發：如果資料庫慢，第一步就是檢查資料表和索引的問題。

面試：問你怎麼優化索引，這是 SQL 面試的經典題。你能解釋 pg_stat_user_indexes 嗎？那你已經過一半了。

資料庫管理：監控是 DBA 的日常。沒有資料表和索引的統計，你根本沒辦法優化。