时间戳

首页 > 解决方案 > 使这个查询更快

postgresql - 使这个查询更快

问题描述

因此，正如我之前提到的，我不处理通常需要帮助来增强此查询的原始 SQL。目前，大约需要 3 秒。我想知道他们是否有办法让它变得更好。我删除了一些选择以使查询更小。

select row_number() over ()                      as id,
       scope.id                                  as sow_id,
       scope.*,
       task.*,
       po.entity_id                              as group_name
from t_scope_of_work as scope,
     (select * from audittaskimpl) as task
         Left join peopleassignments_potowners po ON task.taskid = po.task_id and po.entity_id like '%/%'
Where task.processinstanceid IN
      (select distinct processinstanceid from taskvariableimpl where value = scope.sownumber)
order by task.processinstanceid desc, task.createdon desc;

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN                                                                                                                                                                       |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Sort  (cost=3037398.97..3037728.28 rows=131726 width=1981) (actual time=1714.744..1714.812 rows=648 loops=1)                                                                     |
|  Sort Key: task.processinstanceid DESC, task.createdon DESC                                                                                                                     |
|  Sort Method: quicksort  Memory: 884kB                                                                                                                                          |
|  Buffers: shared hit=930703                                                                                                                                                     |
|  ->  WindowAgg  (cost=0.55..2800174.53 rows=131726 width=1981) (actual time=6.919..1713.339 rows=648 loops=1)                                                                   |
|        Buffers: shared hit=930703                                                                                                                                               |
|        ->  Merge Right Join  (cost=0.55..2798527.96 rows=131726 width=1965) (actual time=6.905..1712.235 rows=648 loops=1)                                                      |
|              Merge Cond: (po.task_id = task.taskid)                                                                                                                             |
|              Buffers: shared hit=930703                                                                                                                                         |
|              ->  Index Only Scan using idx_paspot_taskentity on peopleassignments_potowners po  (cost=0.27..32.45 rows=462 width=28) (actual time=0.101..0.378 rows=446 loops=1)|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

标签： postgresqlquery-optimization

解决方案

您的查询归结为：

select row_number() over ()                      as id,
       scope.id                                  as sow_id,
       scope.*,
       task.*,
       po.entity_id                              as group_name
from t_scope_of_work as scope
join audittaskimpl as task
  on task.processinstanceid IN  (select distinct tvi.processinstanceid 
                                   from taskvariableimpl tvi
                                  where tvi.value = scope.sownumber)
Left outer join peopleassignments_potowners po 
             ON po.task_id = task.taskid 
            and po.entity_id like '%/%'

order by task.processinstanceid desc, task.createdon desc;

如果没有解释，几乎不可能就该查询中的瓶颈所在提供建议。例如，您可能缺少一些索引，可能是因为数据太多而无法快速获得。

就是说，我怀疑您是否需要distinct那里；EXISTS()事实上，仅仅为了看看它给出了什么，它可能值得尝试一个构造。像这样的东西：

 select row_number() over ()                      as id,
       scope.id                                  as sow_id,
       scope.*,
       task.*,
       po.entity_id                              as group_name
from t_scope_of_work as scope
join audittaskimpl as task
  on exists ( select * 
                from taskvariableimpl tvi
               where tvi.value             = scope.sownumber
                 and tvi.processinstanceid = task.processinstanceid )
Left outer join peopleassignments_potowners po 
             ON po.task_id = task.taskid 
            and po.entity_id like '%/%'

order by task.processinstanceid desc, task.createdon desc;

警告：购买前先试用！事实证明，这可能比您的原始查询更糟糕，我以前从未做过JOIN [..] ON EXISTS()；再次，在没有正确了解您的表、卷、索引、数据布局的情况下（阅读：连接中有很多“命中”还是只有几条记录？）等等......我们在这里几乎是盲目地工作。

推荐阅读