PostgreSQL limit的奇异作用详解
最近碰到这样一个SQL引发的性能问题，SQL内容大致以下：
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列上有个索引，以下所示：
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之前正常的走索引的履行计划：
——————————————————————————————————————
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)
而现在的履行计划却是这样的：
-> 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-# 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很低，时间也很少。
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变得很高了。
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比索引扫描还低的缘由了。
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也很低，乃至比索引扫描还低。
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的值），但是却依然没有起作用：
——————————————————————————————————————————————————
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就能够发现：
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}
实际索引扫描的cost大约数10.58，和履行计划中显示的或者有一定差距的。
让我们言归正传，既然知道了为何全表扫描的limit cost更低，我们再来解决下一个问题：为何cost很低但实际履行时间却这么长？
让我们再看看履行计划：
-> 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内容请搜索之前的文章或继续浏览下面的相关文章希望大家以后多多支持！
文章来源：丸子建站
文章标题：PostgreSQL limit的奇异作用详解
https://www.wanzijz.com/view/60972.html
