SQL 性能，在 SELECT 和 WHERE 条件中什么执行速度更快

Question

更新：糟糕！它不是一个整数，它是字符变化（10）

像这样执行查询使用索引

SELECT t."FieldID"
FROM table t
WHERE t."FieldID" = '0123456789'

则不使用索引

SELECT t."FieldID"
FROM table t
WHERE t."FieldID" LIKE '01%'

但是如果我执行这个或这个

SELECT t."FieldID"
FROM table t
WHERE "substring"(t."FieldID", 0, 3) = '01'

也这个

SELECT t."FieldID"
FROM table t
WHERE t."FieldID" ~ '^01'

我的索引看起来像这样

CREATE UNIQUE INDEX fieldid_index
  ON "table"
  USING btree
  ("FieldID");

运行PostgreSQL 7.4（是的升级）

我正在优化我的查询并想知道在语句的 SELECT 或 WHERE 子句中使用三种类型的表达式之一是否有任何性能提升。

注意：使用这些约束样式执行的查询返回大约 200,000 条记录

示例数据是一个字符变化(10)：0123456789并且它也被索引

1 。 （子字符串）

SELECT CASE
    WHEN "substring"(t."FieldID"::text, 0, 3) = '01'::text         
    THEN 'Found Match'::text
    ELSE NULL::text
END AS matching_group

2. （喜欢）

SELECT CASE
    WHEN t."FieldID"::text LIKE '01%'         
    THEN 'Found Match'::text
    ELSE NULL::text
END AS matching_group

3。 （正则表达式）

SELECT CASE
    WHEN t."FieldID" ~ '^01'         
    THEN 'Found Match'::text
    ELSE NULL::text
END AS matching_group

另外，在 WHERE 子句中使用其中一种相对于另一种是否有任何性能优势？

1. （子字符串）

WHERE CASE
    WHEN "substring"(t."FieldID"::text, 0, 3) = '01'::text         
    THEN 1
    ELSE 0
END = 1

2. （喜欢）

WHERE CASE
    WHEN t."FieldID"::text LIKE '01%'         
    THEN 1
    ELSE 0
END = 1

3。 (RegEx)

WHERE CASE
    WHEN t."FieldID" ~ '^01'         
    THEN 1
    ELSE 0
END = 1

在 SELECT 中使用一个选项并在 WHERE 子句中使用不同的选项会提高性能吗？

Answer 1

我个人认为，制造这种问题的人不应该被允许使用“表现”这个词。恕我直言，对数字字段（甚至可能是键字段）内容的文本表示的限制（如 WHERE 子句中的限制）表明设计不好。

如果这是我的数据，我会向记录添加一个标志字段，指示查询 xyz 中想要/不想要。人们甚至可以将其放入一张单独的表中。我更喜欢添加一个（冗余？）列来创建基于 GW-basic-substring 垃圾的整个索引。

Answer 2

最有效的两件事是索引和控制能力。 可控制性意味着使用可以利用索引的表达式。您可以通过使用

ANALYZE your_first_table;
-- ANALYZE other tables used in this query.
EXPLAIN ANALYZE
SELECT ...

查看文档检查索引使用情况 来衡量它们的效果。

您也许可以利用表达式索引或< a href="http://www.postgresql.org/docs/9.1/static/indexes-partial.html" rel="nofollow">部分索引。 PostgreSQL 7.4 支持表达式索引和部分索引。为了进行测试，您可以阻止某些各种查询计划。 （也在 7.4 中。）

基于表达式的索引可能适合您：

create index firsttwochars
on your-table-name (substring(your-column-name from 1 for 2));

但是您仍然需要测试您的查询以查看它们是否实际使用索引。 （无论它们可控制。）这个可能会起作用。

select your-column-name 
from your-table-name 
where substring(your-column-name from 1 for 2) = '01'

前两个字符上没有索引的查询计划。 （我的测试表使用随机纯文本用户名，这就是我搜索“ab”而不是“01”的原因。）

Seq Scan on substring  (cost=0.00..205.00 rows=50 width=11) (actual time=0.315..4.377 rows=14 loops=1)
  Filter: (substring((username)::text, 1, 2) = 'ab'::text)
Total runtime: 4.414 ms

查询计划前两个字符的索引。

Bitmap Heap Scan on substring  (cost=4.36..37.61 rows=14 width=11) (actual time=0.036..0.056 rows=14 loops=1)
  Recheck Cond: (substring((username)::text, 1, 2) = 'ab'::text)
  ->  Bitmap Index Scan on firsttwochars  (cost=0.00..4.36 rows=14 width=0) (actual time=0.028..0.028 rows=14 loops=1)
        Index Cond: (substring((username)::text, 1, 2) = 'ab'::text)
Total runtime: 0.098 ms

Answer 3

在 SQL Server 中，带有 LIKE '01%' 的版本将是可控制的。它实际上将这些不带通配符的 LIKE 查询转换为范围查询。

执行计划将查找谓词显示为 YourCol >= '01' AND YourCol < '02' 也许类似的重写可以在 Postgresql 中有所帮助？

Answer 4

在选择列表中，这三个表达式之间可能不会有太大区别。都是CPU时间。

对于 WHERE 子句，您可以添加一个表达式索引，例如

CREATE INDEX foo ON sometable ((
CASE
    WHEN "substring"("FieldID"::text, 0, 3) = '01'::text         
    THEN 1
    ELSE 0
END
));

，但这种布尔索引的选择性可能很糟糕，不会引起规划者的兴趣。最好将 WHERE 子句重写为仅

WHERE "substring"("FieldID"::text, 0, 3) = '01'::text

然后对其进行索引。

对于 LIKE 和正则表达式情况，您也可以考虑使用 text_pattern_ops 索引；请参阅文档。

总而言之，我认为您需要对该查询进行一些清理工作。