關于PostgreSQL中的plan cache(或預備語句)

SQL的執行大緻分為解析，優化和執行幾個步驟。解析和優化的結果是執行計劃，通常相同的SQL語句要被執行無數遍，并且每次執行很可能采用的是相同的執行計劃。生成執行計劃的過程是要花費時間的，特别是對一些複雜SQL。那麼如果把執行計劃緩存起來，下次執行的時候跳過優化這一步不就可以提高性能了嗎？不錯，這就是所謂的plan cache。

嚴格來講，PostgreSQL并沒有提供plan cache這樣的功能，PG提供的是預備語言(preparedstatement)。關鍵差別在于，預備語言要求應用給這個語句起個名字，之後應用再通過這個名字請求服務端執行，并且由應用負責回收。而plan cache對應用應該是透明的。

PG的預備語言的特點可簡單概括如下：

1)預備語言的有效範圍

  預備語言是會話級别而不是全局的，是以預備語言的名稱必須在會話内是唯一的。

2）建立預備語句的方式

建立一個預備語句主要有下幾種方式

  a)PREPARE語句

  b)在"擴充查詢"協定中使用命名的語句

  c)SPI_prepare_plan()

應用程式都是通過API驅動通路PG後端的。API驅動一般會有條件的通過方式b)使用PG的預備語言功能。

比如對pgjdbc，通常是使用擴充查詢協定的未命名語句。但在滿足以下條件時，使用命名語句。

i)應用程式使用PreparedStatement，且

ii)該PreparedStatement被執行的次數達到了PrepareThreshold（預設是5）

參考：http://jdbc.postgresql.org/documentation/head/server-prepare.html

對Npgsql，通常是使用簡單查詢協定。但在滿足以下條件時，使用擴充查詢協定的命名語句。

i)應用主動調了Prepare()方法，或

ii)應用設定了AlwaysPrepare參數

3)預備語句與查詢計劃的關聯

a)無參的預備語句

  對于無參的預備語句,在第一次執行預備語句時生成執行計劃，以後的執行會重用這個執行計劃。

b)帶參的預備語句

  對于帶參的預備語句，最優的執行計劃可能會因參數值而不同，每次都根據傳入參數生成新的執行計劃又可能造成無謂的浪費。對此，PG采用了以下政策:

i)前5次執行預備語句，每次都根據參數生成新的執行計劃，稱為custom plan。

ii)第6次執行預備語句，生成一個不依賴參數的執行計劃并儲存起來，稱為generic plan。如果generic plan的代價小于之前所有custom plan的平均代價的1.1倍，則采用generic plan，否則根據參數生成新的custom plan。

iii)再以後的執行與第6次執行類似，除了不用生成generic plan，因為第6次執行已經生成過generic plan了。

*)上面的描述把執行計劃失效等一些瑣碎事情略過了

4)預備語句的釋放

預備語句使用完畢後需要主動釋放預備語言，釋放方法和建立方法是相對應的

  a)DEALLOCATE

  b)通過"擴充查詢"協定發送CLOSE消息

  c)SPI_freeplan()

預備語言被釋放時，會順便釋放與之關聯的generic plan。(custom plan在每次執行結束後就釋放了,是以不用額外釋放)。如果應用忘了釋放預備語言，預備語言會一緻持續到會話結束。

JDBC中有個StatmentCache功能，可以把已經close掉的PreparedStatement緩存起來，以備下次使用。在使用連接配接池時，即使連接配接關掉了，緩存的PreparedStatement依然有效，因為緩存的PreparedStatement是挂在實體連接配接而不是邏輯連接配接上。但是，目前的pgjdbc并沒有實作StatmentCache，是以這條路走不通。(去年我們在Symfoware的jdbc中倒是實作了StatementCache)

然而PG倒是在plsql中實作了plan cache。plsql的解釋器會把SQL以預備語句的方式執行，并在會話裡儲存這些預備語句。

下面以分區表的單點查詢作為例子示範一下plsql的plan cache的效果。

這是分區表的定義：

點選(此處)折疊或打開

db1000=# \d maintb;

       Table "public.maintb"

 Column | Type | Modifiers

--------+---------------+-----------

 id | integer |

 name | character(10) |

Triggers:

    insert_maintb_trigger BEFORE INSERT ON maintb FOR EACH ROW EXECUTE PROCEDURE maintb_insert_trigger()

Number of child tables: 1000 (Use \d+ to list them.)

*）詳細表定義參照：http://blog.chinaunix.net/xmlrpc.php?r=blog/article&uid=20726500&id=4732253

由于有1000個分區，是以生成執行計劃很耗時，要56毫秒。

db1000=# explain select * from maintb where id=1;

                                    QUERY PLAN

-----------------------------------------------------------------------------------

 Append (cost=0.00..44.73 rows=12 width=18)

   -> Seq Scan on maintb (cost=0.00..0.00 rows=1 width=48)

         Filter: (id = 1)

   -> Bitmap Heap Scan on childtb_1 (cost=4.50..44.73 rows=11 width=15)

         Recheck Cond: (id = 1)

         -> Bitmap Index Scan on childtb_idx_1 (cost=0.00..4.50 rows=11 width=0)

               Index Cond: (id = 1)

(7 rows)

Time: 56.631 ms

執行了2次，時間都是57毫秒。大部分時間花在生成執行計劃上。

db1000=# select * from maintb where id=1;

 id | name

----+------------

  1 | abcde12345

  1 | abcd12345

(9 rows)

Time: 57.315 ms

Time: 57.665 ms

現在定義一個plsql的存儲過程。

db1000=# CREATE FUNCTION func1() RETURNS TABLE(id int, name char(10)) AS $$

db1000$# BEGIN

db1000$# RETURN QUERY select * from maintb where maintb.id=1;

db1000$# END;

db1000$# $$ LANGUAGE plpgsql;

CREATE FUNCTION

Time: 4.313 ms

通過這個存儲過程完成和前面相同的任務。第一次執行存儲過程，耗時48毫秒。這個時間包含了執行計劃的生成。

db1000=# select * from func1();

Time: 48.998 ms

第2次執行，plsql會直接利用上次執行生成的執行計劃，是以隻花了2毫秒。

Time: 2.366 ms

http://blog.163.com/digoal@126/blog/static/1638770402012112452432251/  (*)

http://www.postgresql.org/docs/9.3/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING

src/backend/tcop/postgres.c

src/backend/utils/cache/plancache.c

*)其實關于PG的Plan cache，德哥的這篇部落格講的更細。