PostgreSQL中视图含CASE+SUBSTR的WHERE过滤能否用到name索引？

不行，原表name列的普通索引没法直接生效。

原因很简单：你在视图里对name列做了包含正则匹配（~）和substring截取的CASE转换，这属于对原列值的计算衍生操作。普通B树索引是基于原列的原始值构建的，只能加速针对原列本身的等值、范围等直接查询。当你查询视图并过滤处理后的name列时，数据库没办法直接用原索引定位数据——因为索引里存的是原始name值，不是经过CASE逻辑转换后的结果。

如果想优化这类查询的性能，有几个可行方向：

1. 创建函数索引：直接基于视图中的CASE表达式，在原表上构建索引。比如执行类似这样的语句：

   CREATE INDEX idx_name_case_transform ON your_table (
       case when name ~ E'^\\d+\\.\\d+\\.\\d+\\.\\d+' then name 
            when name ~ E'\\.(ac|co|gov|ltd|me|net|org|com)\\...$' then substring(name from E'.*?([^.]+.[^.]+.[^.]+)$')
            else substring(name, E'.*?([^.]+.[^.]+)$') end
   );
   

   这样查询视图时过滤处理后的name列，就能匹配这个索引。
2. 预计算存储：在原表新增一列（比如transformed_name），用来存储CASE表达式的计算结果，然后给这个新列创建普通索引。同时用触发器或者定时任务维护这个列的值，确保它和原name列同步更新。
3. 反向推导查询条件（局限性大）：如果业务场景允许，尝试把视图的过滤条件反向转换成针对原name列的查询逻辑，但这种方式在复杂正则匹配的场景下很难落地，实用性不高。

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