PostgreSQL为含true=any()的不可修改查询创建优化索引方法

不用特意为true=any()运算符设计特殊索引，PostgreSQL优化器会把这个相关子查询逻辑转成半连接（Semi Join）执行，本质是判断主表每一行是否存在至少一条满足子查询条件的关联记录，只要给两个涉及的表按过滤、关联、返回字段的顺序建联合覆盖索引，就能把执行效率提上来。

主表communication.vwallcommunication 索引 #

主表先按projectid = 750过滤数据，再拿communicationviewid、type两个字段和子表关联，最后按assignedto分组返回结果，直接建如下联合索引：

CREATE INDEX idx_vwallcommunication_proj_view_type_assigned 
ON communication.vwallcommunication (projectid, communicationviewid, type, assignedto);

索引字段顺序的逻辑：

• 最左侧放过滤条件projectid，直接定位到projectid=750的所有数据，避免全表扫描
• 中间放关联需要的communicationviewid、type，关联子查询时不需要回表查主表数据
• 最后放分组返回的assignedto，做覆盖索引，group by阶段直接从索引取数，凑够limit 100的结果就可以提前终止扫描，不用扫完所有符合条件的数据。

子查询表communication.vwclaimdetails 索引 #

子查询需要按和主表关联的communicationviewid、type定位记录，再判断lower(claimnumber) = lower('193')的条件，这里因为对claimnumber做了lower()函数转换，普通Btree索引命中不了，要建带表达式的联合索引：

CREATE INDEX idx_vwclaimdetails_view_type_lowerclaim
ON communication.vwclaimdetails (communicationviewid, type, lower(claimnumber));

索引字段顺序的逻辑：

• 最左侧两个字段放和主表关联的键，拿到主表传过来的关联值后可以直接在索引里定位对应范围的记录
• 第三个字段放lower(claimnumber)表达式，直接匹配过滤条件，不需要逐行计算lower函数，也不需要回表查原表数据
• 整个子查询的匹配判断完全在索引上完成，只要找到1条匹配记录就可以终止当前主表行的子查询扫描，半连接的开销会非常低。

注意事项 #

如果vwallcommunication或者vwclaimdetails是视图不是物理表，上述索引要建在视图依赖的实际物理基表上，建在视图上不会生效。

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