PostgreSQL 13多表UNION视图查询无法命中源表GIN索引如何解决

视图定义在UNION ALL外层嵌套了多余的子查询层，PostgreSQL 13的查询优化器无法针对这种写法做谓词下推：外层查询的WHERE过滤条件不能分发到UNION ALL的每个分支基表，只能先全量扫描三个源表、完成结果拼接后，再在外层统一执行过滤，因此无法命中基表上的GIN索引。从给出的执行计划可以看到，过滤逻辑在Subquery Scan on search_data节点执行，下层三个源表的扫描节点全部是全表顺序扫描，和单表查询时过滤条件直接作用在基表、触发索引扫描的执行逻辑完全不同。

去掉视图里多余的嵌套子查询层，直接将UNION ALL结果作为视图的输出，修改后的视图定义如下：

CREATE OR REPLACE VIEW public.search_view
AS 
SELECT 'foo.'::text || foo.id AS key,
    foo.name AS title,
    foo.information AS content
FROM foo
UNION ALL
SELECT 'bar.'::text || bar.id AS key,
    bar.code AS title,
    bar.info AS content
FROM bar
UNION ALL
SELECT 'baz.'::text || baz.id AS key,
    baz.title AS title,
    baz.text AS content
FROM baz;

修改后重新执行查询，优化器会自动把WHERE content ilike '%lorem%ipsum%dolor%sit%amet%'的过滤条件下推到UNION ALL的每个分支，每个分支的查询逻辑和单独查源表完全一致，会正常命中基表上的GIN三元组索引，执行效率会和单表查询处于同一量级。

如果修改后仍偶发索引不生效，可以做两项检查：

• 确认pg_trgm扩展已在当前库创建（单表查询能走索引说明该项已满足）
• 执行ANALYZE foo; ANALYZE bar; ANALYZE baz;更新三个源表的统计信息，避免优化器因为统计信息偏差，误判全表扫描成本低于索引扫描。

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