SORT 成本降低了我的查询速度 [英] Cost of SORT is slowing down my query

问题描述

PostgreSQL 7.4(是的升级)

PostgreSQL 7.4 (Yep upgrading)

所以在我的 WHERE 条件下，我有这个

So in my WHERE condition I have this

AND CASE
    WHEN "substring"(t."FieldID"::text, 0, 3) = '01'::text 
        OR "substring"(t."FieldID"::text, 0, 4) = '123'::text 
        OR "substring"(t."FieldID"::text, 0, 5) = '5555'::text 
        OR "substring"(t."FieldID"::text, 0, 6) = '44444'::text 
        OR "substring"(t."FieldID"::text, 0, 3) = '99'::text 
    THEN 1
    ELSE 0
END = 1

替代语法但成本没有变化

Alternate syntax but no change in Cost

AND CASE
    WHEN "substring"(t."FieldID"::text, 0, 3) = '01'::text THEN 1
    WHEN "substring"(t."FieldID"::text, 0, 4) = '123'::text THEN 1
    WHEN "substring"(t."FieldID"::text, 0, 5) = '5555'::text THEN 1
    WHEN "substring"(t."FieldID"::text, 0, 6) = '44444'::text THEN 1
    WHEN "substring"(t."FieldID"::text, 0, 3) = '99'::text THEN 1    
    ELSE 0
END = 1

寻找一种经济有效的方法来通过字符串的开头限制结果.因此，如果字符串以 01、123、5555、44444 或 99 开头，则将其添加到结果集中.

Looking for a Cost effective way to limit the results by the start of a string. So if the string starts with 01, 123, 5555, 44444 or 99 add it to the result set.

有什么想法吗?

注意:FieldID 是索引的查看解释数据以查看查询中的瓶颈，当添加上述代码时，排序的成本会上升并减慢数据集/结果的返回.

Note: the FieldID is indexed Viewing the Explain data to see the bottle necks in the query, when adding the above code is when the cost of the Sort goes way up and slows the return of the data set/results.

解释的输出:

Sort (cost=88716.84..88719.89 rows=822 width=64)

由于查询很复杂，所以还有很多，但如果我删除部分代码，排序成本就会下降

there is a ton more as the query is complex but if I remove the part of the code the Sort cost goes way down

推荐答案

如果你只是按起始字符过滤，你可以使用 like 没有问题，它只会使用一个索引.

If you are just filtering by the starting chars, you can use like with no problem and it will just use an index.

AND (t."FieldID"::text LIKE '01%' OR 
     t."FieldID"::text LIKE '123%' OR 
     t."FieldID"::text LIKE '5555%' OR
     t."FieldID"::text LIKE '44444%' OR
     t."FieldID"::text LIKE '99%')

这篇关于SORT 成本降低了我的查询速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！