Question 421130 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to combine LEFT and LOWER in a multicolumn index for leading text patterns?

I create an index this way:

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

Then I run a SELECT with matching filter:

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

According to EXPLAIN the index would not be used in this case.
How do I make it work with both conditions in the index? The field type is text and I do not want to store more than 127 characters of its content.

EXPLAIN ANALYZE results:

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

There is a single table of this structure, and when I use LOWER or LEFT separately, they work ok with the index.

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);

Update

The problem was, I suppose, caused by testing on a small table.
There are actually 2 different instances with a table in each. One is 125 rows large, while another is 310466 rows. In the bigger table I see that index working as it was expected to do.
Yet, the execution time is still 0.064 ms, though it’s a feature of the quintet data model.

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
Tags:

2

Answers


  1. 0

    btree indexes are, by default, great for <, > or = but can’t be used for pattern matching.

    You must create the index with the text_pattern_ops to index character by character. See it in action in this blog

    Login or Signup to reply.
  2. 0

    Use a COLLATE "C" index. Much like the traditional (obsolete, really) text_pattern_ops index, just simpler and more versatile. See:

    CREATE INDEX rep_tval_c_idx ON rep USING btree (t, lower(left(val, 127)) COLLATE "C");

    Then your current query works as is, with index support on both columns.

    Better

    For your leading search pattern, you can simplify using the "starts with" operator ^@ in Postgres 15+. See:

    SELECT * FROM rep
WHERE  t = 3
AND    lower(left(val, 127)) ^@ 'operation';  -- NO added wild card!

    Typically, you don’t need 127 leading characters to be selective. A much shorter string will do and make the index smaller, faster, and cheaper to maintain. Find the sweat spot for your data distribution and expected filter input. Typically, 10 – 20 characters are plenty.
    Then filter on the shortened string *and on the full string like:

    CREATE INDEX rep_tval_c_idx ON rep USING btree (t, left(val, 10) COLLATE "C");

SELECT * FROM rep
WHERE  t = 3
AND    lower(left(val, 10)) ^@ lower(left($1, 10))  -- bring in idx (logically redundant)
AND    val ^@ $1;  -- preserve full selectivity

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search