如何优化SQL中合并两个返回table_a结果的查询？

你的判断完全正确，用UNION/UNION ALL合并两个独立查询，比原有的LEFT JOIN写法高效得多——原写法里的OR条件会让数据库难以有效利用索引，且LEFT JOIN带来了不必要的关联开销，这就是耗时飙升的核心原因。

优化后的函数代码 #

CREATE OR REPLACE FUNCTION my_custom_function(search text)
 RETURNS SETOF table_a
 LANGUAGE sql
 STABLE
AS $function$
    -- 匹配table_a自身的name
    SELECT table_a.*
    FROM table_a
    WHERE table_a.name ILIKE '%' || search || '%'
    UNION ALL
    -- 匹配table_b的name并关联回table_a
    SELECT table_a.*
    FROM table_a
    INNER JOIN table_b ON table_a.person_id = table_b.person_id
    WHERE table_b.name ILIKE '%' || search || '%'
    ORDER BY name DESC;
$function$

关键细节说明 #

1. 为什么用UNION ALL：两个查询各自独立执行，能分别利用table_a(name)和table_b(name)上的索引（如果没有索引，建议立即创建），彻底避免了原写法中OR条件导致的索引失效问题，执行效率和你分开查询的50ms接近。
2. 去重处理：如果存在table_a的某一行同时满足两个查询条件的情况（比如某行自身name包含搜索词，且关联的table_b name也包含），UNION ALL会返回重复行。如果需要去重，把UNION ALL改成UNION即可，但UNION会有额外的去重开销，优先用UNION ALL。
3. 搜索规则适配：你的示例是模糊搜索，所以用ILIKE '%' || search || '%'（不区分大小写）；如果是精确匹配，直接替换成= search即可。

额外提速建议 #

为了进一步缩短耗时，建议创建以下索引：

• CREATE INDEX idx_table_a_name ON table_a(name);
• CREATE INDEX idx_table_b_name ON table_b(name);
• CREATE INDEX idx_table_a_person_id ON table_a(person_id);

内容的提问来源于stack exchange，提问作者Charklewis