如何在多列索引中结合LEFT与LOWER实现前缀文本匹配？

创建的索引 #

CREATE INDEX rep_tval_idx ON public.rep USING btree (t, lower(left(val, 127)));

执行的查询语句 #

EXPLAIN ANALYZE
select * from rep
where t=3 and lower(left(val, 127)) like 'operation%';

小表的执行计划（索引未完整利用） #

Index Scan using rep_tval_idx on rep  (cost=0.14..3.67 rows=1 width=56) (actual time=0.044..0.045 rows=0 loops=1)
  Index Cond: (t = 3)
  Filter: (lower("left"(val, 127)) ~~ 'operation%'::text)
  Rows Removed by Filter: 16
Planning Time: 0.112 ms
Execution Time: 0.069 ms

表结构 #

CREATE TABLE public.rep (
    id bigserial NOT NULL,
    up int8 NOT NULL,
    t int8 NOT NULL,
    val text NULL,
    CONSTRAINT rep_pk PRIMARY KEY (id)
);

CREATE INDEX rep_tval_idx ON public.rep USING btree (t, lower("left"(val, 127)));
CREATE INDEX rep_upt_idx ON public.rep USING btree (up, t);

问题根源与验证 #

单独使用LOWER或LEFT时索引可正常工作，但测试时仅t=3的条件用到了索引，lower(left(val,127)) like 'operation%'被当作过滤条件而非索引条件。

后续验证发现：问题出在测试用的是小表（仅125行）。当使用大表（310466行）时，索引按预期完整生效，执行计划如下：

Index Scan using rep_tval_idx on rep  (cost=0.42..121.50 rows=120 width=56) (actual time=0.028..0.037 rows=8 loops=1)
  Index Cond: ((t = 3) AND (lower("left"(val, 127)) >= 'operation'::text) AND (lower("left"(val, 127)) < 'operatioo'::text))
  Filter: (lower("left"(val, 127)) ~~ 'operation%'::text)
Planning Time: 0.102 ms
Execution Time: 0.064 ms

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