PostgreSQL lpad函数索引在多表JOIN场景下的生效问题排查

1. PostgreSQL是否支持在lpad这类函数上创建可被查询正常使用的索引？ #

当然支持。PostgreSQL的B-tree函数索引本身就允许基于不可变（immutable）函数创建，你用的lpad只要入参的填充长度、填充字符是固定值，就属于符合要求的不可变函数，建出来的索引完全可以被查询正常调用。
你之前小数据量测试时没走索引根本不是索引失效，是PG优化器的正常选择：表只有少量数据的时候，顺序扫全表+做Hash Join的成本比走索引低很多——走索引还要多一步找索引页、回表取数的开销，优化器算完执行成本账肯定选更便宜的顺序扫描。等你插了10万条数据之后，全表扫描的成本涨上来，超过走索引的成本，优化器自然就选择索引扫描路径了，和你测出来的耗时从1719.682ms降到433.255ms的现象完全吻合。

提个注意点：建索引的时候lpad的入参（字段、填充长度、填充字符）、字段的排序规则必须和你查询里写的完全一致，不然会出现索引匹配不上的情况。

2. one与two两个子查询之间的JOIN操作，是否实际用到了我创建的lpad函数索引？ #

根本用不到你在四张原始表上建的那几个函数索引。
你建的索引是绑定在sellers、buyers、sellers_2、buyers_2这四张物理表上的，只有直接扫描这四张表、查询条件和索引定义匹配的时候才会被调用。而one和two是子查询计算输出的临时结果集，本身没有任何索引，两个临时结果做关联的时候，优化器只能选择Hash Join、Nested Loop、Merge Join这类不需要临时表携带索引的连接方式。你执行计划里看到的Index Scan逻辑，全是出现在扫描四张原始表、生成one和two两个临时结果的阶段，跟两个子查询之间的Join操作完全无关。
要是想让两个子查询的关联也能走索引，你得先把one、two的结果落到带对应lpad函数索引的临时表或者物化视图上，直接关联未落地的子查询结果的场景，不可能复用原始表上建的函数索引。

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