PG 優化器的精彩

我們先看下面sql：[code]
postgres=# SELECT
postgres-# (
postgres(#         SELECT string_agg(x, '')
postgres(#         FROM (
postgres(#                 SELECT chr(ascii('a') + floor(random() * 26)::integer)
postgres(#                 FROM generate_series(1, 40 + b * 0) as f(g)
postgres(#         ) AS y(x)
postgres(# ) AS result
postgres-# FROM generate_series(1,5) as a(b);
                  result                  
------------------------------------------
oltxgwlsjdueshyjeiydcasfslbnpjzevsbcpnuz
rpdjxcsbkrfnrytkjvxzfweapgcftxelnhulknmv
esiwrchbxeacbfdqlfwfdaqqilbsyondhwayyhzw
maynfberhaxkaobjzdbysobzlcyjkyfwyejegowo
ptzphazgeaewpgwaivkttqpruzwbnspcmostprau
(5 DD？？？？)

postgres=# SELECT
(
        SELECT string_agg(x, '')
        FROM (
                SELECT chr(ascii('a') + floor(random() * 26)::integer)
                FROM generate_series(1, 40 ) as f(g)      
        ) AS y(x)
) AS result
FROM generate_series(1,5) as a(b);
                  result                  
------------------------------------------
sfrhmniujsncidudcqeqjutvjmpzeptwvlehymci
sfrhmniujsncidudcqeqjutvjmpzeptwvlehymci
sfrhmniujsncidudcqeqjutvjmpzeptwvlehymci
sfrhmniujsncidudcqeqjutvjmpzeptwvlehymci
sfrhmniujsncidudcqeqjutvjmpzeptwvlehymci
[/code]上面的sql 返回了5筆長度為40的隨機的字串。

下面的sql 返回了5筆重複的字串。  在我們的印象裡，b*0 =0 40+0 =40 加與不加結果應該是一樣的。

實際上卻相差很遠。

從下面的sql 看似乎裡面的隨機數random（） 只執行了一次，或者說是快取了結果集，導致外面的序列，重複的輸出了5次。

我們看看他們的執行計劃：[code]
postgres=# explain SELECT
postgres-# (
postgres(#         SELECT string_agg(x, '')
postgres(#         FROM (
postgres(#                 SELECT chr(ascii('a') + floor(random() * 26)::integer)
postgres(#                 FROM generate_series(1, 40 + b * 0) as f(g)
postgres(#         ) AS y(x)
postgres(# ) AS result
postgres-# FROM generate_series(1,5) as a(b);
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
Function Scan on generate_series a  (cost=0.00..37530.00 rows=1000 width=4)
   SubPlan 1
     ->  Aggregate  (cost=37.51..37.52 rows=1 width=32)
           ->  Function Scan on generate_series f  (cost=0.01..25.01 rows=1000 width=0)
(4 DD？？？？)

postgres=#            
explain SELECT
(
        SELECT string_agg(x, '')
        FROM (
                SELECT chr(ascii('a') + floor(random() * 26)::integer)
                FROM generate_series(1, 40 ) as f(g)      
        ) AS y(x)
) AS result
FROM generate_series(1,5) as a(b);
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
Function Scan on generate_series a  (cost=37.52..47.52 rows=1000 width=0)
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=37.50..37.51 rows=1 width=32)
           ->  Function Scan on generate_series f  (cost=0.00..25.00 rows=1000 width=0)
(4 DD？？？？)
[/code]兩個sql 的執行計劃是不同的。
pg的優化器 認為第二個sql 中，裡面的subquery  是不依賴於外面的sql，所以優化器對他進行了評估計算固定為initplain
這個時候，跟裡面的函式random 的volatile 跟stable 屬性是無關的。
這就要引入pg的函式或者表示式的volatile  很stable 屬性了
所謂的volatile 就是不穩定，即這個函式對同一個輸入，可能產生不同的輸出。

而對於第一個sql ，subquery 應用到了外面sql的變數，導致針對外面sql的每一行都要計算裡面的subquery。

我們修改為每次輸出100調記錄， 第二個sql 用時0.77ms 第一個sql用4.13 ms
也可以看出第一個sql做了更多的活。


針對  函式的volatile 和stable 的屬性，再準備下開新帖說明啦，今天到這裡了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/133735/viewspace-739657/，如需轉載，請註明出處，否則將追究法律責任。