PostgreSQL 表達式索引

一個索引列并不一定是底層表的一個列，也可以是從表的一列或多列計算而來的一個函數或者標量表達式。這種特性對于根據計算結果快速獲取表中內容是有用的。

例如，一種進行大小寫不敏感比較的常用方法是使用lower函數：

SELECT * FROM test1 WHERE lower(col1) = 'value';

這種查詢可以利用一個建立在lower(col1)函數結果之上的索引：

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

如果我們將該索引聲明為UNIQUE，它將阻止創(chuàng)建在col1值上只有大小寫不同的行。

另外一個例子，如果我們經常進行如下的查詢：

SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';

那么值得創(chuàng)建一個這樣的索引：

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

正如第二個例子所示，CREATE INDEX命令的語法通常要求在被索引的表達式周圍書寫圓括號。而如第一個例子所示，當表達式只是一個函數調用時可以省略掉圓括號。

索引表達式的維護代價較為昂貴，因為在每一個行被插入或更新時都得為它重新計算相應的表達式。然而，索引表達式在進行索引搜索時卻不需要重新計算，因為它們的結果已經被存儲在索引中了。在上面兩個例子中，系統(tǒng)將會發(fā)現查詢的條件是WHERE indexedcolumn = 'constant'，因此查詢的速度將等同于其他簡單索引查詢。因此，表達式索引對于檢索速度遠比插入和更新速度重要的情況非常有用。