| PostgreSQL 7.4 文檔 | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 33. 擴展 SQL | Fast Forward | Next |
PostgreSQL 的操作符定義可以包括幾個可選的子句， 這些子句告訴系統一些關于該操作符的特性的有用信息。 在可能的情況下，我們都應該提供這些子句， 因為它們可能為使用這個操作符的查詢帶來可觀的速度提升。 不過要注意如果你聲明了這些子句，你必須確保它們是正確的！ 對優化子句的錯誤使用將導致服務器的崩潰， 微小的輸出錯誤或者其他糟糕事情。如果你對這些事情不確定的話， 你可以總是忽略優化子句；唯一的後果是查詢可能比需要的運行的慢一些。
附加的優化子句可能在今後的 PostgreSQL版本裡出現。 這裡描述的都是版本 7.4 可以理解的。
如果提供了 COMMUTATOR子句，則命名一個操作符是被定義的操作符的交換符。 如果有兩個操作符A，B，對于任何可能的輸入數值 x，y 都有 A，B，對于任何 都有 (x A y) 等于 (y B x)，那麼我們就說 A 是 B 的交換符， 同樣 B 也是 A 的交換符。 例如，操作符 < 和 > 對于所使用的一定的數據類型通常都是對方的交換符， 而操作符 '+' 通常是它自身的交換符。但是操作符 '-' 通常沒有交換符。
一個被換向的操作符的左操作數與它的交換符的右操作數類型相同，反之亦然。 所以 PostgreSQL所需要的只是一個交換符操作符 的名稱用以查找該交換符，那也是COMMUTATOR 子句裡所需要的唯一的東西。
當你定義一個自換向的操作符時，你定義它就是了。 當你定義一對交換符操作符時，事情就有一點棘手： 怎樣定義一個操作符的交換符指向另一個你還沒有定義的操作符呢？ 我們對這個問題有兩個解決方法：
一個方法是省略你定義的第一個操作符的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 選項並不存在；這些操作符 分別寫成了硬代碼 < 和 >。