35.13. 操作符優化信息 · PostgreSQL 中文文檔 9.3

# 35.13\. 操作符優化信息 PostgreSQL的操作符定義可以包括幾個可選的子句，這些子句告訴系統一些關于該操作符特性的有用信息。在可能的情況下，都應該提供這些子句，因為它們可能為使用這個操作符的查詢帶來可觀的速度提升。不過要注意如果你聲明了這些子句，就必須確保它們是正確的！對優化子句的錯誤使用將導致減慢查詢速度、微小的輸出錯誤、或者其它糟糕事情。如果你對這些事情不確定的話，可以總是忽略優化子句；唯一的后果就是查詢可能運行的慢一些。附加的優化子句可能在今后的PostgreSQL版本里增加。這里描述的都是9.3.1版本可以理解的。 ## 35.13.1\. `COMMUTATOR` 如果提供了`COMMUTATOR`子句，則命名一個操作符是被定義的操作符的交換符。如果有兩個操作符 A, B ，對于任何可能的輸入數值 x, y 都有(x A y)等于(y B x)，那么就說 A 是 B 的交換符，同樣 B 也是 A 的交換符。例如，操作符`<`和`>` 對于所使用的一定的數據類型通常都是對方的交換符，而操作符`+`通常是它自身的交換符。但是操作符`-`通常沒有交換符。交換操作符的左操作數與右操作數類型必須相同。所以PostgreSQL 所需要的只是一個交換符操作符的名稱用以查找該交換符，那也是`COMMUTATOR` 子句里所需要的唯一的東西。給那些會在索引和連接子句里面使用的操作符提供交換符是非常關鍵的，因為這樣就允許查詢優化器"移動"這樣的子句，形成所需要的不同的規劃類型的形式。比如，考慮一個有類似`tab1.x = tab2.y`的 WHERE 子句的查詢，這里`tab1.x`和`tab2.y`是用戶定義類型，并且假設`tab2.y`上面有索引。除非優化器知道如何在`tab2.y = tab1.x`周圍四處移動該子句，否則它不能生成索引掃描，因為索引掃描機制期望看到索引字段在給出的操作符左邊。PostgreSQL _不會_簡單地假設這是一個合法的轉換，`=`的創建者必須聲明這是有效的，方法是給這個操作符標記交換器信息。當你定義一個自交換的操作符時，你簡單的定義它就可以了。當你定義一對交換符操作符時，事情就有一點棘手：怎樣定義一個操作符的交換符指向另一個你還沒有定義的操作符呢? 對這個問題有兩個解決方法： * 一個方法是省略你定義的第一個操作符的`COMMUTATOR`子句，然后在第二個操作符的定義里提供一個。因為PostgreSQL知道交換操作符是成對出現的，所以當它看到第二個定義時它會自動折回并填充第一個定義里空缺的`COMMUTATOR`子句。 * 另一個更直接的方法是在兩個定義里面都包含`COMMUTATOR`子句。當PostgreSQL 處理第一個定義并意識到`COMMUTATOR`指向一個不存在的操作符時會在系統表里為該操作符記錄一個虛擬記錄。這個虛擬的記錄只有操作符名，左和右操作數類型以及結果類型是有效的，因為這些是到目前為止 PostgreSQL可以推導出來的東西。第一個操作符類記錄將和這個虛擬記錄連接。稍后，當你定義第二個操作符時，系統將用來自第二個操作符的信息更新該虛擬記錄。如果你試圖在虛擬操作符被填充之前使用它，你將只能收到一條錯誤信息。 ## 35.13.2\. `NEGATOR` 如果提供了`NEGATOR`子句，則命名一個操作符是被定義的操作符的否定符。如果有兩個都返回布爾變量的操作符 A 和 B ，對任何可能的輸入 x 和 y ，都有 (x A y) 等于 NOT(x B y)，那么說 A 是 B 的否定符。當然 B 也是 A 的否定符。例如，`<`和`>=` 對大多數數據類型是一對否定符。一個操作符不可能是它自身的否定符。不像交換符，一對單目操作符可以互為否定符；那就意味著對于所有的 x 都有 (A x) 等于 NOT(B x) ，或者類似的右目操作符的這種情況。一個操作符的否定符必須有與正定義的操作符本身一樣的左和/或右操作數類型，所以就像`COMMUTATOR`一樣，只有操作符名需要在`NEGATOR`子句里面給出。提供否定符對查詢優化器是非常有幫助的，因為這樣就允許像`NOT (x = y)` 這樣的表達式簡化成`x <> y`。這樣的情況比你想像的要頻繁的多，因為`NOT`操作可能因為其它的重排列而被引入。否定符對可以用上面交換符對中解釋的相同的方法來定義。 ## 35.13.3\. `RESTRICT` 如果提供了`RESTRICT`子句，則為操作符命名一個選擇性限制計算函數(注意這里是一個函數名，而不是一個操作符名)。`RESTRICT`子句只是對返回`boolean`變量的雙目操作符有意義。選擇性限制計算符的概念是猜測一個表中所有行的哪一部分對于目前的操作符和特定的常量將滿足一個像下面這樣形式的 `WHERE`條件子句。 ``` column OP constant ``` 它可以給出這種類型的`WHERE`子句可以刪除多少行的一個概念，這將幫助優化器進行優化。你可能會說，如果該常量(constant)在左邊怎么辦?哦，那是`COMMUTATOR`干的事... 書寫新的選擇性限制計算函數遠遠超出了本章的范圍，不過很幸運的是，通常你對自己的操作符只需要使用系統標準的計算器之一就行了。下面是一些標準限制計算器： | `eqsel` 用于 `=` | |:--- | | `neqsel` 用于 `<>` | | `scalarltsel` 用于 `<` 或 `<=` | | `scalargtsel` 用于 `>` 或 `>=` | 這些都是分類，看起來有點奇怪，不過如果你仔細想想，就會覺得有道理。`=`大多將只接受表中的一小部分行； `<>`大多將拒絕一小部分行。`<`接受的行取決于給出的常量落在表的該列數據值的哪一個范圍里 (該值碰巧是`ANALYZE`收集并且提供給選擇性計算器的信息)。`<=`在同樣的常量時會接受比 `<`略微大一些的行，不過它們也非常接近，幾乎不值得區別開來，尤其是無論如何也比做盲猜好得多。類似的情況也適用于`>`和`>=`。你可能常習慣于把`eqsel`或`neqsel`用于那些非常高或者非常低選擇性的操作符，即使它們并非真正相等或者不相等。例如，基于只會匹配整個表中一小部分記錄的假設，幾何操作符約等于就使用`eqsel`。你可以把`scalarltsel`和`scalargtsel` 用于比較那些為進行范圍比較被轉化為數字尺度后有明顯意義的數據類型。如果可能，把該數據類型增加到可以被`src/backend/utils/adt/selfuncs.c`文件里的 `convert_to_scalar()`函數理解的部分。最終，這個過程將被放到由`pg_type` 表里的一個列標識的每種類型一個的函數代替，不過目前還沒有這么做。如果你沒有做這些，系統仍然能工作，不過優化器的估計不會像想像的那么好。在`src/backend/utils/adt/geo_selfuncs.c`里還有為幾何操作符設計的額外選擇性評估函數： `areasel`, `positionsel`, `contsel`。目前，它們都只是存根，但是你還是可以使用(最后是改良)它們。 ## 35.13.4\. `JOIN` 如果提供了`JOIN`子句，則為操作符命名一個連接選擇性計算器函數(是函數名，不是操作符名)。`JOIN`子句只是對返回`boolean`的雙目操作符有意義。一個連接選擇性計算器后面的概念是猜測一對表上的哪部分行對目前的操作符將滿足下面形式的 `WHERE`子句的條件： ``` table1.column1 OP table2.column2 ``` 和`RESTRICT`子句一樣，這些很有可能幫助優化器用最少的處理勾畫出要采取可能的連接順序中的哪一個。和前面一樣，本節不會試圖解釋如何書寫一個連接選擇性計算器函數，但是會建議你盡可能使用一個標準的計算器： | `eqjoinsel` 用于 `=` | | `neqjoinsel` 用于 `<>` | | `scalarltjoinsel` 用于 `<` 或 `<=` | | `scalargtjoinsel` 用于 `>` 或 `>=` | | `areajoinsel` 用于基于面積的二維比較 | | `positionjoinsel` 用于基于位置的二維比較 | | `contjoinsel` 用于基于包含的二維比較 | ## 35.13.5\. `HASHES` 如果出現了`HASHES`子句，則告訴系統對于一個基于此操作符的連接可以使用 Hash 連接。 `HASHES`只對返回`boolean`的雙目操作符有意義，并且實際上該操作符最好是對某種數據類型的相等操作符。 Hash 連接的假設是：對于一對散列到同樣的 Hash 代碼的左和右操作數值，該連接操作符只能返回真。如果兩個值被放到不同的 Hash 桶里，連接將根本不比較它們，隱含地意味著連接操作符的結果一定是假。所以對于不代表相等的操作符，聲明`HASHES`是沒有意義的。在大多數情況下，支持兩端接受同樣數據類型的操作符是唯一可行的。然而，有時為兩個或更多的數據類型設計兼容的hash函數也是可能的；也就是，函數將為"相等的" 值產生相同的hash代碼，即使值有不同的代表。例如，當哈希整數有不容的寬度時，排列這個屬性是非常簡單。要標記為`HASHES`，連接操作符必須出現在一個 Hash 索引操作符類中。在創建操作符時并不強制這樣，因為引用操作符類不可能還存在。但是企圖在 Hash 連接中使用尚不存在的操作符類將在運行時導致失敗。系統需要操作符類根據操作符的輸入數據類型確定特定于該數據類型的 Hash 函數。當然，你必須在創建操作符類之前首先提供合適的 Hash 函數。在編寫 Hash 函數時必須小心，因為有一些硬件相關的因素會導致錯誤。比如，如果你的數據類型是一個存在間隙的結構體，你就不能簡單的將其傳遞給某個`hash_any` 函數。除非你的其它操作符能夠確保這些間隙總是零(這是建議的策略)。另一個例子是在符合IEEE浮點標準的機器上，負零和正零是不同的值(不同的位模式)，但是它們被定義為比較相等。如果一個浮點值可能包含負零，那么必須使用額外的步驟來確保產生和正零相同的 Hash 值。一個可 Hash 連接的操作符必須有一個在相同操作符類中的交換符（如果兩個操作符數據類型相同則是它本身，如果不同則是一個相關的相等操作符）。如果不是這樣，當使用操作符時會發生規劃器錯誤。同樣，一個hash操作符類支持多種數據類型以為數據類型的每種結合提供相等操作符是一個好主意（但不是嚴格要求）；這允許更好的優化。 > **Note:** 在一個可 Hash 連接的操作符下層的函數必須標明 immutable 或 stable 。如果它是 volatile ，那么系統將從不在 Hash 連接中使用這些操作符。 > **Note:** 如果一個可 Hash 連接的操作符有一個下層函數標記為嚴格的(strict)，那么該函數必須完整：也就是說，對于任何非 NULL 輸入，它應該返回 TRUE 或 FALSE ，但絕不能是 NULL 。如果不遵循這個規則，`IN`操作的 Hash 優化可能會生成錯誤的結果。特別是根據規范正確答案是 NULL 的時候，`IN`可能會返回 FALSE ；或者它可能生成一個錯誤，抱怨說它對 NULL 結果沒有思想準備。 ## 35.13.6\. `MERGES` 如果出現了`MERGES`子句，則告訴系統對基于目前操作符的連接可以使用融合連接方法。 `MERGES`只是對返回`boolean`的雙目操作符有意義，實際上這個操作符對于某些數據類型或者某對數據類型必須表示相等。融合連接是以這樣的概念為基礎的：對左邊和右邊的表進行排序，然后并發地掃描它們。所以，兩種數據類型都必須是能夠完全排序的，并且連接操作符必須只對那些落在排序順序中的 "某個位置"的數值對成功。實際上這意味著連接操作符必須表現得像等于。但是可以對兩種不同數據類型進行融合連接(只要他們邏輯相等即可)。例如，`smallint` 對`integer`的相等操作符是可以用融合連接的。只需要可以把兩種數據類型排列成邏輯可比序列的排序操作符即可。要標記為`MERGES`，連接操作符必須作為`btree`索引操作符類的一個相等的成員出現。在創建操作符時并不強制這么做，因為引用操作符類不可能還存在。但是操作符不會被實際用于融合連接，除非可以找到一個匹配操作符類。 `MERGES`標志因此作為一個對規劃器的提示，查找一個匹配的操作符類是值得的。可融合連接的相等操作符必須有一個在同一個操作符類中的交換符（如果兩個操作數數據類型相同則是它自身，如果不同則是一個相關的相等操作符）。如果不是這樣，當使用操作符時會發生規劃器錯誤。同樣，一個`btree`操作符類支持多種數據類型以為數據類型的每種結合提供相等操作符是一個好主意（但不是嚴格要求）；這允許更好的優化。 > **Note:** 在一個可融合連接操作符下層的函數必須標記為永久(immutable)或者穩定(stable)。如果它是易失的(volatile)，那么系統將從不在融合連接中使用這些操作符。