主页 / dba / 问题 / 345413
Accepted
Hasan Can Saral
Asked: 2025-02-24 13:12:05 +0800 CST

查询中未使用具有用户定义函数的表达式索引

  • 772

我有下表：

CREATE TABLE users (
 first_name VARCHAR(256)  DEFAULT NULL NULL,
 last_name  VARCHAR(256)  DEFAULT NULL NULL,
 full_name  VARCHAR(1024) GENERATED ALWAYS AS
        (CASE
             WHEN first_name IS NULL AND
                  last_name IS NULL THEN NULL
             ELSE
                 (TRIM(COALESCE(first_name, '') || ' ' || COALESCE(last_name, ''))) END) STORED;
);

我有以下“规范化”功能：

CREATE OR REPLACE FUNCTION NORMALIZE_FUNC(IN source VARCHAR)
    RETURNS VARCHAR
    LANGUAGE PLPGSQL
AS
$$
BEGIN

    source := (SELECT COALESCE(source, ''));

    source := (SELECT REPLACE(
                      REPLACE(
                      REPLACE(
                      REPLACE(
                      REPLACE(
                      REPLACE(
                      LOWER(source),
                      'ş', 's'),
                      'ç', 'c'),
                      'ı', 'i'),
                      'ü', 'u'),
                      'ö', 'o'),
                      'ğ', 'g'))::VARCHAR;

    RETURN source;

END
$$
IMMUTABLE;

以及以下索引和查询，其中soundex()来自fuzzystrmatch扩展：

CREATE INDEX users_full_name_soundex_idx ON users (SOUNDEX(NORMALIZE_FUNC(full_name))) WHERE full_name IS NOT NULL;

SELECT *
FROM users
WHERE SOUNDEX(NORMALIZE_FUNC(full_name)) = SOUNDEX(NORMALIZE_FUNC('name surname'))

我对表进行了顺序扫描users。

当我尝试不使用用户定义时NORMALIZE_FUNC：

CREATE INDEX users_full_name_soundex_idx ON users (SOUNDEX(full_name)) WHERE full_name IS NOT NULL;

SELECT *
FROM users
WHERE SOUNDEX(full_name) = SOUNDEX('name surname')

我看到users_full_name_soundex_idx使用了索引，并且查询很快。

我错过了什么？

postgresql

1 个回答

  1. Best Answer

    问题在于WHERE索引定义中的子句，而您没有normalize_func()用 进行定义RETURNS NULL ON NULL INPUT。

    通常，只有当索引的条件也用于查询时，WHERE 才能使用部分索引（带条件的索引）。那么，如果在索引定义和查询中都没有使用，为什么还会使用索引呢？答案是，PostgreSQL 优化器足够聪明，可以做出以下推论：WHEREnormalize_func()

    • soundex('name surname')在查询规划过程中计算的结果'N526'不为 NULL
    • soundex()是一个定义为的函数RETURNS NULL ON NULL INPUT（与相同STRICT），因此对于所有为full_nameNULL 的行，soundex(full_name)也将为 NULL，并且比较不会返回TRUE
    • 由于所有为full_nameNULL 的行都不满足条件，因此只有full_name不为 NULL 的行才能成功，我们可以使用部分索引而不会遗漏任何行

    由于您忘记定义normalize_func()为RETURNS NULL ON NULL INPUT，因此涉及该函数时无法进行该推论。

    您有多种选择：

    • 定义函数为RETURNS NULL ON NULL INPUT：

      ALTER FUNCTION normalize_func() RETURNS NULL ON NULL INPUT;

    • 取消WHERE索引定义中的条件

    • 添加AND full_name IS NOT NULL到查询

    第一个解决方案是最智能的，并且它还可以带来额外的性能优势，因为 PostgreSQL 可以跳过执行 NULL 参数的函数体。

    • 2

相关问题