logo标签列表

按ASC排序比按DESC排序快100倍？为什么？

postgresqlperformancehibernateoptimizationsql-order-by
4

我有一个由Hibernate生成的JBPM复杂查询。 我无法真正修改它，因此正在尽可能优化它。

我发现ORDER BY DESC比ORDER BY ASC慢得多，你有什么想法吗？

PostgreSQL版本：9.4 模式：https://pastebin.com/qNZhrbef 查询：

select 
taskinstan0_.ID_ as ID1_27_, 
taskinstan0_.VERSION_ as VERSION3_27_, 
taskinstan0_.NAME_ as NAME4_27_, 
taskinstan0_.DESCRIPTION_ as DESCRIPT5_27_, 
taskinstan0_.ACTORID_ as ACTORID6_27_, 
taskinstan0_.CREATE_ as CREATE7_27_, 
taskinstan0_.START_ as START8_27_, 
taskinstan0_.END_ as END9_27_,
taskinstan0_.DUEDATE_ as DUEDATE10_27_, 
taskinstan0_.PRIORITY_ as PRIORITY11_27_, 
taskinstan0_.ISCANCELLED_ as ISCANCE12_27_, 
taskinstan0_.ISSUSPENDED_ as ISSUSPE13_27_, 
taskinstan0_.ISOPEN_ as ISOPEN14_27_, 
taskinstan0_.ISSIGNALLING_ as ISSIGNA15_27_, 
taskinstan0_.ISBLOCKING_ as ISBLOCKING16_27_, 
taskinstan0_.LOCKED as LOCKED27_, 
taskinstan0_.QUEUE as QUEUE27_, 
taskinstan0_.TASK_ as TASK19_27_, 
taskinstan0_.TOKEN_ as TOKEN20_27_, 
taskinstan0_.PROCINST_ as PROCINST21_27_, 
taskinstan0_.SWIMLANINSTANCE_ as SWIMLAN22_27_, 
taskinstan0_.TASKMGMTINSTANCE_ as TASKMGM23_27_ 
from JBPM_TASKINSTANCE taskinstan0_, JBPM_VARIABLEINSTANCE stringinst1_, JBPM_PROCESSINSTANCE processins2_, JBPM_VARIABLEINSTANCE variablein3_ 

where stringinst1_.CLASS_='S' 
    and taskinstan0_.PROCINST_=processins2_.ID_ 
    and taskinstan0_.ID_=variablein3_.TASKINSTANCE_ 
    and variablein3_.NAME_ = 'NIR' 
    and taskinstan0_.QUEUE = 'ERT_TPS'
    and (processins2_.ORGAPATH_ like '/ERT%')
    and taskinstan0_.ISOPEN_= 't'
    and variablein3_.ID_=stringinst1_.ID_
order by stringinst1_.STRINGVALUE_ ASC limit '10';

解释 ASC 的结果：
ASC 是升序排序，即从小到大排列。
 Limit  (cost=1.71..11652.93 rows=10 width=646) (actual time=6.588..82.407 rows=10 loops=1)
   ->  Nested Loop  (cost=1.71..6215929.27 rows=5335 width=646) (actual time=6.587..82.402 rows=10 loops=1)
         ->  Nested Loop  (cost=1.29..6213170.78 rows=5335 width=646) (actual time=6.578..82.363 rows=10 loops=1)
               ->  Nested Loop  (cost=1.00..6159814.66 rows=153812 width=13) (actual time=0.537..82.130 rows=149 loops=1)
                     ->  Index Scan Backward using totoidx10 on jbpm_variableinstance stringinst1_  (cost=0.56..558481.07 rows=11199905 width=13) (actual time=0.018..11.914 rows=40182 loops=1)
                           Filter: (class_ = 'S'::bpchar)
                     ->  Index Scan using jbpm_variableinstance_pkey on jbpm_variableinstance variablein3_  (cost=0.43..0.49 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=40182)
                           Index Cond: (id_ = stringinst1_.id_)
                           Filter: ((name_)::text = 'NIR'::text)
                           Rows Removed by Filter: 1
               ->  Index Scan using jbpm_taskinstance_pkey on jbpm_taskinstance taskinstan0_  (cost=0.29..0.34 rows=1 width=641) (actual time=0.001..0.001 rows=0 loops=149)
                     Index Cond: (id_ = variablein3_.taskinstance_)
                     Filter: (isopen_ AND ((queue)::text = 'ERT_TPS'::text))
                     Rows Removed by Filter: 0
         ->  Index Only Scan using idx_procin_2 on jbpm_processinstance processins2_  (cost=0.42..0.51 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=10)
               Index Cond: (id_ = taskinstan0_.procinst_)
               Filter: ((orgapath_)::text ~~ '/ERT%'::text)
               Heap Fetches: 0
 Planning time: 2.598 ms
 Execution time: 82.513 ms

DESC 的结果解释：
 Limit  (cost=1.71..11652.93 rows=10 width=646) (actual time=8144.871..8144.986 rows=10 loops=1)
   ->  Nested Loop  (cost=1.71..6215929.27 rows=5335 width=646) (actual time=8144.870..8144.984 rows=10 loops=1)
         ->  Nested Loop  (cost=1.29..6213170.78 rows=5335 width=646) (actual time=8144.858..8144.951 rows=10 loops=1)
               ->  Nested Loop  (cost=1.00..6159814.66 rows=153812 width=13) (actual time=8144.838..8144.910 rows=20 loops=1)
                     ->  Index Scan using totoidx10 on jbpm_variableinstance stringinst1_  (cost=0.56..558481.07 rows=11199905 width=13) (actual time=0.066..2351.727 rows=2619671 loops=1)
                           Filter: (class_ = 'S'::bpchar)
                           Rows Removed by Filter: 906237
                     ->  Index Scan using jbpm_variableinstance_pkey on jbpm_variableinstance variablein3_  (cost=0.43..0.49 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=2619671)
                           Index Cond: (id_ = stringinst1_.id_)
                           Filter: ((name_)::text = 'NIR'::text)
                           Rows Removed by Filter: 1
               ->  Index Scan using jbpm_taskinstance_pkey on jbpm_taskinstance taskinstan0_  (cost=0.29..0.34 rows=1 width=641) (actual time=0.002..0.002 rows=0 loops=20)
                     Index Cond: (id_ = variablein3_.taskinstance_)
                     Filter: (isopen_ AND ((queue)::text = 'ERT_TPS'::text))
         ->  Index Only Scan using idx_procin_2 on jbpm_processinstance processins2_  (cost=0.42..0.51 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=10)
               Index Cond: (id_ = taskinstan0_.procinst_)
               Filter: ((orgapath_)::text ~~ '/ERT%'::text)
               Heap Fetches: 0
 Planning time: 2.080 ms
 Execution time: 8145.053 ms

表信息： jbpm_variableinstance 表有12100592行数据 jbpm_taskinstance 表有69913行数据 jbpm_processinstance 表有97546行数据

如果您有任何想法，谢谢。

如果您的查询有误，请不要抱怨性能。 - wildplasser
@wildplasser 谢谢你的帮助 :))) - xalo
在我看来，如果其中一个连接部分返回(actual time=0.002..0.002 rows=0 loops=20)，那么主查询不可能返回任何内容。而错误的部分是and variablein3_.ID_=stringinst1_.ID_。 - wildplasser
@wildplasser 目前查询返回了行 :/ 这可能是 PostgreSQL 缓存 bug 吗？服务器正在 Docker 容器内运行。 - xalo
在jbpm_variableinstance上使用btree创建索引idx_varinst_task_name_string(taskinstance_, name_, stringvalue_)。这是EAV模型中Value表的自然键。(id_是代理)由于Id_是唯一的(PK)，通过variablein3_.ID_=stringinst1_.ID_自连接只能成功地针对variableinstance表的同一行(或者没有，因为其他条件可能会冲突)。 - joop
1个回答
5
这通常只会发生在涉及OFFSET和/或LIMIT的情况下（就像这里一样）。
与使用ASC排序的查询相比，使用DESC时EXPLAIN输出中的这行是主要区别：

Rows Removed by Filter: 906237

这意味着，虽然在索引totoidx10中向后扫描时前10行匹配（显然符合你的升序排序），但当向前扫描同一索引时，Postgres必须过滤近900k行才能最终找到符合条件的行。
一个匹配的多列索引（具有正确的排序顺序）可能会大有帮助。
或者，由于Postgres选择了一个不利的查询计划，也许只需更新（或更详细的）表统计信息或成本设置。
相关：
我已经尝试了很多索引（例如：CREATE INDEX totoidx10 ON JBPM_VARIABLEINSTANCE(STRINGVALUE_ DESC) WHERE CLASS_='S'; CREATE INDEX totoidx11 ON JBPM_VARIABLEINSTANCE(STRINGVALUE_ ASC) WHERE CLASS_='S';），但都没有找到有效的。你有什么线索可以提供吗？无论如何，感谢你的完整回答 :) - xalo
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接