PostgreSQL limit的神奇作用详解

2024-08-01 05:48:57

最近碰到这样一个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) 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很低但实际执行时间却这么长？

让我们再看看执行计划：

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内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

Postgresql排序与limit组合场景性能极限优化详解

1 构造测试数据 create table tbl(id int, num int, arr int[]); create index idx_tbl_arr on tbl using gin (arr); create or replace function gen_rand_arr() returns int[] as $$ select array(select (1000*random())::int from generate_series(1,64)); $$ language sq
PostgreSQL limit的神奇作用详解

最近碰到这样一个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开始说起. 首先我们先看下这个表结构,比较简单,in
Mysql中explain作用详解

一.MYSQL的索引索引(Index):帮助Mysql高效获取数据的一种数据结构.用于提高查找效率,可以比作字典.可以简单理解为排好序的快速查找的数据结构. 索引的作用:便于查询和排序(所以添加索引会影响where 语句与 order by 排序语句). 在数据之外,数据库还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用数据.这样就可以在这些数据结构上实现高级查找算法.这些数据结构就是索引. 索引本身也很大,不可能全部存储在内存中,所以索引往往以索引文件的形式存储在磁盘上. 我们
bootstrap中的 form表单属性role="form"的作用详解

html 里面的 role 本质上是增强语义性,当现有的HTML标签不能充分表达语义性的时候,就可以借助role来说明.通常这种情况出现在一些自定义的组件上,这样可增强组件的可访问性.可用性和可交互性. role的作用是描述一个非标准的tag的实际作用.比如用div做button,那么设置div 的 role="button",辅助工具就可以认出这实际上是个button 比如, <div role="checkbox" aria-checked="c
JavaScript中闭包的写法和作用详解

1.什么是闭包闭包是有权访问另一个函数作用域的变量的函数. 简单的说,Javascript允许使用内部函数---即函数定义和函数表达式位于另一个函数的函数体内.而且,这些内部函数可以访问它们所在的外部函数中声明的所有局部变量.参数和声明的其他内部函数.当其中一个这样的内部函数在包含它们的外部函数之外被调用时,就会形成闭包. 2.变量的作用域要理解闭包,首先要理解变量的作用域. 变量的作用域无非就是两种:全局变量和局部变量. Javascript语言的特殊之处,就在于函数内部可以直接读取全局变
PHP中加速、缓存扩展的区别和作用详解(eAccelerator、memcached、xcache、APC )

PHP中有eAccelerator.memcached.xcache.APC 4个加速.缓存扩展,下面给大家介绍下其区别,一起看看吧! 折腾VPS的朋友,在安装好LNMP等Web运行环境后都会选择一些缓存扩展安装以提高PHP运行速度,常被人介绍的有 eAccelerator.memcached.xcache.Alternative PHP Cache这几个缓存扩展,它们之间有什么区别?分别的作用又是什么?我们如何选择?这是本文给于大家的答案. 1.eAccelerator eAccelerato
ssm框架下web项目,web.xml配置文件的作用(详解)

1. web.xml中配置了CharacterEncodingFilter,配置这个是拦截所有的资源并设置好编号格式. encoding设置成utf-8就相当于request.setCharacterEncoding("UTF-8"); foreEncoding设置成true就相当于response.setCharacterEncoding("UTF-8"); <filter> <filter-name>CharacterEncodingFi
Python中__init__.py文件的作用详解

__init__.py 文件的作用是将文件夹变为一个Python模块,Python 中的每个模块的包中,都有__init__.py 文件. 通常__init__.py 文件为空,但是我们还可以为它增加其他的功能.我们在导入一个包时,实际上是导入了它的__init__.py文件.这样我们可以在__init__.py文件中批量导入我们所需要的模块,而不再需要一个一个的导入. # package # __init__.py import re import urllib import sys impo
关于break和continue以及label的区别和作用(详解)

break和continue的区别和作用: break用于完全结束一个循环[一般只退出一重循环],跳出循环体执行循环后面的语句 continue是跳过当次循环中剩下的语句,执行下一次循环. 标号label 标号提供了一种简单的break语句所不能实现的控制循环的方法,当在循环语句中碰到break时, 不管其它控制变量,都会终止.但是,当你嵌套在几层循环中想退出循环时又怎么办呢?break只退出一重循环, 但你可以用标号label标出你想退出哪一个语句.规定标号label必需放在循环之前(意味着循
对Python模块导入时全局变量__all__的作用详解

Python中一个py文件就是一个模块,"__all__"变量是一个特殊的变量,可以在py文件中,也可以在包的__init__.py中出现. 1.在普通模块中使用时,表示一个模块中允许哪些属性可以被导入到别的模块中, 如:全局变量,函数,类.如下,test1.py和main.py test1.py __all__=["test"] def test(): print('----test-----') def test1(): print('----test1----
对python中的*args与**kwgs的含义与作用详解

在定义函数的时候参数通常会使用 *args与**kwgs,形参与实参的区别不再赘述,我们来解释一下这两个的作用. *args是非关键字参数,用于元组,**kw是关键字参数例如下面的代码 def foo(*args,**kwargs): print 'args is',args print 'kwargs is',kwargs foo(1,2) foo(k=1,w=2,a=3,r=4,g=5,s=6) foo(1,2,a=1,b=2,c=2) foo('a',1,None,a=1,b='2',c

PostgreSQL limit的神奇作用详解

相关推荐

随机推荐