Postgresql Linked server遠端伺服器取資料的執行計劃原理

本文來自postgresql官方文件






代價估計選項
postgres_fdw透過在遠端伺服器上執行查詢來檢索遠端資料，因此理想的掃描一個外部表的估計代價應該是在遠端伺服器上完成它的花銷，外加一些通訊開銷。得到這樣一個估計的最可靠的方法是詢問遠端伺服器並加上一些通訊開銷 — 但是對於簡單查詢，不值得為獲得一個代價估計而額外使用一次遠端查詢。因此postgres_fdw提供了下列選項來控制如何完成代價估計

use_remote_estimate
這個選項控制postgres_fdw 是否發出遠端 EXPLAIN 命令以獲得成本估算，它可以為一個外部表或一個外部伺服器指定。一個外部表的設定會覆蓋它的伺服器的任何設定，但是隻用於這個表。預設值是false。

fdw_startup_cost
這個選項是一個要被加到那個伺服器上所有外部表掃描的估計啟動代價的數字值。這表示在遠端端建立連線、解析和規劃查詢等的額外開銷。預設值為 100。

fdw_tuple_cost
這個選項是一個數字值，它被用作那個伺服器上外部表掃描的每元組額外成本，它可以為一個外部伺服器指定。這表示在伺服器之間資料傳輸的額外負荷。你可以增加或減少這個數來反映到遠端伺服器更高或更低的網路延遲。預設值是0.01。

當 use_remote_estimate 為 true 時，postgres_fdw 從遠端伺服器獲取行數和成本估計，然後將 fdw_startup_cost 和 fdw_tuple_cost 新增到成本估計中。當 use_remote_estimate 為 false 時，postgres_fdw 執行本地行計數和成本估算，然後將 fdw_startup_cost 和 fdw_tuple_cost 新增到成本估算中。這種本地估計不太可能非常準確，除非遠端表的統計資訊的本地副本可用。在外部表上執行 ANALYZE 是更新本地統計資訊的方式；這將執行遠端表的掃描，然後就像該表是本地表一樣計算和儲存統計資訊。保留本地統計資訊可能是減少遠端表的每個查詢計劃開銷的有用方法——但如果遠端表經常更新，本地統計資訊很快就會過時。



遠端執行選項
預設情況下，只有使用了內建運算子和函式的WHERE子句才會被考慮在遠端伺服器上執行。涉及非內建函式的子句將會在取完行後在本地進行檢查。如果這類函式在遠端伺服器上可用並且可以用來產生和本地執行時一樣的結果，則可以透過將這種WHERE子句傳送到遠端執行來提高效能。可以用下面的選項控制這種行為：

extensions
這個選項是一個用逗號分隔的已安裝的PostgreSQL副檔名稱列表，這些擴充套件在本地和遠端伺服器上具有相容的版本。屬於一個該列表中擴充套件的 immutable 函式和運算子將被考慮轉移到遠端伺服器上執行。這個選項只能為外部伺服器指定，無法逐個表指定。在使用extensions選項時，使用者應該負責確保列出的擴充套件在本地和遠端伺服器上都存在且保持一致。否則，遠端查詢可能失敗或者行為異常。

fetch_size
這個選項指定在每次獲取行的操作中postgres_fdw應該得到的行數。可以為一個外部表或者外部伺服器指定這個選項。在表上指定的選項將會覆蓋在伺服器級別上指定的選項。預設值為100。



遠端查詢最佳化
postgres_fdw嘗試最佳化遠端查詢來減少從外部伺服器傳來的資料量。這可以透過把查詢的WHERE子句傳送給遠端伺服器執行來完成，並且還可以不檢索當前查詢不需要的表列。為了降低錯誤執行查詢的風險，WHERE 子句不會傳送到遠端伺服器，除非它們僅使用內建的或屬於外部伺服器擴充套件選項中列出的擴充套件的資料型別、運算子和函式。這些子句中的運算子合函式也必須是IMMUTABLE。對於UPDATE或者DELETE查詢， 如果沒有不能傳送給遠端伺服器的WHERE子句、 沒有查詢的本地連線、目標表上沒有本地的行級BEFORE或AFTER觸發器， 並且沒有來自父檢視的CHECK OPTION約束，postgres_fdw會嘗試透過將整個查詢傳送給遠端伺服器來最佳化查詢的執行。在UPDATE中，賦值給目標列的表示式只能使用內建資料型別、IMMUTABLE運算子或者IMMUTABLE運算子，這樣能降低查詢被誤執行的風險。

當postgres_fdw碰到同一個外部伺服器上的外部表之間的連線時，它會把整個連線傳送給外部伺服器，除非由於某些原因它認為逐個從每一個表取得行的效率更高或者涉及的表引用屬於不同的使用者對映。在傳送JOIN子句時，它也會採取和上述WHERE子句相同的預防措施。

實際被髮送到遠端伺服器執行的查詢可以使用EXPLAIN VERBOSE來檢查。





來自TDS_FDW(PG到Sqlserver的linked server元件)的官方文件說明



use_remote_estimate
Whether we estimate the size of the table by performing some operation on the remote server (as defined by row_estimate_method), or whether we just use a local estimate, as defined by local_tuple_estimate.
我們是否透過在遠端伺服器上執行一些操作來估計表的大小（由 row_estimate_method 定義），或者我們是否只使用本地估計，如 local_tuple_estimate 所定義。

local_tuple_estimate
A locally set estimate of the number of tuples that is used when use_remote_estimate is disabled.
禁用 use_remote_estimate 時使用的元組數量的本地設定估計。

row_estimate_method
Default: execute
This can be one of the following values:
execute: Execute the query on the remote server, and get the actual number of rows in the query.
在遠端伺服器上執行查詢，並獲取查詢中的實際行數。
showplan_all: This gets the estimated number of rows using MS SQL Server's SET SHOWPLAN_ALL.
使用 MS SQL Server 的 SET SHOWPLAN_ALL 獲取估計的行數。

tds_fdw沒有postgresql_fdw中的遠端連線選項fetch_size，如下
ocmportfolioDB=# CREATE SERVER mssql_svrtest1 FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername '172.22.100.103', port '1433', database 'Wondb',fetch_size '20');
ERROR:  Invalid option "fetch_size"
HINT:  Valid options in this context are: servername, language, character_set, port, database, dbuse, tds_version, msg_handler, row_estimate_method, use_remote_estimate, fdw_startup_cost, fdw_tuple_cost





TDS_FDW外部大表limit 1很慢的一個案例

現象1
1、查詢外部表where osid<10003，返回15074行到postgresql服務端再展示15074行給客戶端，總計需要180ms
2、查詢外部表where osid<10003 limit 1，返回15074行到postgresql端服務端再展示1行給客戶端，總計需要75ms
3、查詢外部表limit 1沒有where條件，返回129348086行到postgresql端服務端再展示1行給客戶端，總計需要245000ms

現象2
postgresql查詢外部表“select * from won.SDHSFTmp limit 1”  和 sqlserver的 “select top 1 * from dbo.SDHsfTmp”很類似，但是
sqlserver端直接查詢“select top 1 * from dbo.SDHsfTmp”很快，postgresql查詢外部表“select * from won.SDHSFTmp limit 1” 很慢

原因是因為：因為postgresql的limit 1語句在sqlserver中並不存在，所以當postgresql端的limit 1沒有where條件，則postgresql端的語句壓根不會透過tds_fdw轉化為sqlserver對應的select top 1 這樣的語句，這樣結果就是sqlserver遠端只能直接查詢整表再把整表結果返回給postgresql端，由postgresql接受了整表結果再取1行



ocmportfolioDB=# explain analyze select * from won.SDHSFTmp where osid<10003;
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on sdhsftmp  (cost=200.00..1507650.74 rows=15074 width=100) (actual time=1.117..81.374 rows=15074 loops=1)
 Planning Time: 71.005 ms
 Execution Time: 101.006 ms
(3 rows)


ocmportfolioDB=# explain analyze select * from won.SDHSFTmp where osid<10003 limit 1;
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=200.00..300.00 rows=1 width=100) (actual time=1.237..1.239 rows=1 loops=1)
   ->  Foreign Scan on sdhsftmp  (cost=200.00..1507650.74 rows=15074 width=100) (actual time=1.234..1.235 rows=1 loops=1)
 Planning Time: 55.918 ms
 Execution Time: 19.656 ms
(4 rows)


ocmportfolioDB=# explain analyze select * from won.SDHSFTmp limit 1;
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=200.00..300.01 rows=1 width=100) (actual time=4.995..4.998 rows=1 loops=1)
   ->  Foreign Scan on sdhsftmp  (cost=200.00..12936102180.86 rows=129348086 width=100) (actual time=4.992..4.993 rows=1 loops=1)
 Planning Time: 244993.104 ms
 Execution Time: 78.929 ms
(4 rows)