PostgreSQL limit的神奇作用詳解
最近碰到這樣一個(gè)SQL引發(fā)的性能問(wèn)題，SQL內(nèi)容大致如下：
SELECT *
FROM t1
WHERE id = 999
AND (case $1
WHEN 'true' THEN
info = $2
ELSE info = $3 end) limit 1;
開(kāi)發(fā)反應(yīng)這條SQL加上limit 1之后過(guò)了一段時(shí)間從原先的索引掃描變成了全表掃描，一個(gè)簡(jiǎn)單的limit 1為何會(huì)產(chǎn)生這樣的影響，我只取一條數(shù)據(jù)不是應(yīng)該更快了嗎？
下面我們就從這條SQL開(kāi)始說(shuō)起。
首先我們先看下這個(gè)表結(jié)構(gòu)，比較簡(jiǎn)單，info列上有個(gè)索引，如下所示：
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列是沒(méi)有重復(fù)值的，這意味著無(wú)論where條件中傳入什么變量都肯定是能走索引掃描的。那為什么加上limit 1后會(huì)變成全表掃描呢？
我們先看看這條SQL之前正常的走索引的執(zhí)行計(jì)劃：
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)
而現(xiàn)在的執(zhí)行計(jì)劃卻是這樣的：
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反而更低，但實(shí)際時(shí)間竟然長(zhǎng)了這么多。而當(dāng)我們將日志中獲取的綁定變量值帶入SQL中再去查看執(zhí)行計(jì)劃時(shí)，仍然是走索引掃描。既然如此，那比較容易想到的就是plan cache導(dǎo)致的執(zhí)行計(jì)劃錯(cuò)誤了。
由于在PostgreSQL中執(zhí)行計(jì)劃緩存只是會(huì)話級(jí)別的，PostgreSQL在生成執(zhí)行計(jì)劃緩存前，會(huì)先走5次custom plan，然后記錄這5次總的custom plan的cost, 以及custom plan的次數(shù)，最后生成通用的generic plan。
以后，每次bind時(shí)，會(huì)根據(jù)緩存的執(zhí)行計(jì)劃以及給定的參數(shù)值計(jì)算一個(gè)COST，如果這個(gè)COST 小于前面存儲(chǔ)的custom plan cost的平均值，則使用當(dāng)前緩存的執(zhí)行計(jì)劃。如果這個(gè)COST大于前面存儲(chǔ)的custom plan cost的平均值，則使用custom plan(即重新生成執(zhí)行計(jì)劃)，同時(shí)custom plan的次數(shù)加1，custom plan總成本也會(huì)累加進(jìn)去。
既然如此，我們使用prepare語(yǔ)句再測(cè)試一次：
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次時(shí)出現(xiàn)了我們想要的結(jié)果！
可以看到前5次索引掃描的cost都是3.18，而全表掃描的cost卻是0.35，所以自然優(yōu)化器選擇了全表掃描，可為什么cost變低了反而時(shí)間更久了呢？解答這個(gè)問(wèn)題前我們先要來(lái)了解下limit子句的cost是如何計(jì)算的。
limit cost計(jì)算方法：
先從一個(gè)最簡(jiǎn)單的例子看起：
我們只取1條記錄，cost很低，時(shí)間也很少。
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了，似乎也很好理解，因?yàn)槲覀冊(cè)谶M(jìn)行l(wèi)imit前要使用where條件進(jìn)行一次數(shù)據(jù)過(guò)濾，所以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)
但當(dāng)我們換個(gè)條件時(shí)結(jié)果又不同了：
從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)
那么這個(gè)limit的cost究竟是如何計(jì)算的呢，為什么條件不同cost能差這么多呢？
下面給出limit cost計(jì)算方法：
limit_cost = ( N / B ) * A
N：表示limit取的數(shù)據(jù)，如limit 1則N=1；
B：表示估算得到的總記錄數(shù)；
A：表示估算的總成本。
例如上面cost=0.13的執(zhí)行計(jì)劃中，N = 1，B = 983582，A = 125915.11，那么limit cost便是：
(1/983582)*125915.11 = 0.128，即執(zhí)行計(jì)劃中顯示的0.13。
簡(jiǎn)而言之就是如果通過(guò)where條件篩選得到的行數(shù)越多，那么limit cost就會(huì)越低。
知道了這些我們?cè)倩剡^(guò)頭去看那條SQL就清楚了，因?yàn)閣here id = 999這個(gè)條件的數(shù)據(jù)比較多，這也就導(dǎo)致了即使是全表掃描limit cost也很低，甚至比索引掃描還低。
SELECT *
FROM t1
WHERE id = 999
AND (case $1
WHEN 'true' THEN
info = $2
ELSE info = $3 end) limit 1;
但是需要注意的是，我們即使使用explain analyze看到的執(zhí)行計(jì)劃中的cost也是一個(gè)估算值，并不是實(shí)際值，盡管這個(gè)和實(shí)際值差距不會(huì)很大，但如果cost本身就很小，那么還是會(huì)帶來(lái)一點(diǎn)誤解的。
例如前面的SQL我想要提高全表掃描的limit cost讓其大于索引掃描，這樣優(yōu)化器便會(huì)一直選擇索引掃描了，于是我將limit 1改成limit 100（即增加N的值），但是卻仍然沒(méi)有起作用：
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次傳入?yún)?shù)得到的，可以看到全表掃描的cost是8.9，而索引掃描是5.58，那應(yīng)該不會(huì)選擇cost更高的8.9?。?/p>
而當(dāng)我們?nèi)ジ檶?shí)際的cost就可以發(fā)現(xiàn)：
$1 = {magic = 195726186, raw_parse_tree = 0x15df470,
query_string = 0x16d65b8 "PREPARE p1(varchar,varchar,varchar) as\n select\n t.scm_sale_customer_id,\n t.scm_customer_id\n from\n scm_bind t\n 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}
實(shí)際索引掃描的cost大約數(shù)10.58，和執(zhí)行計(jì)劃中顯示的還是有一定差距的。
讓我們言歸正傳，既然知道了為什么全表掃描的limit cost更低，我們?cè)賮?lái)解決下一個(gè)問(wèn)題：為什么cost很低但實(shí)際執(zhí)行時(shí)間卻這么長(zhǎng)？
讓我們?cè)倏纯磮?zhí)行計(jì)劃：
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)
仔細(xì)觀察可以發(fā)現(xiàn)，原先應(yīng)該作為索引的info列的過(guò)濾條件，竟然整個(gè)作為了filter條件去進(jìn)行數(shù)據(jù)過(guò)濾了。
那么最后的問(wèn)題就出現(xiàn)在這個(gè)where條件中的case when表達(dá)式了，因?yàn)樵赾ase when表達(dá)式進(jìn)行過(guò)濾前，綁定變量還沒(méi)有傳入實(shí)際的值，而優(yōu)化器對(duì)于不確定的值自然無(wú)法選擇是否去走索引了，這里不得不吐槽一下這種寫法。。。
因此對(duì)于優(yōu)化器計(jì)算limit cost時(shí)，只知道where id = 999會(huì)得到大量的數(shù)據(jù)，而無(wú)法判斷后面的case when里面會(huì)得到多少數(shù)據(jù)，因此雖然后面的條件只會(huì)得到很少一部分?jǐn)?shù)據(jù)，但是優(yōu)化器生成limit cost時(shí)估算得到的總記錄數(shù)B只是根據(jù)id = 999去判斷，導(dǎo)致估算的cost很低，但實(shí)際卻只得到很少的數(shù)據(jù)，要去表中過(guò)濾大量數(shù)據(jù)。
不得不感嘆這個(gè)“簡(jiǎn)單”的SQL竟然包含著這么多知識(shí)。
到此這篇關(guān)于PostgreSQL limit的神奇作用詳解的文章就介紹到這了,更多相關(guān)PostgreSQL limit內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
postgresql合并string_agg函數(shù)的實(shí)例
這篇文章主要介紹了postgresql合并string_agg函數(shù)的實(shí)例，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
PostgreSQL教程(十二)：角色和權(quán)限管理介紹
這篇文章主要介紹了PostgreSQL教程(十二)：角色和權(quán)限管理介紹,本文講解了數(shù)據(jù)庫(kù)角色、角色屬性、權(quán)限、角色成員,需要的朋友可以參考下2015-05-05
PostgreSQL設(shè)置時(shí)區(qū)、時(shí)間/日期函數(shù)匯總大全
PostgreSQL是一款簡(jiǎn)介而又性能強(qiáng)大的數(shù)據(jù)庫(kù)應(yīng)用程序,其在日期時(shí)間數(shù)據(jù)方面所支持的功能也都非常給力,這篇文章主要給大家介紹了關(guān)于PostgreSQL設(shè)置時(shí)區(qū)、時(shí)間/日期函數(shù)的相關(guān)資料,需要的朋友可以參考下2023-09-09
psql是PostgreSQL的一個(gè)命令行交互式客戶端工具，它具有非常豐富的功能，類似于Oracle的命令行工具sqlplus，這篇文章主要介紹了PostgreSQL-psql命令詳解,需要的朋友可以參考下2023-07-07
Mybatis調(diào)用PostgreSQL存儲(chǔ)過(guò)程實(shí)現(xiàn)數(shù)組入?yún)鬟f
這篇文章主要介紹了mybatis調(diào)用postgresql自定義函數(shù)傳遞數(shù)組參數(shù)的解決方案,具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2016-11-11
這篇文章主要介紹了PostgreSQL 定義返回表函數(shù)的操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
這篇文章主要介紹了PostgreSQL的多種備份方法,包括邏輯備份和物理備份,以及歸檔日志備份,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2025-02-02
PostgreSQL已經(jīng)存在的表怎么設(shè)置id自增長(zhǎng)詳解
這篇文章主要介紹了如何為已有的PostgreSQL表設(shè)置ID字段為自增,包括創(chuàng)建序列、設(shè)置默認(rèn)值、可能的表結(jié)構(gòu)修改以及重置序列的步驟,需要的朋友可以參考下2025-03-03
關(guān)于PostgreSql數(shù)據(jù)庫(kù)與mysql數(shù)據(jù)庫(kù)的不同點(diǎn)以及注意事項(xiàng)
PostgreSQL和MySQL是兩種流行的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)(RDBMS),它們都可以用來(lái)存儲(chǔ)和管理數(shù)據(jù),但是它們?cè)谀承┓矫嬗兴煌?下面這篇文章主要給大家介紹了關(guān)于PostgreSql數(shù)據(jù)庫(kù)與mysql數(shù)據(jù)庫(kù)的不同點(diǎn)以及注意事項(xiàng)的相關(guān)資料,需要的朋友可以參考下2023-05-05
PostgreSQL 恢復(fù)誤刪數(shù)據(jù)的操作
這篇文章主要介紹了PostgreSQL 恢復(fù)誤刪數(shù)據(jù)的操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01