PostgreSQL limit的神奇作用詳解

2022-09-16 22:04:44

最近碰到這樣一個SQL引發的效能問題，SQL內容大致如下：

SELECT *
FROM t1
WHERE id = 999
AND (case $1
    WHEN 'true' THEN
    info = $2
    ELSE info = $3 end) limit 1;

開發反應這條SQL加上limit 1之後過了一段時間從原先的索引掃描變成了全表掃描，一個簡單的limit 1為何會產生這樣的影響，我只取一條資料不是應該更快了嗎？

下面我們就從這條SQL開始說起。

首先我們先看下這個表結構，比較簡單，info列上有個索引，如下所示：

bill=# d t1
                            Table "public.t1"
  Column  |            Type             | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
 id       | integer                     |           |          |
 info     | text                        |           |          |
 crt_time | timestamp without time zone |           |          |
Indexes:
    "idx_t1" btree (info)

並且info列是沒有重複值的，這意味著無論where條件中傳入什麼變數都肯定是能走索引掃描的。那為什麼加上limit 1後會變成全表掃描呢？

我們先看看這條SQL之前正常的走索引的執行計劃：

                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..3.18 rows=1 width=45) (actual time=0.027..0.027 rows=0 loops=1)
   ->  Index Scan using idx_t1 on t1  (cost=0.56..3.18 rows=1 width=45) (actual time=0.025..0.026 rows=0 loops=1)
         Index Cond: (info = 'bill'::text)
         Filter: (id = 999)
 Planning Time: 0.158 ms
 Execution Time: 0.057 ms
(6 rows)

而現在的執行計劃卻是這樣的：

 Limit  (cost=0.00..0.35 rows=1 width=45) (actual time=487.564..487.564 rows=0 loops=1)
   ->  Seq Scan on t1  (cost=0.00..170895.98 rows=491791 width=45) (actual time=487.562..487.562 rows=0 loops=1)
         Filter: ((id = 999) AND CASE $1 WHEN 'true'::text THEN (info = $2) ELSE (info = $3) END)
         Rows Removed by Filter: 6000000
 Planning Time: 0.119 ms
 Execution Time: 487.595 ms
(6 rows)

奇怪的是下面的全表掃描加上limit後cost反而更低，但實際時間竟然長了這麼多。而當我們將紀錄檔中獲取的繫結變數值帶入SQL中再去檢視執行計劃時，仍然是走索引掃描。既然如此，那比較容易想到的就是plan cache導致的執行計劃錯誤了。

由於在PostgreSQL中執行計劃快取只是對談級別的，PostgreSQL在生成執行計劃快取前，會先走5次custom plan，然後記錄這5次總的custom plan的cost, 以及custom plan的次數，最後生成通用的generic plan。

以後，每次bind時，會根據快取的執行計劃以及給定的引數值計算一個COST，如果這個COST 小於前面儲存的custom plan cost的平均值，則使用當前快取的執行計劃。如果這個COST大於前面儲存的custom plan cost的平均值，則使用custom plan(即重新生成執行計劃)，同時custom plan的次數加1，custom plan總成本也會累加進去。

既然如此，我們使用prepare語句再測試一次：

bill=# prepare p1 as select * from t1 where id = 999
bill-# and (case $1 when 'true' then info = $2 else info = $3 end)  limit 1;
PREPARE
bill=# explain analyze execute p1('true','bill','postgres');
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..3.18 rows=1 width=45) (actual time=0.831..0.831 rows=0 loops=1)
   ->  Index Scan using idx_t1 on t1  (cost=0.56..3.18 rows=1 width=45) (actual time=0.830..0.830 rows=0 loops=1)
         Index Cond: (info = 'bill'::text)
         Filter: (id = 999)
 Planning Time: 0.971 ms
 Execution Time: 0.889 ms
(6 rows)
bill=# explain analyze execute p1('true','bill','postgres');
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..3.18 rows=1 width=45) (actual time=0.038..0.039 rows=0 loops=1)
   ->  Index Scan using idx_t1 on t1  (cost=0.56..3.18 rows=1 width=45) (actual time=0.037..0.037 rows=0 loops=1)
         Index Cond: (info = 'bill'::text)
         Filter: (id = 999)
 Planning Time: 0.240 ms
 Execution Time: 0.088 ms
(6 rows)
bill=# explain analyze execute p1('true','bill','postgres');
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..3.18 rows=1 width=45) (actual time=0.036..0.036 rows=0 loops=1)
   ->  Index Scan using idx_t1 on t1  (cost=0.56..3.18 rows=1 width=45) (actual time=0.035..0.035 rows=0 loops=1)
         Index Cond: (info = 'bill'::text)
         Filter: (id = 999)
 Planning Time: 0.136 ms
 Execution Time: 0.076 ms
(6 rows)
bill=# explain analyze execute p1('true','bill','postgres');
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..3.18 rows=1 width=45) (actual time=0.051..0.051 rows=0 loops=1)
   ->  Index Scan using idx_t1 on t1  (cost=0.56..3.18 rows=1 width=45) (actual time=0.049..0.050 rows=0 loops=1)
         Index Cond: (info = 'bill'::text)
         Filter: (id = 999)
 Planning Time: 0.165 ms
 Execution Time: 0.091 ms
(6 rows)
bill=# explain analyze execute p1('true','bill','postgres');
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..3.18 rows=1 width=45) (actual time=0.027..0.027 rows=0 loops=1)
   ->  Index Scan using idx_t1 on t1  (cost=0.56..3.18 rows=1 width=45) (actual time=0.025..0.026 rows=0 loops=1)
         Index Cond: (info = 'bill'::text)
         Filter: (id = 999)
 Planning Time: 0.158 ms
 Execution Time: 0.057 ms
(6 rows)
bill=# explain analyze execute p1('true','bill','postgres');
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.35 rows=1 width=45) (actual time=487.564..487.564 rows=0 loops=1)
   ->  Seq Scan on t1  (cost=0.00..170895.98 rows=491791 width=45) (actual time=487.562..487.562 rows=0 loops=1)
         Filter: ((id = 999) AND CASE $1 WHEN 'true'::text THEN (info = $2) ELSE (info = $3) END)
         Rows Removed by Filter: 6000000
 Planning Time: 0.119 ms
 Execution Time: 487.595 ms
(6 rows)

果然在第6次時出現了我們想要的結果！

可以看到前5次索引掃描的cost都是3.18，而全表掃描的cost卻是0.35，所以自然優化器選擇了全表掃描，可為什麼cost變低了反而時間更久了呢？解答這個問題前我們先要來了解下limit子句的cost是如何計算的。

limit cost計算方法：

先從一個最簡單的例子看起：

我們只取1條記錄，cost很低，時間也很少。

bill=# explain analyze select * from t1 limit 1;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.02 rows=1 width=45) (actual time=0.105..0.106 rows=1 loops=1)
   ->  Seq Scan on t1  (cost=0.00..110921.49 rows=5997449 width=45) (actual time=0.103..0.103 rows=1 loops=1)
 Planning Time: 0.117 ms
 Execution Time: 0.133 ms
(4 rows)

加上where條件試試呢？

cost一下子變成3703.39了，似乎也很好理解，因為我們在進行limit前要使用where條件進行一次資料過濾，所以cost變得很高了。

bill=# explain analyze select * from t1 where id = 1000 limit 1;
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..3703.39 rows=1 width=45) (actual time=0.482..0.483 rows=1 loops=1)
   ->  Seq Scan on t1  (cost=0.00..125915.11 rows=34 width=45) (actual time=0.480..0.481 rows=1 loops=1)
         Filter: (id = 1000)
         Rows Removed by Filter: 1008
 Planning Time: 0.117 ms
 Execution Time: 0.523 ms
(6 rows)

但當我們換個條件時結果又不同了：

從where id=1000變成 id=999，cost竟然一下子又降低到0.13了，似乎找到了前面全表掃描的limit cost比索引掃描還低的原因了。

bill=# explain analyze select * from t1 where id = 999 limit 1;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.13 rows=1 width=45) (actual time=0.041..0.042 rows=1 loops=1)
   ->  Seq Scan on t1  (cost=0.00..125915.11 rows=983582 width=45) (actual time=0.040..0.040 rows=1 loops=1)
         Filter: (id = 999)
         Rows Removed by Filter: 107
 Planning Time: 0.114 ms
 Execution Time: 0.079 ms
(6 rows)

那麼這個limit的cost究竟是如何計算的呢，為什麼條件不同cost能差這麼多呢？

下面給出limit cost計算方法：

limit_cost = ( N / B ) * A

N：表示limit取的資料，如limit 1則N=1；

B：表示估算得到的總記錄數；

A：表示估算的總成本。

例如上面cost=0.13的執行計劃中，N = 1，B = 983582，A = 125915.11，那麼limit cost便是：

(1/983582)*125915.11 = 0.128，即執行計劃中顯示的0.13。

簡而言之就是如果通過where條件篩選得到的行數越多，那麼limit cost就會越低。

知道了這些我們再回過頭去看那條SQL就清楚了，因為where id = 999這個條件的資料比較多，這也就導致了即使是全表掃描limit cost也很低，甚至比索引掃描還低。

SELECT *
FROM t1
WHERE id = 999
AND (case $1
    WHEN 'true' THEN
    info = $2
    ELSE info = $3 end) limit 1;

但是需要注意的是，我們即使使用explain analyze看到的執行計劃中的cost也是一個估算值，並不是實際值，儘管這個和實際值差距不會很大，但如果cost本身就很小，那麼還是會帶來一點誤解的。

例如前面的SQL我想要提高全表掃描的limit cost讓其大於索引掃描，這樣優化器便會一直選擇索引掃描了，於是我將limit 1改成limit 100（即增加N的值），但是卻仍然沒有起作用：

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.56..5.58 rows=1 width=53) (actual time=0.049..0.051 rows=1 loops=1)
-> Index Scan using idx_scm_bind_scm_customer_id_index on scm_bind t (cost=0.56..5.58 rows=1 width=53) (actual time=0.049..0.050 rows=1 loops=1)
Index Cond: ((scm_customer_id)::text = 'wmGAgeDQAAXcpcw9QWkDOUQsIDI1xOqQ'::text)
Filter: ((bind_status)::text = '2'::text)
Planning Time: 0.160 ms
Execution Time: 0.072 ms
(6 rows)
Time: 0.470 ms
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..8.90 rows=100 width=53) (actual time=1047.859..16654.360 rows=1 loops=1)
-> Seq Scan on scm_bind t (cost=0.00..552392.00 rows=6208050 width=53) (actual time=1047.858..16654.357 rows=1 loops=1)
Filter: (((bind_status)::text = '2'::text) AND CASE $1 WHEN 'client'::text THEN ((scm_customer_id)::text = ($2)::text) ELSE ((scm_customer_id)::text = ($3)::text) END)
Rows Removed by Filter: 12169268
Planning Time: 0.147 ms
Execution Time: 16654.459 ms
(6 rows)
Time: 16654.924 ms (00:16.655)

下面的全表掃描是第6次傳入引數得到的，可以看到全表掃描的cost是8.9，而索引掃描是5.58，那應該不會選擇cost更高的8.9啊？

而當我們去跟蹤實際的cost就可以發現：

$1 = {magic = 195726186, raw_parse_tree = 0x15df470,
query_string = 0x16d65b8 "PREPARE p1(varchar,varchar,varchar) asn selectn t.scm_sale_customer_id,n t.scm_customer_idn fromn scm_bind tn where t.bind_status = '2'n and (case $1 when 'client' then scm_customer_id ="..., commandTag = 0x95b5ba "SELECT", param_types = 0x16d66c8, num_params = 3, parserSetup = 0x0, parserSetupArg = 0x0, cursor_options = 256, fixed_result = true,
resultDesc = 0x16d66e8, context = 0x15df250, query_list = 0x16dbe80, relationOids = 0x16e6138, invalItems = 0x0, search_path = 0x16e6168, query_context = 0x16dbd70, rewriteRoleId = 10,
rewriteRowSecurity = true, dependsOnRLS = false, gplan = 0x16ff668, is_oneshot = false, is_complete = true, is_saved = true, is_valid = true, generation = 6, next_saved = 0x0,
generic_cost = 8.8979953447539888, total_custom_cost = 52.899999999999999, num_custom_plans = 5}

實際索引掃描的cost大約數10.58，和執行計劃中顯示的還是有一定差距的。

讓我們言歸正傳，既然知道了為什麼全表掃描的limit cost更低，我們再來解決下一個問題：為什麼cost很低但實際執行時間卻這麼長？

讓我們再看看執行計劃：

Limit  (cost=0.00..0.35 rows=1 width=45) (actual time=487.564..487.564 rows=0 loops=1)
   ->  Seq Scan on t1  (cost=0.00..170895.98 rows=491791 width=45) (actual time=487.562..487.562 rows=0 loops=1)
         Filter: ((id = 999) AND CASE $1 WHEN 'true'::text THEN (info = $2) ELSE (info = $3) END)
         Rows Removed by Filter: 6000000
 Planning Time: 0.119 ms
 Execution Time: 487.595 ms
(6 rows)

仔細觀察可以發現，原先應該作為索引的info列的過濾條件，竟然整個作為了filter條件去進行資料過濾了。

那麼最後的問題就出現在這個where條件中的case when表示式了，因為在case when表示式進行過濾前，繫結變數還沒有傳入實際的值，而優化器對於不確定的值自然無法選擇是否去走索引了，這裡不得不吐槽一下這種寫法。。。

因此對於優化器計算limit cost時，只知道where id = 999會得到大量的資料，而無法判斷後面的case when裡面會得到多少資料，因此雖然後面的條件只會得到很少一部分資料，但是優化器生成limit cost時估算得到的總記錄數B只是根據id = 999去判斷，導致估算的cost很低，但實際卻只得到很少的資料，要去表中過濾大量資料。

不得不感嘆這個“簡單”的SQL竟然包含著這麼多知識。

到此這篇關於PostgreSQL limit的神奇作用詳解的文章就介紹到這了,更多相關PostgreSQL limit內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

PostgreSQL limit的神奇作用詳解

熱門文章