| PostgreSQL 8.0.0 中文文件（轉譯自 PostgreSQL 中國 製作的簡體中文版本） | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 31. 擴展 SQL | Fast Forward | Next |
PostgreSQL 的操作符定義可以包括幾個可選的子句， 這些子句告訴系統一些關於該操作符的特性的有用訊息。 在可能的情況下，我們都應該提供這些子句， 因為它們可能為使用這個操作符的查詢帶來可觀的速度提升。 不過要注意如果您聲明了這些子句，您必須確保它們是正確的！ 對優化子句的錯誤使用將導致伺服器的崩潰， 微小的輸出錯誤或者其他糟糕事情。如果您對這些事情不確定的話， 您可以總是忽略優化子句；唯一的後果是查詢可能比需要的執行的慢一些。
附加的優化子句可能在今後的 PostgreSQL版本裡出現。 這裡描述的都是版本 8.0.0 可以理解的。
如果提供了 COMMUTATOR子句，則命名一個操作符是被定義的操作符的交換符。 如果有兩個操作符A，B，對於任何可能的輸入數值 x，y 都有 A，B，對於任何可能的輸入數值都有 (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子句， 然後在第二個操作符的定義裡提供一個 (COMMUTATOR子句)。因為 PostgreSQL 知道換向操作符是成對出現的， 所以當它看到第二個定義時它會自動折回並填充第一個定義裡空缺的COMMUTATOR子句。
另一個更直接的方法是在兩個定義裡面都包含COMMUTATOR子句。當 PostgreSQL 處理第一個定義並意識到 COMMUTAOTR 指向一個不存在的操作符， 系統會在系統資料表裡面為該操作符記錄一個虛擬的記錄。 這個虛擬的記錄只有操作符名，左和右操作數類型以及結果類型是有效的， 因為這些是到目前為止 PostgreSQL 可以推導出來的東西。第一個操作符資料表記錄將和這個虛擬記錄連線。 稍後，當您定義第二個操作符時，系統將用來自第二個操作符的訊息更新該虛擬記錄。 如果您試圖在虛擬操作符被填充之前使用它， 您將只能收到一條錯誤訊息。
如果提供了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可能因為其他的重排列而被引入。
否定符對可以用上面換向符對中解釋的相同的方法來定義。
如果提供了RESTRICT子句，則為操作符命名一個選擇性限制計算函數 （注意這裡是一個函數名，而不是一個操作符名）。 RESTRICT子句只是對返回boolean變量的雙目操作符有意義。 選擇性限制計算符的概念是猜測一個資料表中所有行的哪 一部分對於目前的操作符和特定的常量將滿足一個象下面這樣形式的 WHERE 條件子句
column OP constant
它可以給出這種類型的WHERE子句可以刪除多少行的一個概念， 這將幫助優化器進行優化。（您可能會說， 如果該常量（constant）在左邊怎麼辦？哦，那是COMMUTATOR 幹的事...）
書寫新的選擇性限制計算函數遠遠超出了本章的範圍， 不過很幸運的是，通常您對自己的操作符只需要使用系統標準的計算器之一就行了。下面是一些標準限制計算器：
| eqsel for = |
| neqsel for <> |
| scalarltsel for < or <= |
| scalargtsel for > or >= |
您可能常習慣於把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。在我寫這些的時候，它們都只是存根， 但是您還是可以使用（或者更好的是，改良它們）它們。
如果提供了JOIN子句， 則為操作符命名一個連接選擇性函數。（注意這裡是函數名，不是操作符名。） JOIN子句只是對返回boolean的雙目操作符有意義。 一個連接選擇性計算器後面的概念是猜測一對資料表上的哪一部分 行對目前的操作符將滿足下面形式的WHERE子句的條件
table1.field1 OP table2.field2
和RESTRICT子句一樣， 這些很有可能幫助優化器用最少的處理勾畫出要採取可能的連接順序中的哪一個。
和前面一樣，本節不會試圖解釋如何書寫一個連接選擇性計算器函數， 但是會建議您在有一個可用的情況下，使用一個標準的計算器：
| eqjoinsel for = |
| neqjoinsel for <> |
| scalarltjoinsel for < or <= |
| scalargtjoinsel for > or >= |
| areajoinsel for 2D area-based comparisons |
| positionjoinsel for 2D position-based comparisons |
| contjoinsel for 2D containment-based comparisons |
如果出現了HASHES子句， 則告訴系統對於一個基於此操作符的連接可以使用哈希（散列）連接。 HASHES只對返回boolean的雙目操作符有意義， 並且實際上該操作符最好是對某種資料類型的相等操作符。
哈希（散列）連接的假設是： 對於一對哈希（散列）到同樣的哈希（散列）代碼的左和右操作數值，該連接操作符只能返回真。 如果兩個值被放到不同的哈希桶裡， 連接將根本不比較它們，隱含地意味著連接操作符的結果一定是假。 所以對於不代資料表相等的操作符，聲明HASHES是沒有意義的。
實際上，邏輯相等還不夠好；該操作符最好是代資料表完全的按位相等， 因為哈希函數將對該值的內存資料表現形式進行計算而不管這些位的含義是什麼。 比如，多邊形操作符 ~=，它檢查兩個多邊形是否相等， 它就不是按位相等的，因為即使兩個多邊形的定點聲明的順序不同， 我們也可以認為它們是相等的。 這就意味著對於一個用 ~= 在對邊形域之間的連接， 如果用哈希連接實現將會和用別的連接實現生成不同的結果， 因為可以匹配的大部分資料對將被哈希成不同的值因而不會被哈希連接進行比較。 但是如果優化器選擇使用不同的連接方法， 那麼所有~=操作符說相等的資料對都會被找出來。我們不想出現那種不一致性， 所以我們沒有標記~=為可哈希的。
同時還有一些硬件相關的因素會導致一個哈希連接的計算錯誤。 例如，如果您的資料類型是一個結構，結構裡可能有不引人注意的填充位， 這時把這個等號操作符標記為HASHES也是不安全的。 （除非您書寫您的其他操作符以確保這些未用的位總是零。這是我們建議的策略。） 另一個例子是浮點資料類型對哈希連接也是不安全的。 在符合IEEE浮點標準的機器上，負零和正零是不同的值（不同的位模式）， 但是它們被定義為比較相等。所以，如果浮點等號被標記為HASHES， 一個負零和一個正零可能不被哈希連接匹配，但是用其他連接處理， 它們應該是匹配的。
底線是： 您可能只能把HASHES用於用（或可以用）memcmp() 實現的等號操作符。
注意: 在一個 hashjoinable （可散列連接）的操作符下層的函數必須 標記為 immutable （永久的）或者 stable（穩定的）。如果它是 volatile（易失的），那麼系統 將從不用這些操作符於散列連接中。
注意: 如果一個 hashjoinable （可散列連接）有一個下層函數標記為嚴格的（strict）， 那麼該函數必須完整：也就是說，對於任何非 NULL 輸入，它應該返回 TRUE 或者 FALSE，決不能是 NULL。 如果不遵循這個規則，IN 操作的散列優化可能會生成錯誤的結果。 （特別是，根據規範，正確的答案可能是 NULL 的時候，IN 可能會返回 FALSE； 或者它可能生成一個錯誤，抱怨說它對 NULL 結果沒有思想準備。）
如果出現了SORT子句， 則告訴系統對基於目前的操作符可以使用融合連接方法。 MERGES只是對返回 boolean 的雙目操作符有意義， 實際上這個操作符對於某些資料類型或者某對資料類型必須資料表示相等。
融合連接是以這樣的概念為基礎的： 對左邊和右邊的資料表進行排序，然後並行地掃瞄它們。所以，兩種資料類型都必須是能夠完全排序的， 並且連接操作符必須只對那些落在排序順序中的"某個位置"的數值對成功。 實際上這意味著連接操作符必須資料表現得像等於。 但是和哈希連接不同，哈希連接裡左邊和右邊的資料類型最好是相同的（至少是按位相等）， 可以對兩種不同資料類型進行融合連接 -- 只要他們邏輯相等即可。 例如，smallint對integer的相等操作符是可以用融合連接的。 我們只需要可以把兩種資料類型排列成邏輯可比的序列的排序操作符即可。
融合連接地執行要求系統可以標識四種與融和連接相等性操作符相關的操作符： 用於左手邊操作數資料類型地小於比較，用於右手邊操作數資料類型的小於比較， 在兩種資料類型之間的小於比較，以及在兩種資料類型之間的大於比較。 （如果可以融和連接的操作符有兩個不同的操作數資料類型，那麼這裡實際上有四種不同的操作符； 但是如果操作數類型相同，那麼三個小於操作符都是相同的操作符。） 我們可以透過名字逐個聲明這些操作符，分別是 SORT1， SORT2，LTCMP，以及 GTCMP 選項。 如果在聲明了 MERGES 的同時卻省略了其中的任何一個， 那麼系統將填充預設的名字 <，<，<， >。同樣，如果這四種操作符選項中的任何出現， 那麼將假設MERGES 為隱含的， 因此我們可以只聲明其中一部分操作符然後讓系統填充其它的。
四種比較操作符的操作數類型可以從可融合連接的操作符的操作數類型歸納出來， 因此，和 COMMUTATOR 一樣，我們只需要在這些子句中給出操作符名。 除非您使用了特定選取的操作符名，那麼寫一個 MERGES 然後讓系統填充細節就足夠了。（和 COMMUTATOR 以及 NEGATOR 一樣，如果您碰巧在其它操作符前定義了相等性操作符，那麼系統可以製作偽操作符記錄。）
還有一些對您標記為可融合連接的操作符的附加限制。 這些限制目前沒有被CREATE OPERATOR檢查， 但是如果下面之一不為真的話，融合連接會在執行時失敗：
可融合連接的相等操作符必須有一個交換符 （如果兩個操作數資料類型相同則是它自身，如果不同則是一個相關的相等操作符）。
如果有一種可融合連接的操作符與兩種資料類型 A 和 B 相關， 並且另外一個可融合連接的操作符與 B 和任意第三種資料類型 C 相關，那麼 A 和 C 也一定有一種可融合連接的操作符；換句話來說， 一個可融合的操作符必須是可傳遞的。
如果您命名的這四種操作符不能完全對資料值排序，那麼在執行時就會發生非常怪異的結果。
注意: 在一個可融合連接（mergejoinable）的操作符下層的函數必須標記為永久（immutable）或者穩定（stable）。 如果它是易失的，那麼系統將從不使用它們用於融合連接。
注意: GROUP BY 和 DISTINCT 操作要求每個被分組或者比較的資料類型都有一個可融合連接的相等性操作符，叫做 =。 相等性操作符和他的關聯 SORT1 操作符用於實現這些操作。 同樣，相關的 SORT1 操作符是 ORDER BY 的預設排序操作符。
注意: 注意，在 PostgreSQL 版本 7.3 以前， 所寫 MERGES 並不存在：要寫一個可融合連接的操作符， 我們必須明確寫出 SORT1 和 SORT2。同樣， LTCMP 和 GTCMP 選項並不存在； 這些操作符分別寫成了硬代碼 < 和 >。