Postgresql 在自定义函数、php 上速度较慢，但​​如果使用带有 gin 索引的文本搜索直接在 psql 上输入则速度很快

Question

我有 3 个表“人员”、“姓名”和“注释”。每个人都有多个名字并有可选的注释。我对名称和注释的某些列进行了全文搜索（见下文），如果我搜索的单词在结果集中或在数据库中，它们就可以完美工作，这适用于自定义函数、php 和 psql。现在的问题是，当我搜索的单词不存在于数据库中时，查询在 php 和自定义函数中变得超级慢，但在 psql 上仍然很快。在psql上不到1s，其他的都在10s以上。

表格：

Person | id, birthday  
Name   | person_id, name, fs_name  
Notes  | person_id, note, fs_note  

除了PK和FK索引之外，fs_name和fs_note上还有Gin索引。

函数/查询

create or replace function queryNameFunc (TEXT)
returns TABLE(id int, name TEXT) as $$

    select id, name
    from person_name pnr
    inner join person pr on (pnr.person_id=pr.id) 
    left join personal_notes psr on (psr.person_id = pr.id) 
    where pr.id in 
        (select distinct(id)
         from person_name pn
         inner join person p on (p.id = pn.person_id)
         left join personal_notes ps on (ps.person_id = p.id)
         where tname @@ to_tsquery($1)
         limit 20);

$$ language SQL;

这里的 where 条件被精简，例如，如果我执行 'john & james' 在 $1 上并且数据在数据库中，那么结果很快，但如果 'john 和 james' 不在数据库中，那么结果很慢。这变得更慢，因为我有 1M 个人记录和 3M+ 姓名记录（所有虚拟记录）。知道如何解决这个问题吗？我尝试重新启动服务器，重新启动 postgresql。

Answer 1

数据库必须在了解有关参数之前准备内部查询。这可能会导致错误的查询计划。要避免函数中出现此问题，请使用 plpgsql 语言并在函数内使用 EXECUTE：

CREATE OR REPLACE FUNCTION queryNameFunc (TEXT) RETURNS TABLE(id INT, name TEXT) AS $
BEGIN
    RETURN QUERY EXECUTE '
        SELECT 
            id, 
            name 
        FROM 
            person_name pnr
                INNER JOIN person pr ON (pnr.person_id=pr.id)
                LEFT JOIN personal_notes psr ON (psr.person_id = pr.id)
               WHERE 
            pr.id IN(
                SELECT 
                    DISTINCT(id) 
                FROM 
                    person_name pn
                        INNER JOIN person p ON (p.id = pn.person_id)
                        LEFT JOIN personal_notes ps ON (ps.person_id = p.id)
                        WHERE tname @@ to_tsquery($1)
                        LIMIT 20)' USING $1;
END;
$ LANGUAGE plpgsql;

这适用于版本 8.4，并且您必须安装 plpgsql：

CREATE LANGUAGE plpgsql;