PostgreSQL order by limit 1的”坑”

IT那活儿发布于2023-01-11 13:20 / 3240人阅读

最近在研究一个SQL的时候，发现一个有意思的问题。Sql代码如下：

sql代码挺长的，我们直接看它的执行计划，把它的执行计划在https://explain.depesz.com/格式化显示。

通过STATS可以发现，SQL总共执行3.2秒，Limit这个操作占用了近1.1秒的时间。这里首先我们对数据进行全部排序，排序完后选择整个记录的第一行。按道理来说慢应该在排序这个地方，而不应该在limit 1这里。

为了研究这个问题，搜索了一下，发现stackoverflow中有人提过这类问题。

我按照这个思路，把这个SQL进行了改写，使用了row_number() OVER()的方式。改写后代码如下：

经过改写后的执行计划如下：

查看统计值

几乎没有特别高消耗。执行时间从3.2秒下降到1.4秒。

当然此类问题还有很多，都是limit 1引起的。我在stackexchange上也发现了这样的一个案例。

我们观察它改写之前和改写之后的SQL，整个改写就是把它变成了子查询就正常了。这是因为前面的SQL没有使用上索引，改成子查询后就使用上了索引。

知乎上也有作者遇到这个问题，并按照这个方式解决了。
https://zhuanlan.zhihu.com/p/45748671

所以说order by Limit n这种写法是有坑的。如果你发现它有一些慢，建议进行改写。

参考链接：

https://zhuanlan.zhihu.com/p/45748671

https://dba.stackexchange.com/questions/110636/postgres-poor-performance-on-order-by-id-desc-limit-1/110919

https://stackoverflow.com/questions/6037843/extremely-slow-postgresql-query-with-order-and-limit-clauses

END

更多精彩干货分享

点击下方名片关注

IT那活儿

GPU云服务器云服务器 order-by 配置阿里云window服务器的一些坑 Ordered ORDER

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129792.html

title: postGreSQL 插件 timescaleDB 安装使用 date: 2019-0

摘要：基本环境操作系统安装安装和安装时序数据库插件部署实践时空数据库德哥官网安装文档设置实验下载测试数据创建数据库解压下载文件创建表结构导入数据查询测试注释数据库启动连接数据库创基本环境操作系统： centOS 7 postGreSQL ： 10 timescaleDB : 1.0 + postGreSQL安装 Centos7 安装Postgresql10.5和PostGIS times...

Kylin_Mountain 2019-07-25 12:39 评论0 收藏0
PostgreSQL 自动分区分表维护管理插件 pathman 基础使用

摘要：使用数据库会自动的根据从某几个片中读取数据。更加详细的请参考德哥文章官方地址：https://github.com/postgrespr...关于pathman的原理和优化问题，请移步至https://yq.aliyun.com/article... 检查环境变量如果直接执行psql命令提示command not found则执行下面的命令设置环境变量 root@host# PA...

MASAILA 2019-07-25 12:37 评论0 收藏0
order by造成分页查询的记录重复

摘要：场景有这样一张表，分页查询时，第一页的记录又出现下第二页中，总之每一页会出现之前出现的内容。原因是值重复分析在数据库中使用查询语句第一页第二页第三页查询结果在第一页和第二页出现了重复的记录。原因的值重复了解决方法用来的字段要保证唯一性。场景有这样一张表，分页查询时，第一页的记录又出现下第二页中，总之每一页会出现之前出现的内容。原因是sort值重复 showImg(https://s...

chenjiang3 2019-07-25 12:40 评论0 收藏0
你知道MySQL的Limit有性能问题吗

摘要：问题对于小的偏移量，直接使用来查询没有什么问题，但随着数据量的增大，越往后分页，语句的偏移量就会越大，速度也会明显变慢。优化思想避免数据量大时扫描过多的记录解决子查询的分页方式或者分页方式。MySQL的分页查询通常通过limit来实现。 MySQL的limit基本用法很简单。limit接收1或2个整数型参数，如果是2个参数，第一个是指定第一个返回记录行的偏移量，第二个是返回记录行的最大数目。...

Arno 2019-05-23 17:28 评论0 收藏0