PostgreSQL、KingBase 数据库 ORDER BY LIMIT 查询缓慢案例

好久没写博客了,最近从人大金仓离职了,新公司入职了蚂蚁集团,正在全力学习 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到此已经优化完毕。

 

再来看个 postgresql 类似案例

慢SQL如下:

-- 慢SQL :
SELECT
    npspppordedddd.*,
    npsmainproxxss.member_num,
    npsmainproxxss.tools,
    npsmainproxxss.importance,
    npsmainproxxss.member_type,
    npsmainproxxss.machine_switch,
    npsmainproxxss.image_url,
    npsmainproxxss.sys_file_id,
    npsmainproxxss.work_time,
    npsmainproxxss.opt_place,
    npsmainproxxss.opt_work,
    npsmainproxxss.opt_work_desc,
    npsmainproxxss.check_item,
    npsmainproxxss.val_min,
    npsmainproxxss.val_max,
    npsmanscheeee.DYNAMIC,
    npsmanscheeee.task_policy,
    npsmainfssdx.package_code,
    npsmainfssdx.package_name,
    npsmainfssdx.package_desc,
    npsmainfssdx.location_desc,
    npsmainfssdx.product_line,
    npsmainfssdx.product_line_code,
    npsmainfssdx.work_area,
    npsmainfssdx.work_area_code,
    npsmainfssdx.PROCEDURE,
    npsmainfssdx.procedure_code,
    npsmanscheeee.quantum,
    npsmanscheeee.float_time,
    npsmanscheeee.sort,
    ccssdeewqnn.team_id,
    cssdsewff.pos_id,
    cssdsewff.pos_name,
    npsmainfssdx.by_sort
FROM
    npspppordedddd
    LEFT JOIN cssxxxaaaset ON cssxxxaaaset.ID = npspppordedddd.target_code
    LEFT JOIN cssxxxaaaset AS facility_location ON facility_location.ID = cssxxxaaaset.parent_id
    LEFT JOIN cssxxxaaaset AS fc_tp_line ON fc_tp_line.ID = facility_location.parent_id
    LEFT JOIN npsmanscheeee ON npsmanscheeee.ID = npspppordedddd.schedule_id
    LEFT JOIN npsmainproxxss ON npsmainproxxss.ID = npsmanscheeee.maintain_project_id
    LEFT JOIN npsmainfssdx ON npsmainfssdx.ID = npsmanscheeee.maintain_package_id
    LEFT JOIN cssdsewff ON cssdsewff.device_code = npsmainfssdx.target_code
    LEFT JOIN ccssdeewqnn ON ccssdeewqnn.team_id = cssdsewff.team_id 
WHERE
    npsmainfssdx.target_type = 'device' 
    AND npspppordedddd.maintain_type = 'DS' 
    AND npspppordedddd.plan_time >= '2024-02-03' 
    AND npspppordedddd.plan_time <= '2024-03-03' 
    AND cssxxxaaaset.level_code = 'asset' 
    AND cssxxxaaaset.ID IN (
        'a4fe1cbb-8f3f-499e-9ae2-f4a5301b074e',
        '1537e802-0458-4e75-ba53-47b4c60cc9b8',
        '7e7aa6c8-c2dd-4373-b793-70440b940c29',
        'fb5b2c90-a7cc-4734-b0e1-518f08e8972e',
        'b8929b19-cfa7-4581-8867-41d091ca3ade',
        '8e4559af-7755-4425-a409-e3cbe97669db',
        'a50e4279-5f52-4e36-b2ed-b1de6c5086e4',
        'ac94281e-6549-40db-bb7b-664c07bdb84e',
        '80d6a2b8-9268-446a-bb0c-b787f49b262c',
        'fe473ca5-d96f-49fb-959e-b70cf15919a9',
        '291f2271-9645-4f0f-830b-9190786d78d3',
        '92c9d569-fa32-4cca-b18e-8f69a0802a3c',
        'f7dab2be-5b8e-4dfd-be16-af2bd2d6c6d6',
        '87e1890e-9a54-4f85-8340-d341f9b9a1f3',
        '16032ca6-3009-48eb-be54-6b32e2dce11f',
        '8c410ba7-852c-4510-ad61-abb6e4e62706',
        'cfdaacca-c6ca-431f-ab9c-fce60df46ee1',
        'c7aa7e85-0076-4b1f-a2b6-8d49d67eefc6',
        'a4ea061c-ac20-4515-b069-59ce8c8a0b1b',
        '624826b0-1c73-4d73-a46a-84df45336b3b',
        '9853c273-844c-4fd7-a64a-e27b7d44f577',
        'c663f5b3-6a17-4863-aa8f-4d1c8a85200c',
        'e0d499cf-9b41-4da3-b501-e29cb1dcc0a3' 
    ) 
    AND npsmainfssdx.tenant_code IN (
        '_ADMIN',
        '_NA_',
        '59052881-df03-4c49-923f-6382b8ed138b',
        '573bcfa0-239a-4f0b-9105-b906db4895ec',
        '3264de0d-7262-4dce-8178-0112c0a1603b',
        '0e0208f8-8763-4735-bb2c-ea5ef8bda742',
        'f333dba3-7f08-4276-8bcb-a99c6ecc5120',
        '4b04078d-8265-400d-a27d-e40d0f1646b4',
        '52e44df1-be04-4bfc-bd5f-f37dbb60252c',
        '6088e13a-28de-4b88-908f-247889e3d068',
        '213fe35d-ddce-4876-a91c-f8c292713a20',
        'e5fa7501-b3cb-4c59-bb78-6f8e5cdad19b',
        'acda7d56-6d0f-4302-b7f4-292fb71173ba',
        'ad13e67e-8bdc-45b1-96f4-f156c9c7dfb0',
        '8b240bd1-bc1d-4dcf-87de-4e8e3e74d004',
        '69869877-f9ce-4e85-a73b-edefe702bb6c',
        '0ad151a9-bec9-4e23-953b-9f4c2e7be9a1' 
    ) ORDER BY npsmanscheeee.sort,npspppordedddd.opt_shift_team ASC
    LIMIT  15;

上面这条慢SQL 加了limit 15 以后出不了结果,如果去掉的话,10s左右就能返回查询结果。

使用 row_number 分页方案优化SQL:

-- row_number 分页 :
SELECT * FROM (
SELECT
    npspppordedddd.*,
    npsmainproxxss.member_num,
    npsmainproxxss.tools,
    npsmainproxxss.importance,
    npsmainproxxss.member_type,
    npsmainproxxss.machine_switch,
    npsmainproxxss.image_url,
    npsmainproxxss.sys_file_id,
    npsmainproxxss.work_time,
    npsmainproxxss.opt_place,
    npsmainproxxss.opt_work,
    npsmainproxxss.opt_work_desc,
    npsmainproxxss.check_item,
    npsmainproxxss.val_min,
    npsmainproxxss.val_max,
    npsmanscheeee.DYNAMIC,
    npsmanscheeee.task_policy,
    npsmainfssdx.package_code,
    npsmainfssdx.package_name,
    npsmainfssdx.package_desc,
    npsmainfssdx.location_desc,
    npsmainfssdx.product_line,
    npsmainfssdx.product_line_code,
    npsmainfssdx.work_area,
    npsmainfssdx.work_area_code,
    npsmainfssdx.PROCEDURE,
    npsmainfssdx.procedure_code,
    npsmanscheeee.quantum,
    npsmanscheeee.float_time,
    npsmanscheeee.sort,
    ccssdeewqnn.team_id,
    cssdsewff.pos_id,
    cssdsewff.pos_name,
    npsmainfssdx.by_sort,
    row_number() over(ORDER BY npsmanscheeee.sort,npspppordedddd.opt_shift_team ASC) rn
FROM
    npspppordedddd
    LEFT JOIN cssxxxaaaset ON cssxxxaaaset.ID = npspppordedddd.target_code
    LEFT JOIN cssxxxaaaset AS facility_location ON facility_location.ID = cssxxxaaaset.parent_id
    LEFT JOIN cssxxxaaaset AS fc_tp_line ON fc_tp_line.ID = facility_location.parent_id
    LEFT JOIN npsmanscheeee ON npsmanscheeee.ID = npspppordedddd.schedule_id
    LEFT JOIN npsmainproxxss ON npsmainproxxss.ID = npsmanscheeee.maintain_project_id
    LEFT JOIN npsmainfssdx ON npsmainfssdx.ID = npsmanscheeee.maintain_package_id
    LEFT JOIN cssdsewff ON cssdsewff.device_code = npsmainfssdx.target_code
    LEFT JOIN ccssdeewqnn ON ccssdeewqnn.team_id = cssdsewff.team_id 
WHERE
    npsmainfssdx.target_type = 'device' 
    AND npspppordedddd.maintain_type = 'DS' 
    AND npspppordedddd.plan_time >= '2024-02-03' 
    AND npspppordedddd.plan_time <= '2024-03-03' 
    AND cssxxxaaaset.level_code = 'asset' 
    AND cssxxxaaaset.ID IN (
        'a4fe1cbb-8f3f-499e-9ae2-f4a5301b074e',
        '1537e802-0458-4e75-ba53-47b4c60cc9b8',
        '7e7aa6c8-c2dd-4373-b793-70440b940c29',
        'fb5b2c90-a7cc-4734-b0e1-518f08e8972e',
        'b8929b19-cfa7-4581-8867-41d091ca3ade',
        '8e4559af-7755-4425-a409-e3cbe97669db',
        'a50e4279-5f52-4e36-b2ed-b1de6c5086e4',
        'ac94281e-6549-40db-bb7b-664c07bdb84e',
        '80d6a2b8-9268-446a-bb0c-b787f49b262c',
        'fe473ca5-d96f-49fb-959e-b70cf15919a9',
        '291f2271-9645-4f0f-830b-9190786d78d3',
        '92c9d569-fa32-4cca-b18e-8f69a0802a3c',
        'f7dab2be-5b8e-4dfd-be16-af2bd2d6c6d6',
        '87e1890e-9a54-4f85-8340-d341f9b9a1f3',
        '16032ca6-3009-48eb-be54-6b32e2dce11f',
        '8c410ba7-852c-4510-ad61-abb6e4e62706',
        'cfdaacca-c6ca-431f-ab9c-fce60df46ee1',
        'c7aa7e85-0076-4b1f-a2b6-8d49d67eefc6',
        'a4ea061c-ac20-4515-b069-59ce8c8a0b1b',
        '624826b0-1c73-4d73-a46a-84df45336b3b',
        '9853c273-844c-4fd7-a64a-e27b7d44f577',
        'c663f5b3-6a17-4863-aa8f-4d1c8a85200c',
        'e0d499cf-9b41-4da3-b501-e29cb1dcc0a3' 
    ) 
    AND npsmainfssdx.tenant_code IN (
        '_ADMIN',
        '_NA_',
        '59052881-df03-4c49-923f-6382b8ed138b',
        '573bcfa0-239a-4f0b-9105-b906db4895ec',
        '3264de0d-7262-4dce-8178-0112c0a1603b',
        '0e0208f8-8763-4735-bb2c-ea5ef8bda742',
        'f333dba3-7f08-4276-8bcb-a99c6ecc5120',
        '4b04078d-8265-400d-a27d-e40d0f1646b4',
        '52e44df1-be04-4bfc-bd5f-f37dbb60252c',
        '6088e13a-28de-4b88-908f-247889e3d068',
        '213fe35d-ddce-4876-a91c-f8c292713a20',
        'e5fa7501-b3cb-4c59-bb78-6f8e5cdad19b',
        'acda7d56-6d0f-4302-b7f4-292fb71173ba',
        'ad13e67e-8bdc-45b1-96f4-f156c9c7dfb0',
        '8b240bd1-bc1d-4dcf-87de-4e8e3e74d004',
        '69869877-f9ce-4e85-a73b-edefe702bb6c',
        '0ad151a9-bec9-4e23-953b-9f4c2e7be9a1' 
    ) 
        ) x WHERE x.rn <= 15;  

使用 row_number 分页执行计划:

Subquery Scan on x  (cost=73002.03..73041.39 rows=404 width=2650) (actual time=492.419..562.760 rows=15 loops=1)
  Filter: (x.rn <= 15)
  Rows Removed by Filter: 24089
  ->  WindowAgg  (cost=73002.03..73026.25 rows=1211 width=2650) (actual time=492.417..561.576 rows=24104 loops=1)
        ->  Sort  (cost=73002.03..73005.06 rows=1211 width=2642) (actual time=492.373..511.693 rows=24104 loops=1)
              Sort Key: npsmanscheeee.sort, npspppordedddd.opt_shift_team
              Sort Method: external merge  Disk: 20776kB
              ->  Hash Left Join  (cost=229.76..72940.02 rows=1211 width=2642) (actual time=3.379..432.995 rows=24104 loops=1)
                    Hash Cond: ((cssdsewff.team_id)::text = (ccssdeewqnn.team_id)::text)
                    ->  Hash Left Join  (cost=228.34..72934.70 rows=1211 width=2529) (actual time=3.352..424.280 rows=24104 loops=1)
                          Hash Cond: ((npsmainfssdx.target_code)::text = (cssdsewff.device_code)::text)
                          ->  Nested Loop Left Join  (cost=223.99..72925.80 rows=1211 width=2473) (actual time=3.252..413.474 rows=24104 loops=1)
                                ->  Hash Join  (cost=223.57..67548.64 rows=1211 width=1998) (actual time=3.236..298.888 rows=24104 loops=1)
                                      Hash Cond: ((npsmanscheeee.maintain_package_id)::text = (npsmainfssdx.id)::text)
                                      ->  Nested Loop  (cost=134.64..67454.55 rows=1958 width=1009) (actual time=2.159..284.665 rows=24104 loops=1)
                                            ->  Nested Loop  (cost=134.22..58768.62 rows=1958 width=726) (actual time=2.138..163.896 rows=24104 loops=1)
                                                  ->  Hash Right Join  (cost=133.67..194.89 rows=12 width=39) (actual time=0.655..0.839 rows=23 loops=1)
                                                        Hash Cond: ((fc_tp_line.id)::text = (facility_location.parent_id)::text)
                                                        ->  Index Only Scan using ccs_xdevice_parent_asset_pseudo_level_idx on cssxxxaaaset fc_tp_line  (cost=0.28..58.10 rows=655 width=39) (actual time=0.011..0.096 rows=655 loops=1)
                                                              Heap Fetches: 0
                                                        ->  Hash  (cost=133.24..133.24 rows=12 width=79) (actual time=0.616..0.620 rows=23 loops=1)
                                                              Buckets: 1024  Batches: 1  Memory Usage: 11kB
                                                              ->  Hash Left Join  (cost=66.56..133.24 rows=12 width=79) (actual time=0.571..0.611 rows=23 loops=1)
                                                                    Hash Cond: ((cssxxxaaaset.parent_id)::text = (facility_location.id)::text)
                                                                    ->  Index Only Scan using ccs_xdevice_parent_asset_pseudo_level_idx on cssxxxaaaset  (cost=0.28..66.79 rows=12 width=79) (actual time=0.239..0.269 rows=23 loops=1)
                                                                          Index Cond: (level_code = 'asset'::text)
                                                                          Filter: ((id)::text = ANY ('{a4fe1cbb-8f3f-499e-9ae2-f4a5301b074e,1537e802-0458-4e75-ba53-47b4c60cc9b8,7e7aa6c8-c2dd-4373-b793-70440b940c29,fb5b2c90-a7cc-4734-b0e1-518f08e8972e,b8929b19-cfa7-4581-8867-41d091ca3ade,8e4559af-7755-4425-a409-e3cbe97669db,a50e4279-5f52-4e36-b2ed-b1de6c5086e4,ac94281e-6549-40db-bb7b-664c07bdb84e,80d6a2b8-9268-446a-bb0c-b787f49b262c,fe473ca5-d96f-49fb-959e-b70cf15919a9,291f2271-9645-4f0f-830b-9190786d78d3,92c9d569-fa32-4cca-b18e-8f69a0802a3c,f7dab2be-5b8e-4dfd-be16-af2bd2d6c6d6,87e1890e-9a54-4f85-8340-d341f9b9a1f3,16032ca6-3009-48eb-be54-6b32e2dce11f,8c410ba7-852c-4510-ad61-abb6e4e62706,cfdaacca-c6ca-431f-ab9c-fce60df46ee1,c7aa7e85-0076-4b1f-a2b6-8d49d67eefc6,a4ea061c-ac20-4515-b069-59ce8c8a0b1b,624826b0-1c73-4d73-a46a-84df45336b3b,9853c273-844c-4fd7-a64a-e27b7d44f577,c663f5b3-6a17-4863-aa8f-4d1c8a85200c,e0d499cf-9b41-4da3-b501-e29cb1dcc0a3}'::text[]))
                                                                          Rows Removed by Filter: 328
                                                                          Heap Fetches: 0
                                                                    ->  Hash  (cost=58.10..58.10 rows=655 width=79) (actual time=0.324..0.325 rows=655 loops=1)
                                                                          Buckets: 1024  Batches: 1  Memory Usage: 80kB
                                                                          ->  Index Only Scan using ccs_xdevice_parent_asset_pseudo_level_idx on cssxxxaaaset facility_location  (cost=0.28..58.10 rows=655 width=79) (actual time=0.030..0.122 rows=655 loops=1)
                                                                                Heap Fetches: 0
                                                  ->  Index Scan using npspppordedddd_target_schedule_maintain_plan_status_idx on npspppordedddd  (cost=0.56..4877.55 rows=359 width=726) (actual time=3.047..6.593 rows=1048 loops=23)
                                                        Index Cond: (((target_code)::text = (cssxxxaaaset.id)::text) AND ((maintain_type)::text = 'DS'::text) AND (plan_time >= '2024-02-03 00:00:00'::timestamp without time zone) AND (plan_time <= '2024-03-03 00:00:00'::timestamp without time zone))
                                            ->  Index Scan using npsmanscheeee_pkey on npsmanscheeee  (cost=0.42..4.44 rows=1 width=303) (actual time=0.004..0.004 rows=1 loops=24104)
                                                  Index Cond: ((id)::text = (npspppordedddd.schedule_id)::text)
                                      ->  Hash  (cost=82.76..82.76 rows=493 width=1028) (actual time=1.066..1.067 rows=747 loops=1)
                                            Buckets: 1024  Batches: 1  Memory Usage: 149kB
                                            ->  Seq Scan on npsmainfssdx  (cost=0.00..82.76 rows=493 width=1028) (actual time=0.020..0.784 rows=747 loops=1)
                                                  Filter: (((target_type)::text = 'device'::text) AND ((tenant_code)::text = ANY ('{_ADMIN,_NA_,59052881-df03-4c49-923f-6382b8ed138b,573bcfa0-239a-4f0b-9105-b906db4895ec,3264de0d-7262-4dce-8178-0112c0a1603b,0e0208f8-8763-4735-bb2c-ea5ef8bda742,f333dba3-7f08-4276-8bcb-a99c6ecc5120,4b04078d-8265-400d-a27d-e40d0f1646b4,52e44df1-be04-4bfc-bd5f-f37dbb60252c,6088e13a-28de-4b88-908f-247889e3d068,213fe35d-ddce-4876-a91c-f8c292713a20,e5fa7501-b3cb-4c59-bb78-6f8e5cdad19b,acda7d56-6d0f-4302-b7f4-292fb71173ba,ad13e67e-8bdc-45b1-96f4-f156c9c7dfb0,8b240bd1-bc1d-4dcf-87de-4e8e3e74d004,69869877-f9ce-4e85-a73b-edefe702bb6c,0ad151a9-bec9-4e23-953b-9f4c2e7be9a1}'::text[])))
                                                  Rows Removed by Filter: 46
                                ->  Index Scan using npsmainproxxss_pkey on npsmainproxxss  (cost=0.42..4.44 rows=1 width=515) (actual time=0.004..0.004 rows=1 loops=24104)
                                      Index Cond: ((id)::text = (npsmanscheeee.maintain_project_id)::text)
                          ->  Hash  (cost=3.60..3.60 rows=60 width=367) (actual time=0.034..0.035 rows=0 loops=1)
                                Buckets: 1024  Batches: 1  Memory Usage: 8kB
                                ->  Seq Scan on cssdsewff  (cost=0.00..3.60 rows=60 width=367) (actual time=0.013..0.031 rows=60 loops=1)
                    ->  Hash  (cost=1.19..1.19 rows=19 width=146) (actual time=0.014..0.015 rows=19 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 10kB
                          ->  Seq Scan on ccssdeewqnn  (cost=0.00..1.19 rows=19 width=146) (actual time=0.007..0.009 rows=19 loops=1)
Planning Time: 3.460 ms
Execution Time: 788.862 ms

可以看到 postgresql 这条分页SQL语句从 limit 改成 row_number 函数实现分页功能后,速度也能从原来跑不出结果降至 788ms 执行完成,本条SQL到此已经优化完毕。

 

最后问题:那为什么在Kingbase数据库上原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() 窗口函数来进行限制即可。 

posted @ 2024-03-02 14:09  小至尖尖  阅读(629)  评论(1编辑  收藏  举报