PostgreSQL RDS索引陷阱 - nanit

1. RDS 卷效能透過每秒 IO 運算元 (IOPS) 來衡量：基準效能確定為以 GiB x 3 為單位的卷大小，例如，500 GiB 卷的基準效能為 1500 IOPS。當基準效能不足時，卷可能會消耗 IO 積分。只要卷效能低於基線，積分就會以每 GiB 3 IOPS 的速率累積。這意味著 500 GiB 的卷以 1500 IOPS 的速率累積積分。
2. 需要注意的是，低於 1 TiB 的卷在 30 分鐘內使用積分的限制為 3000 IOPS。
3. 突發餘額是可用積分的百分比。

解釋分析
為了找出查詢執行緩慢的原因，我們使用了EXPLAIN ANALYZE命令。EXPLAIN命令顯示查詢的執行計劃而不實際執行它。
使用 ANALYZE 選項執行查詢，結果包括實際執行時間。
在慢查詢上執行EXPLAIN ANALYZE的結果。我們發現索引掃描需要 10 秒（準確地說是 10444.278），它返回 9019 行，然後由資料庫在記憶體中排序。如果我們只需要一行，從索引掃描返回 9019 行意味著大量冗餘讀取 IOPS，我們發現這可能是我們在看到的 IOPS 激增的原因。
 

索引使用
接下來是瞭解查詢計劃器為何使用此索引以及我們如何減少查詢時間。
下一件事是瞭解為什麼查詢計劃器使用這個索引，以及我們如何能減少查詢時間。我們對索引的結構很感興趣，看看它是否符合查詢的結構（選定的列、where子句、限制），所以我們在psql客戶端執行了\d命令，該命令返回一個表的所有索引及其結構。現在我們有了索引的結構，但令人驚訝的是，列表中包括另一個索引，其結構包括查詢中使用的確切欄位和標準。我們的問題是為什麼查詢計劃器沒有使用正確的索引？我們接下來做的事情是透過使用下面的查詢來看看是否使用了適當的索引。

SELECT
    pg_t.tablename,
    pg_size_pretty(pg_relation_size(pg_c.oid)) AS table_size,
    pg_sai.indexrelname AS index_name,
    pg_sai.idx_scan AS number_of_scans,
    pg_sai.idx_tup_read AS tuples_read,
    pg_sai.idx_tup_fetch AS tuples_fetched
FROM
    pg_tables pg_t
    LEFT JOIN pg_class pg_c ON pg_t.tablename = pg_c.relname
    LEFT JOIN pg_index pg_i ON pg_c.oid = pg_i.indrelid
    LEFT JOIN pg_stat_all_indexes pg_sai ON pg_i.indexrelid = pg_sai.indexrelid
WHERE
    pg_t.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 
    pg_t.tablename, 
    table_size;

索引的刪除
接下來我們做的事情是使用DROP INDEX CONCURRENTLY查詢來刪除這個不太最佳化的索引。我們使用CONCURRENTLY選項來避免鎖定表。一旦索引被刪除，查詢計劃器就開始使用適當的索引。
，現在的總執行時間是1ms。此外，索引的使用也從索引掃描變成了只掃描索引。索引掃描從索引和表的堆中獲取行，而僅索引掃描則直接從索引的資料中返回行，而不訪問表的堆。
但最重要的是，只用索引掃描只返回1條記錄，而使用以前的索引則返回9019條記錄。
現在，查詢計劃員開始使用適當的索引，讀取IOPS下降，收件箱完全恢復。
 

總結
一段索引誤區讓我們開始了一段旅程，從前端應用程式開始，訪問了後端程式碼，最後在資料庫本身結束。我們從中學到了很多東西，特別是在除錯計劃器查詢執行輸出和索引使用方面。