PostgreSQL、KingBase 資料庫 ORDER BY LIMIT 查詢緩慢案例

小至尖尖發表於2024-03-02

好久沒寫部落格了，最近從人大金倉離職了，新公司入職了螞蟻集團，正在全力學習 OcenaBase 資料庫的體系結構中。

以後分享的案例知識基本上都是以 OcenaBase 分散式資料庫為主了，呦西。😁

昨天幫朋友看了個金倉KES資料庫的 SQL 案例，廢話不說，直接貼SQL：

慢SQL（執行時間 8s ，限制返回 30 行）

explain analyze 
SELECT GI.ID,
       GI.MODULE_ID,
       GI.BT,
       GI.WH,
       GI.JJCD_TEXT,
       GI.CREATE_DEPTNAME,
       GI.CREATE_TIME,
       GI.MODULE_NAME
FROM gifgifgif GI
         INNER JOIN gufgufguf GUF ON (GUF.ifid = GI.ID)
WHERE GI.ROWSTATE > - 1
  AND (GUF.usid = '0' OR GUF.usid = '210317100256if6gVcTb3Ado1o2ytLs')
  AND ((GI.BT LIKE '%籤%') OR (GI.MODULE_NAME LIKE '%籤%') OR (GI.WH LIKE '%籤%') OR (GI.JJCD_TEXT LIKE '%籤%') OR
       (GI.CREATE_DEPTNAME LIKE '%籤%'))
ORDER BY GI.CREATE_TIME DESC LIMIT 30;

慢SQL執行計劃

                                                                                                                        QUERY PLAN                                                                                                           
             
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------
 Limit  (cost=1001.05..17578.06 rows=30 width=240) (actual time=6458.263..8763.733 rows=7 loops=1)
   ->  Gather Merge  (cost=1001.05..3879467.79 rows=7019 width=240) (actual time=6458.261..8763.728 rows=7 loops=1)
         Workers Planned: 4
         Workers Launched: 4
         ->  Nested Loop  (cost=0.99..3877631.71 rows=1755 width=240) (actual time=2843.144..8274.217 rows=1 loops=5)
               ->  Parallel Index Scan Backward using gifgifgif_CREATE_TIME1 on gifgifgif GI  (cost=0.43..1158925.09 rows=433728 width=240) (actual time=0.043..2159.037 rows=350466 loops=5)
                     Filter: ((ROWSTATE > '-1'::numeric) AND (((BT)::text ~~ '%籤%'::text) OR ((MODULE_NAME)::text ~~ '%籤%'::text) OR ((WH)::text ~~ '%籤%'::text) OR ((JJCD_TEXT)::text ~~ '%籤%'::text) OR ((CREATE_DEPTNAME)::text ~~ '%
%'::text)))
                     Rows Removed by Filter: 423271
               ->  Index Only Scan using idx_gufgufguf_1_2_3 on gufgufguf GUF  (cost=0.56..6.26 rows=1 width=32) (actual time=0.017..0.017 rows=0 loops=1752329)  -- 慢：(1752329/5) * 0.017 / 1000  = 5.95s
                     Index Cond: (ifid = (GI.ID)::text)
                     Filter: (((usid)::text = '0'::text) OR ((usid)::text = '210317100256if6gVcTb3Ado1o2ytLs'::text))
                     Rows Removed by Filter: 3
                     Heap Fetches: 0
 Planning Time: 0.832 ms
 Execution Time: 8763.803 ms
(15 行記錄)

我看到這計劃簡直無語，這種SQL不能 300 ms以內出來就絕對有問題，而且這麼簡單的語句都能用上並行，真的服。

　　Index Only Scan using idx_gufgufguf_1_2_3 on gufgufguf GUF 每個並行程序執行 5.95s 這也太拉跨了。

看執行計劃基本都是用 Index Scan 或者是 Index Only Scan，但是本SQL 謂詞過濾條件很多 or ，其實最佳化器如果執行點陣圖掃描才是最優解計劃，但是CBO偏偏沒執行！！！

SQL去掉 LIMIT 30限制條件:

explain analyze 
SELECT GI.ID,
       GI.MODULE_ID,
       GI.BT,
       GI.WH,
       GI.JJCD_TEXT,
       GI.CREATE_DEPTNAME,
       GI.CREATE_TIME,
       GI.MODULE_NAME
FROM gifgifgif GI
         INNER JOIN gufgufguf GUF ON (GUF.ifid = GI.ID)
WHERE GI.ROWSTATE > - 1
  AND (GUF.usid = '0' OR GUF.usid = '210317100256if6gVcTb3Ado1o2ytLs')
  AND ((GI.BT LIKE '%籤%') OR (GI.MODULE_NAME LIKE '%籤%') OR (GI.WH LIKE '%籤%') OR (GI.JJCD_TEXT LIKE '%籤%') OR
       (GI.CREATE_DEPTNAME LIKE '%籤%'))
ORDER BY GI.CREATE_TIME DESC ;

去掉 LIMIT 30限制條件SQL執行計劃：

                                                                                                                        QUERY PLAN                                                                                                           
             
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------
 Gather Merge  (cost=98222.89..99026.61 rows=6792 width=240) (actual time=33.640..35.974 rows=7 loops=1)
   Workers Planned: 3
   Workers Launched: 3
   ->  Sort  (cost=97222.85..97228.51 rows=2264 width=240) (actual time=26.724..26.725 rows=2 loops=4)
         Sort Key: GI.CREATE_TIME DESC
         Sort Method: quicksort  Memory: 25kB
         Worker 0:  Sort Method: quicksort  Memory: 25kB
         Worker 1:  Sort Method: quicksort  Memory: 25kB
         Worker 2:  Sort Method: quicksort  Memory: 26kB
         ->  Nested Loop  (cost=510.90..97096.70 rows=2264 width=240) (actual time=11.118..26.693 rows=2 loops=4)
               ->  Parallel Bitmap Heap Scan on gufgufguf GUF  (cost=510.35..59045.81 rows=5049 width=32) (actual time=0.480..3.498 rows=1178 loops=4)
                     Recheck Cond: (((usid)::text = '0'::text) OR ((usid)::text = '210317100256if6gVcTb3Ado1o2ytLs'::text))
                     Heap Blocks: exact=1464
                     ->  BitmapOr  (cost=510.35..510.35 rows=15652 width=0) (actual time=1.567..1.568 rows=0 loops=1)
                           ->  Bitmap Index Scan on gufgufguf_usid  (cost=0.00..251.26 rows=7826 width=0) (actual time=0.022..0.022 rows=0 loops=1)
                                 Index Cond: ((usid)::text = '0'::text)
                           ->  Bitmap Index Scan on gufgufguf_usid  (cost=0.00..251.26 rows=7826 width=0) (actual time=1.545..1.545 rows=4713 loops=1)
                                 Index Cond: ((usid)::text = '210317100256if6gVcTb3Ado1o2ytLs'::text)
               ->  Index Scan using gifgifgif_PKEY1 on gifgifgif GI  (cost=0.56..7.54 rows=1 width=240) (actual time=0.019..0.019 rows=0 loops=4713)
                     Index Cond: ((ID)::text = (GUF.ifid)::text)
                     Filter: ((ROWSTATE > '-1'::numeric) AND (((BT)::text ~~ '%籤%'::text) OR ((MODULE_NAME)::text ~~ '%籤%'::text) OR ((WH)::text ~~ '%籤%'::text) OR ((JJCD_TEXT)::text ~~ '%籤%'::text) OR ((CREATE_DEPTNAME)::text ~~ '%
%'::text)))
                     Rows Removed by Filter: 1
 Planning Time: 0.815 ms
 Execution Time: 36.060 ms
(24 行記錄)

可以看到去掉LIMIT 30 以後，CBO能正常使用上 Bitmap Index Scan + BitmapOr 的查詢策略，SQL只需要 36ms就能跑出結果。

PG比較牛逼的地方是B+樹索引能基於SQL的查詢條件，自動能轉換成點陣圖索引的查詢策略。

像這種情況就簡單了，只需要改變下限制SQL返回條數的邏輯即可，kingbase也相容Oracle rownum 的語法，我們可以將上面SQL等價改成 rownum 來最佳化。

LIMIT 改寫成 rownum ：

explain analyze 
SELECT * FROM (
SELECT GI.ID,
       GI.MODULE_ID,
       GI.BT,
       GI.WH,
       GI.JJCD_TEXT,
       GI.CREATE_DEPTNAME,
       GI.CREATE_TIME,
       GI.MODULE_NAME
FROM gifgifgif GI
         INNER JOIN gufgufguf GUF ON (GUF.ifid = GI.ID)
WHERE GI.ROWSTATE > - 1
  AND (GUF.usid = '0' OR GUF.usid = '210317100256if6gVcTb3Ado1o2ytLs')
  AND ((GI.BT LIKE '%籤%') OR (GI.MODULE_NAME LIKE '%籤%') OR (GI.WH LIKE '%籤%') OR (GI.JJCD_TEXT LIKE '%籤%') OR
       (GI.CREATE_DEPTNAME LIKE '%籤%'))
ORDER BY GI.CREATE_TIME DESC) WHERE ROWNUM <= 30;

LIMIT 改寫成 rownum 執行計劃：

                                                                                                                           QUERY PLAN                                                                                                        
                   
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------
 Count  (cost=98222.89..99162.45 rows=0 width=240) (actual time=31.418..33.691 rows=7 loops=1)
   Stop Keys: (ROWNUM <= 30)
   ->  Gather Merge  (cost=98222.89..99026.61 rows=6792 width=240) (actual time=31.415..33.686 rows=7 loops=1)
         Workers Planned: 3
         Workers Launched: 3
         ->  Sort  (cost=97222.85..97228.51 rows=2264 width=240) (actual time=26.497..26.498 rows=2 loops=4)
               Sort Key: GI.CREATE_TIME DESC
               Sort Method: quicksort  Memory: 25kB
               Worker 0:  Sort Method: quicksort  Memory: 25kB
               Worker 1:  Sort Method: quicksort  Memory: 27kB
               Worker 2:  Sort Method: quicksort  Memory: 25kB
               ->  Nested Loop  (cost=510.90..97096.70 rows=2264 width=240) (actual time=14.246..26.465 rows=2 loops=4)
                     ->  Parallel Bitmap Heap Scan on gufgufguf GUF  (cost=510.35..59045.81 rows=5049 width=32) (actual time=0.513..3.401 rows=1178 loops=4)
                           Recheck Cond: (((usid)::text = '0'::text) OR ((usid)::text = '210317100256if6gVcTb3Ado1o2ytLs'::text))
                           Heap Blocks: exact=1373
                           ->  BitmapOr  (cost=510.35..510.35 rows=15652 width=0) (actual time=1.664..1.664 rows=0 loops=1)
                                 ->  Bitmap Index Scan on gufgufguf_usid  (cost=0.00..251.26 rows=7826 width=0) (actual time=0.024..0.024 rows=0 loops=1)
                                       Index Cond: ((usid)::text = '0'::text)
                                 ->  Bitmap Index Scan on gufgufguf_usid  (cost=0.00..251.26 rows=7826 width=0) (actual time=1.639..1.639 rows=4713 loops=1)
                                       Index Cond: ((usid)::text = '210317100256if6gVcTb3Ado1o2ytLs'::text)
                     ->  Index Scan using gifgifgif_PKEY1 on gifgifgif GI  (cost=0.56..7.54 rows=1 width=240) (actual time=0.019..0.019 rows=0 loops=4713)
                           Index Cond: ((ID)::text = (GUF.ifid)::text)
                           Filter: ((ROWSTATE > '-1'::numeric) AND (((BT)::text ~~ '%籤%'::text) OR ((MODULE_NAME)::text ~~ '%籤%'::text) OR ((WH)::text ~~ '%籤%'::text) OR ((JJCD_TEXT)::text ~~ '%籤%'::text) OR ((CREATE_DEPTNAME)::text 
~~ '%籤%'::text)))
                           Rows Removed by Filter: 1
 Planning Time: 0.897 ms
 Execution Time: 33.778 ms
(26 行記錄)

可以看到SQL透過將LIMIT 改寫成 rownum 以後，原來執行時間 8s 降低到 33ms 就能跑出結果了，本條SQL到此已經最佳化完畢。

最後問題：那為什麼原SQL使用 limit 會慢？改成 rownum 後速度能秒出，通常情況下來說 limit 是PG提供原生的語法，效能應該更好才是？

解答：是因為在PostgreSQL中，LIMIT子句本身不直接與索引型別相關聯，而是用於指定返回的記錄數。然而，當LIMIT與ORDER BY結合使用時，PostgreSQL的查詢最佳化器可能會利用B+樹索引來加速查詢。

　　 這是因為B+樹索引能夠有效地支援有序資料的檢索，使得資料庫能夠快速地定位到需要的記錄而不必掃描整個表或索引。

　　然而需要透過索引進行排序的話，必然要透過 Index Scan 或者 Index Only Scan 掃描才可以對資料進行升序或者降序排序，而點陣圖索引是不支援對資料進行排序功能的。

　　所以為什麼一開始SQL會使用 Index Scan 和 Index Only Scan 而不使用 Bitmap Index Scan + BitmapOr 的查詢策略。

　　各位讀者以後在kingbase資料庫進行業務開發，如果需要謂詞過濾條件中有 or ，排序限制條件中有 order by + limit 的需求，儘量對業務SQL進行評估，從而選擇使用 rownum 還是 limit 語句來進行限制資料。

　　　如果在postgresql 進行開發的話遇到這種需求（pg不支援rownum寫法），還需要在外面再包一層查詢，使用 row_number() over() 視窗函式來進行限制即可。

Oracle資料庫非同步IO導致查詢響應緩慢
2015-09-02
Oracle資料庫非同步
Oracle 11g資料庫緩慢診斷案例
2015-12-17
Oracle資料庫
資料庫查詢慢的原因
2019-06-29
資料庫
資料庫連線緩慢
2013-03-14
資料庫
postgresql 匯入緩慢
2013-10-11
SQL
dba_jobs_running查詢緩慢
2016-09-06
MySQLslowquery[慢查詢]資料整理
2016-05-24
MySql
oracle 9i 查詢資料字典檢視慢案例分析
2010-09-08
Oracle
並行查詢緩慢的問題分析
2015-07-02
並行
查詢DBA_HIST_ACTIVE_SESS_HISTORY緩慢
2014-08-22
【ElasticSearch】給ElasticSearch資料庫配置慢查詢日誌
2021-06-18
Elasticsearch資料庫
資料庫MySQL一般查詢日誌或者慢查詢日誌歷史資料的清理
2018-08-03
資料庫MySql
MySQL slow query [慢查詢] 資料整理
2015-05-12
MySql
資料庫系列：MySQL慢查詢分析和效能最佳化
2023-02-10
資料庫MySql
oracle 跨庫查詢greenplum慢
2012-11-27
Oracle
【kingbase資料庫】kingbase檢視所有表名
2024-03-30
資料庫
資料庫查詢
2012-10-10
資料庫
MySQL order by limit 分頁資料重複問題
2018-05-14
MySqlMIT
資料庫 - 資料查詢
2015-05-05
資料庫
資料庫資料的查詢----連線查詢
2017-08-12
資料庫
EM自動任務導致資料庫緩慢
2015-12-10
資料庫
laravel 5.8 連線資料庫庫查詢資料速度慢，使用mysql 直接查詢響應就快，什麼原因呢？
2021-06-22
Laravel資料庫MySql
SQL Server資料庫查詢速度慢原因及優化方法
2009-02-03
SQLServer資料庫優化
Datagrip連線Kingbase資料庫
2024-04-08
資料庫
資料庫高階查詢之子查詢
2018-07-15
資料庫
遊標+bulk collect into limit的不同方法查詢資料
2016-04-06
MIT
Access查詢實現Mysql的 limit 查詢
2016-08-23
MySqlMIT
求助：資料庫查詢
2020-08-16
資料庫
ThinkPHP 資料庫查詢
2019-12-18
PHP資料庫
查詢資料庫大小
2021-09-09
資料庫
資料庫排序查詢
2020-10-02
資料庫排序
Jemter查詢資料庫
2024-03-15
資料庫
mysql資料庫優化之表的設計和慢查詢定位
2017-05-21
MySql資料庫優化
MySQL資料庫中的日誌檔案---(3)慢查詢日誌
2011-09-28
MySql資料庫
資料庫 - 連線查詢、巢狀查詢、集合查詢
2015-05-05
資料庫巢狀
Mysql索引型別建立錯誤導致SQL查詢緩慢
2017-03-02
MySql索引型別
資料庫基礎查詢--單表查詢
2018-07-15
資料庫
資料庫查詢優化：巢狀查詢
2017-01-24
資料庫優化巢狀

PostgreSQL、KingBase 資料庫 ORDER BY LIMIT 查詢緩慢案例

相關文章