为带前后通配符的ILIKE查询创建XML列索引的问题

我有一条存在性能问题的查询，正尝试为其创建索引：

SELECT inst.policydraftinstanceid, Client.*, 
    COALESCE( ssn, tin ) TaxId
    FROM ypolicydraftinstance inst, XMLTABLE( '/policy-draft-xml/policy/clients/nb-client/roles/role' PASSING policydraftxml COLUMNS 
       name text PATH '../../client/detail/name',
       clientCompositeId text PATH '../../@client-composite-id',
       clientType text PATH '../../client/detail/@type',
       roleType text PATH '@type',
       ssn text PATH '../../client/detail/tax-id/@value',
       tin text PATH '../../client/detail/tax-id/@value',
       dateOfBirth text PATH '../../client/detail/date-of-birth'
       ) Client
     WHERE  Client.Name  ILIKE  '%829116417%' ... more comparisons....

我尝试执行以下索引创建语句，但因xpath并非不可变函数而无法运行：

CREATE INDEX test_idx1 ON ypolicydraftinstance USING GIN ( CAST( xpath('/policy-draft-xml/policy/clients/nb-client/client/detail/name/text()', policydraftxml) AS TEXT) gin_trgm_ops ); 

想请教两个问题：

1. 是否可以用不可变转换包装该函数？如何有效实现？（注：名称不常变更但仍可能修改，并非完全不可变）
2. 还有哪些性能优化或索引创建的建议？

一、用不可变函数包装xpath #

PostgreSQL要求索引列依赖的函数必须是IMMUTABLE，而原生xpath是STABLE类型（因为XML处理可能依赖环境配置等变量）。可以创建自定义不可变函数来包装它，但要注意：如果后续XML内容或结构发生修改，需确保函数逻辑能适配，否则可能出现索引与实际数据不一致的情况。

具体实现步骤：

1. 创建不可变包装函数：

CREATE OR REPLACE FUNCTION extract_client_names(xml_data xml)
RETURNS text[]
LANGUAGE sql
IMMUTABLE
AS $$
SELECT xpath('/policy-draft-xml/policy/clients/nb-client/client/detail/name/text()', xml_data);
$$;

2. 基于该函数创建GIN索引：

CREATE INDEX idx_ypolicydraftinstance_client_names 
ON ypolicydraftinstance 
USING GIN (extract_client_names(policydraftxml) gin_trgm_ops);

3. 调整查询语句以命中索引：
   需要让查询逻辑匹配索引的函数返回值，例如用EXISTS子句关联函数结果：

SELECT inst.policydraftinstanceid, Client.*, 
    COALESCE( ssn, tin ) TaxId
FROM ypolicydraftinstance inst, 
XMLTABLE( '/policy-draft-xml/policy/clients/nb-client/roles/role' PASSING policydraftxml COLUMNS 
       name text PATH '../../client/detail/name',
       clientCompositeId text PATH '../../@client-composite-id',
       clientType text PATH '../../client/detail/@type',
       roleType text PATH '@type',
       ssn text PATH '../../client/detail/tax-id/@value',
       tin text PATH '../../client/detail/tax-id/@value',
       dateOfBirth text PATH '../../client/detail/date-of-birth'
       ) Client
WHERE EXISTS (
    SELECT 1 
    FROM unnest(extract_client_names(inst.policydraftxml)) AS names(name)
    WHERE name ILIKE '%829116417%'
)
... -- 其他过滤条件

二、其他性能优化建议 #

• 将XML数据拆解到关系表：如果客户端数据访问频繁，建议把XML中的客户端字段同步到单独的关系表（如policy_clients），包含policydraftinstanceid、name、clientCompositeId等字段。直接在关系表的name字段创建GIN/GIST索引，查询效率远高于XML索引，且维护更简单。可以用触发器自动同步XML数据到关系表，保证数据一致性。
• 优化XMLTABLE的XPath路径：当前查询中使用../../向上回溯节点，会增加解析开销。建议拆分XMLTABLE，先定位到nb-client节点，再关联role节点，使用更直接的路径：

XMLTABLE( '/policy-draft-xml/policy/clients/nb-client' PASSING policydraftxml 
COLUMNS 
    clientCompositeId text PATH '@client-composite-id',
    name text PATH 'client/detail/name',
    clientType text PATH 'client/detail/@type',
    roles XML PATH 'roles/role'
) AS nb_client,
XMLTABLE( '/role' PASSING nb_client.roles 
COLUMNS 
    roleType text PATH '@type'
) AS roles

• 选择合适的索引类型：如果数据量不大，GIST索引的写入性能优于GIN，PostgreSQL 9.6+也支持GIST的trgm_ops，可根据业务场景切换。
• 分析查询计划：执行EXPLAIN ANALYZE查看查询瓶颈，确认是XML解析慢还是过滤条件效率低，针对性优化。比如全表扫描XML的话，优先做索引优化；如果是XMLTABLE解析耗时，优先考虑拆解到关系表。

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