iis服务器助手广告广告

扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 后端开发 > 其他教程 >PostgreSQLlimit的神奇作用详解
  • 664

0
分享到

PostgreSQLlimit的神奇作用详解

2024-04-02 19:04:59 664人浏览 泡泡鱼
摘要

最近碰到这样一个sql引发的性能问题，SQL内容大致如下： SELECT * FROM t1 WHERE id = 999 AND (case $1 WHEN

最近碰到这样一个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内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！

点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: PostgreSQLlimit的神奇作用详解

本文链接: https://www.lsjlt.com/news/167727.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档
去做题
猜你喜欢
  • PostgreSQLlimit的神奇作用详解
    最近碰到这样一个SQL引发的性能问题，SQL内容大致如下： SELECT * FROM t1 WHERE id = 999 AND (case $1 WHEN...
    99+
    2024-04-02
  • 详解C++语言中std::array的神奇用法
    目录概述自动推导数组大小用函数返回std::array编译期字面量数值合法性校验编译期生成数组截取子数组拼接多个数组编译期拼接字符串展望C++20——打破更多的枷锁尾注概述 std:...
    99+
    2024-04-02
  • Python中弱引用的神奇用法与原理详解
    目录背景典型用法工作原理实现细节总结背景 开始讨论弱引用（ weakref ）之前，我们先来看看什么是弱引用？它到底有什么作用？ 假设我们有一个多线程程序，并发处...
    99+
    2024-04-02
  • Windows下神奇的SysNative文件夹的作用
    其实%WINDIR%\SysNative文件夹是不存在的，它只是64位Windows系统提供的一种重定向机制。 我们已经知道64位Windows通过System32和SysWoW64两件文件夹来区分64位和32位的系统文...
    99+
    2023-06-16
    Windows SysNative 文件夹 作用
  • 详解Python中神奇的字符串驻留机制
    目录1 什么是字符串驻留机制2 如何使用字符串驻留机制3 简单拼接驻留， 运行时不驻留4 总结5 全部代码今天有一个初学者在学习Python的时候又整不会了。 原因是以下代码： a ...
    99+
    2023-05-14
    Python字符串驻留机制 Python字符串驻留 Python字符串
  • JAVA并发中VOLATILE关键字的神奇之处详解
    并发编程中的三个概念: 1.原子性 在Java中，对基本数据类型的变量的读取和赋值操作是原子性操作，即这些操作是不可被中断的，要么执行，要么不执行。 2.可见性 对于可见性，Jav...
    99+
    2024-04-02
  • 用Python 制作微信全家福，这么神奇的吗？
    前聊天刷朋友圈，晒了一张下面的照片，微信好友墙，一张大图片，展示了自己所有好友的照片！效果图如下，出于隐私，这里作了模糊处理。...
    99+
    2023-06-02
  • 条件与循环的相互作用，Vue 应用的神奇世界！
    v-if 和 v-else 指令 v-if 指令允许在基于表达式求值为 true 时显示元素。 v-else 指令用于在 v-if 表达式求值为 false 时显示备用元素。 示例： <template> <di...
    99+
    2024-04-02
  • 一些Python 5行代码的神奇操作汇总
    目录1、古典兔子问题2、加法计算器3、循环问答4、实现一个简单的服务器5、九九乘法表16、九九乘法表27、逆序打印数字8、生成词云9、快速生成二维码10、实现批量抠图总结Python...
    99+
    2024-04-02
  • 基于CUDAoutofmemory的一种神奇解决方式
    目录CUDA out of memory的解决CUDA error: out of memory问题解决思路溯寻总结CUDA out of memory的解决 我的输入样本维度是(1...
    99+
    2023-02-22
    CUDA out of memory CUDA out of memory的解决
  • 带你了解Python语言的神奇世界
    目录一、特点二、语法三、表达式四、变量五、缩进六、数据类型七、Python值得学习嘛？Python是世界上最流行的解释型编程语言之一。 由 Guido van Rossum 设计，作...
    99+
    2024-04-02
  • ASP 入门指南：破解类的神奇世界
    理解破解类 ASP 页面 破解类 ASP 页面以 ".asp" 为扩展名，与标准 HTML 页面相似。然而，它们包含额外的 ASP 代码，这些代码由服务器处理并在响应中生成 HTML 代码。ASP 代码通常包含在 <% %> ...
    99+
    2024-04-02
  • 发现Unix系统下NumPy的神奇应用
    NumPy是一个强大的Python数学库，提供了高效的数组操作工具和数学函数。它广泛应用于科学计算、数据分析和机器学习等领域。在Unix系统下，NumPy更是得到了广泛的应用。本文将介绍NumPy在Unix系统中的神奇应用，以及一些代码示...
    99+
    2023-11-09
    http numpy unix
  • Python 文件操作：揭秘文件处理的神奇世界
    ...
    99+
    2024-04-02
  • 怎么用Python实现神奇的树效果
    本篇内容主要讲解“怎么用Python实现神奇的树效果”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么用Python实现神奇的树效果”吧!turtle是一只神奇的小海龟，可以画出大千世界。而tu...
    99+
    2023-06-16
  • node.js 操作系统模块的魔法：化腐朽为神奇
    文件和目录操作：揭开存储的奥秘 该模块赋予开发者对文件和目录的完全操控权。它提供了一系列函数，用于创建、读取、写入、删除和管理文件，以及创建、移动和删除目录。通过这种能力，开发人员可以构建各种应用程序，包括文件管理工具、数据存储解决方案和...
    99+
    2024-04-02
  • 探索多用户操作系统的神奇世界：实现团队无缝协作
    文件共享和访问控制 多用户操作系统集成文件共享功能，使团队成员可以轻松访问和编辑共同项目文件。访问控制机制允许管理员授予不同用户不同的权限级别，确保文件安全和协作的可控性。成员可以同时编辑文档，实时查看更改，从而加快协作流程。 通信和协作...
    99+
    2024-03-14
    多用户操作系统
  • 支持PyTorch的einops张量操作神器用法示例详解
    目录基础用法高级用法 今天做visual transformer研究的时候，发现了einops这么个神兵利器，决定大肆安利一波。 先看链接：https://github.c...
    99+
    2024-04-02
  • 虚拟内存，操作系统内存的神奇扩充器
    虚拟内存的工作原理 虚拟内存建立在虚拟地址和物理地址的概念之上。虚拟地址是应用程序使用的地址，而物理地址是计算机硬件实际使用的内存地址。虚拟内存的机制将虚拟地址映射到物理地址，从而让应用程序访问超出实际物理内存大小的内存区域。 当应用程序...
    99+
    2024-04-02
  • Java JMX 神奇之旅：解锁应用性能的无限可能
    ...
    99+
    2024-04-02
软考高级职称资格查询
iis服务器助手广告
软考职称历年真题下载
软考职称资料下载
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作