PostgreSQL order by limit 1的”坑”

IT那活兒發布于2023-01-11 13:20 / 3227人閱讀

最近在研究一個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

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/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