Postgresql

我們可以在第一次執行 PL/pgSQL 函式時執行最優計劃而不是通用計劃嗎？

  • August 11, 2017

我有一個非常繁忙的功能，我需要盡我所能優化。這個函式只是一個嵌套的 select 語句，舊應用程序每秒請求數次。

索引已經到位，但我注意到它僅在第一次執行函式後使用。我認為問題在於 Postgres 創建了一個通用的執行計劃，因為它在大多數情況下是高度排他的，但有時它可能不是那麼好。

當我EXPLAIN ANALYZE在第一次執行後進行測試時，查詢執行得非常快，但應用程序會話僅呼叫該函式一次然後終止。我需要第一次執行使用實際的優化計劃。任何人都可以幫忙嗎？

我們嘗試弄亂管理連接池的連接器驅動程序以發出 aDISCARD TEMP而不是DISCARD ALL，因此它可以保持會話的記憶體計劃並且性能達到頂峰，但我不想在生產環境中這樣做.

我們在 CentOS 6 上執行的 Postgres 9.4 上。我嘗試作為 SQL 函式執行，但沒有幫助，它實際上作為 plpgsql 函式更快。這是功能程式碼：

CREATE OR REPLACE FUNCTION public.ap_keepalive_geteqpid_veiid(
   IN tcbserie bigint,
   IN protocolo integer)
 RETURNS TABLE(eqpid integer, veiid integer, tcbid integer, veiplaca character varying, veiproprietariocliid integer, tcbtppid integer, tcbversao character, veirpmparametro double precision, tcbconfiguracao bigint, tcbevtconfig integer, veibitsalertas integer, sluid integer, harid integer) AS
$BODY$
BEGIN
   RETURN QUERY
   SELECT  teqp.eqpID, 
           teqp.eqpveiID AS veiID, 
           tcb.tcbID, 
           tvei.veiPlaca, 
           tvei.veiProprietariocliID, 
           tcb.tcbtppID, 
           tcb.tcbVersao,
           tvei.veiRPMParametro, 
           COALESCE(COALESCE(NULLIF(tcb.tcbConfiguracao, 0), tcc.clcConfiguracaoBitsVeic), 0) AS tcbConfiguracao,
           COALESCE(tcb.tcbevtConfig, 0) AS tcbevtConfig,
           COALESCE(tvei.veiBitsAlertas, 0) AS veiBitsAlertas,
           COALESCE(tvei.veisluID, 0) AS sluID,
           COALESCE(tcb.tcbharID, 0) AS harID
   FROM TabEquipamento teqp
   INNER JOIN TabPacoteProduto tpp ON teqp.eqptppID = tpp.tppID
   INNER JOIN TabComputadorBordo tcb ON teqp.eqptcbID = tcb.tcbID
   INNER JOIN TabVeiculos tvei ON teqp.eqpveiID = tvei.veiID
   LEFT JOIN TabCliente tcli ON tcli.cliid = tvei.veiProprietariocliID
   LEFT JOIN TabClienteConfig tcc ON tcc.clcCliID = tcli.cliID
   WHERE   tcb.tcbserie = $1
       AND teqp.eqpAtivo = 1
       AND tpp.tppIDProtocolo = $2
       AND tvei.veiBloqueioSinal = 0;

END
$BODY$
 LANGUAGE plpgsql VOLATILE COST 100 ROWS 1;

第一次執行中的執行計劃：

"Function Scan on ap_keepalive_geteqpid_veiid  (cost=0.25..0.26 rows=1 width=116) (actual time=3.268..3.268 rows=1 loops=1)"
"Planning time: 0.032 ms"
"Execution time: 3.288 ms"

第二次執行：

"Function Scan on ap_keepalive_geteqpid_veiid  (cost=0.25..0.26 rows=1 width=116) (actual time=0.401..0.402 rows=1 loops=1)"
"Planning time: 0.058 ms"
"Execution time: 0.423 ms"

編輯：添加了具有意外結果的函式的自動解釋輸出（至少對我而言）。自動解釋聲稱 postgres 以所需的純文字僅用 0.230 毫秒執行了該函式，但該函式本身花費了 4.057 毫秒。我不知道這是否準確。

< 2015-12-14 18:10:02.314 BRST >LOG:  duration: 0.234 ms  plan:
Query Text: SELECT  teqp.eqpID, 
       teqp.eqpveiID AS veiID, 
       tcb.tcbID, 
       tvei.veiPlaca, 
       tvei.veiProprietariocliID, 
       tcb.tcbtppID, 
       tcb.tcbVersao,
       tvei.veiRPMParametro, 
       COALESCE(COALESCE(NULLIF(tcb.tcbConfiguracao, 0), tcc.clcConfiguracaoBitsVeic), 0) AS tcbConfiguracao,
       COALESCE(tcb.tcbevtConfig, 0) AS tcbevtConfig,
       COALESCE(tvei.veiBitsAlertas, 0) AS veiBitsAlertas,
       COALESCE(tvei.veisluID, 0) AS sluID,
       COALESCE(tcb.tcbharID, 0) AS harID
   FROM TabComputadorBordo tcb
   INNER JOIN TabEquipamento teqp ON teqp.eqptcbID = tcb.tcbID
   INNER JOIN TabPacoteProduto tpp ON teqp.eqptppID = tpp.tppID
   INNER JOIN TabVeiculos tvei ON teqp.eqpveiID = tvei.veiID
   LEFT JOIN TabCliente tcli ON tcli.cliid = tvei.veiProprietariocliID
   LEFT JOIN TabClienteConfig tcc ON tcc.clcCliID = tcli.cliID
   WHERE   tcb.tcbserie = $1
       AND teqp.eqpAtivo = 1
       AND tpp.tppIDProtocolo = $2
       AND tvei.veiBloqueioSinal = 0
Nested Loop Left Join  (cost=1.29..18.65 rows=1 width=75) (actual time=0.226..0.230 rows=1 loops=1)
 Join Filter: (tcc.clccliid = tcli.cliid)
 Rows Removed by Join Filter: 3
 ->  Nested Loop Left Join  (cost=1.29..17.57 rows=1 width=75) (actual time=0.205..0.209 rows=1 loops=1)
       ->  Nested Loop  (cost=1.01..17.26 rows=1 width=71) (actual time=0.200..0.203 rows=1 loops=1)
             ->  Nested Loop  (cost=0.72..16.80 rows=1 width=43) (actual time=0.097..0.098 rows=1 loops=1)
                   ->  Nested Loop  (cost=0.58..16.63 rows=1 width=47) (actual time=0.079..0.080 rows=1 loops=1)
                         ->  Index Scan using ix_tabcomputadorbordo_tcbserie on tabcomputadorbordo tcb  (cost=0.29..8.31 rows=1 width=35) (actual time=0.046..0.046 rows=1 loops=1)
                               Index Cond: (tcbserie = $1)
                         ->  Index Scan using ix_tabequipamento_eqptcbid_eqpativo_eqptppid_eqpveiid on tabequipamento teqp  (cost=0.29..8.31 rows=1 width=16) (actual time=0.030..0.031 rows=1 loops=1)
                               Index Cond: ((eqptcbid = tcb.tcbid) AND (eqpativo = 1))
                   ->  Index Only Scan using ix_tabpacoteproduto_tppidprotocolo on tabpacoteproduto tpp  (cost=0.14..0.16 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=1)
                         Index Cond: ((tppidprotocolo = $2) AND (tppid = teqp.eqptppid))
                         Heap Fetches: 1
             ->  Index Scan using pk_tabveiculos on tabveiculos tvei  (cost=0.29..0.45 rows=1 width=32) (actual time=0.100..0.101 rows=1 loops=1)
                   Index Cond: (veiid = teqp.eqpveiid)
                   Filter: (veibloqueiosinal = 0)
       ->  Index Only Scan using pk_tabcliente on tabcliente tcli  (cost=0.28..0.30 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)
             Index Cond: (cliid = tvei.veiproprietariocliid)
             Heap Fetches: 1
 ->  Seq Scan on tabclienteconfig tcc  (cost=0.00..1.03 rows=3 width=8) (actual time=0.014..0.015 rows=3 loops=1)
< 2015-12-14 18:10:02.314 BRST >CONTEXTO:  função PL/pgSQL ap_keepalive_geteqpid_veiid(bigint,integer) linha 4 em RETURN QUERY
< 2015-12-14 18:10:02.314 BRST >LOG:  duration: 4.057 ms  plan:
Query Text: SELECT * FROM ap_keepalive_geteqpid_veiid (tcbSerie := 8259492, protocolo:= 422);

我清理並簡化了一些小細節。對於性能而言，這應該不會有太大變化。但增加的SET join_collapse_limit = 1可能：

CREATE OR REPLACE FUNCTION public.ap_keepalive_geteqpid_veiid(tcbserie bigint, protocolo int)
 RETURNS TABLE(eqpid int, veiid int, tcbid int
             , veiplaca varchar, veiproprietariocliid int, tcbtppid int, tcbversao character, veirpmparametro double precision
             , tcbconfiguracao bigint, tcbevtconfig int, veibitsalertas int, sluid int, harid int) AS
$func$
BEGIN
  RETURN QUERY
  SELECT eqp.eqpID
       , eqp.eqpveiID AS veiID
       , cb.tcbID
       , vei.veiPlaca
       , vei.veiProprietariocliID
       , cb.tcbtppID
       , cb.tcbVersao
       , vei.veiRPMParametro
       , CASE WHEN cb.tcbConfiguracao = 0 THEN COALESCE(cc.clcConfiguracaoBitsVeic, 0)
              ELSE cb.tcbConfiguracao END -- AS tcbConfiguracao
       , COALESCE(cb.tcbevtConfig, 0)     -- AS tcbevtConfig
       , COALESCE(vei.veiBitsAlertas, 0)  -- AS veiBitsAlertas
       , COALESCE(vei.veisluID, 0)        -- AS sluID
       , COALESCE(cb.tcbharID, 0)         -- AS harID
  FROM   TabEquipamento        eqp
  JOIN   TabVeiculos           vei ON vei.veiID = eqp.eqpveiID
  JOIN   TabComputadorBordo    cb  ON cb.tcbID  = eqp.eqptcbID
  JOIN   TabPacoteProduto      pp  ON pp.tppID  = eqp.eqptppID
  LEFT   JOIN TabCliente       cli ON cli.cliid = vei.veiProprietariocliID
  LEFT   JOIN TabClienteConfig cc  ON cc.clcCliID = cli.cliID
  WHERE  eqp.eqpAtivo = 1
  AND    vei.veiBloqueioSinal = 0
  AND    cb.tcbserie = $1
  AND    pp.tppIDProtocolo = $2;
END
$func$  LANGUAGE plpgsql VOLATILE STRICT COST 10000 ROWS 1
       **SET join_collapse_limit = 1**; -- see below!

筆記

COALESCE可以帶多個參數，無需嵌套：

COALESCE(NULLIF(cb.tcbConfiguracao, 0), cc.clcConfiguracaoBitsVeic, 0) AS tcbConfiguracao

我最終在上面使用的CASE表達式應該更快一些。

character作為數據類型可疑。它與 相同char(1)，我假設您已經意識到這一點。

在 PL/pgSQL 內部，在同一查詢中未引用的列別名僅用於文件。只有RETURNS TABLE子句中的名稱在函式外可見。

COST 100是使用者定義函式的預設值，可能不適合您的情況。10000 可能是一個更好的估計值，但除非您將此函式嵌套在外部查詢中，否則幾乎沒有任何效果。

我刪除了參數預設值（如所討論的）並創建了 function STRICT，因為無論如何查詢對於任何 NULL 輸入都不會返回任何內容。

我簡化了您的表格別名並進行了更多格式化，以使其更易於閱讀和使用。最後一點主要是品味和風格的問題。

回答問題

至於您的實際問題：PostgreSQL 中的查詢計劃器（優化器）沒有（直接）提示，就像其他 RDBMS 中一樣。Postgres Wiki 中有關“OptimizerHintsDiscussion”的詳細資訊。

PL/pgSQL 在內部使用準備好的語句。它將使用給定的輸入參數為每個 session的前幾次呼叫重新計劃函式體內的查詢。只有當它發現特定計劃的性能不如通用計劃時，它才會切換到通用計劃並保留它，這樣可以節省一些成本。

細節：

但是，您可以調整一些設置。特別是，如果您知道最佳查詢計劃，您可以強制 PostgresFROM按照給定的子句中的連接順序而不是嘗試重新排序（這對於許多表來說可能會變得很昂貴 - 您有 6 個表），通過設置join_collapse_limit. _ 這將降低規劃查詢的成本。如果操作正確，它將使前幾次呼叫更快。如果你搞砸了，性能當然會受到影響。

你可以把一個SET LOCAL作為第一個命令：

...
BEGIN
  SET LOCAL join_collapse_limit = 1;
  RETURN QUERY ...

更好的是，像我在上面所做的那樣，將它聲明為函式本身的一個屬性。函式體中的效果SET LOCAL將持續到事務結束，但是，根據文件：

該SET子句使指定的配置參數在函式進入時設置為指定值，然後在函式退出時恢復為之前的值。

顯然，您需要自己正確獲取子句****中的連接順序FROM。它必須適用於所有可能的參數組合。Postgres 不會嘗試優化。（STRICT修飾符簡化了一點，因為現在排除了 NULL 值。）

將具有最選擇性謂詞的表放在首位。

警告：請注意，升級到 Postgres 版本或數據庫中的任何重大更改後，此類優化可能會從有用變為阻礙。

更多詳細資訊的相關答案：

在旁邊

我假設您知道還有其他一些可能的影響可以使第一次呼叫比後續呼叫慢。就像填充記憶體記憶體和其他東西一樣：

郵件列表 pgsql-performance 中的人告訴我，在會話上第一次執行查詢預計需要更多時間，並建議我使用 pgpooler。也許這就是為什麼自動解釋說查詢只用了 0.230 毫秒，而函式本身需要 4.057 毫秒。

湯姆·萊恩寫道：

基本上，您應該期望任何 PG 會話執行的前幾個查詢都會比稍後執行的查詢慢。如果您無法修復您的應用程序以在合理的時間內保持會話打開，請使用連接池為您完成此操作（例如 pgpooler）。

引用自：https://dba.stackexchange.com/questions/123618

相關問答

Postgresql

我們可以為 JSONB 數據類型的鍵/值創建索引嗎？

  • February 7, 2022
檢視問答
Postgresql

帶有 SELECT 的 SQL 函式與帶有 RETURN QUERY SELECT 的 PLPGSQL 函式？

  • November 17, 2021
檢視問答
Postgresql

優化postgres功能

  • June 28, 2021
檢視問答
Postgresql

提高 GROUP BY 子句中的排序性能

  • June 8, 2020
檢視問答
Postgresql

PostgreSQL UDF（使用者定義函式）成本

  • October 23, 2018
檢視問答
Postgresql

X時如何結束功能

  • September 11, 2018
檢視問答