After the String Processing Function Is Used, the Query Does Not Use the Index. What Should I Do?

Answer: After a function is used to process an index column, the standard index is not used. You can create an expression index.

Example:

-- Create a table and insert data into the table.
gaussdb=# CREATE TABLE tbl_test (c1 varchar);
gaussdb=# INSERT INTO tbl_test VALUES (generate_series(1,10000));

-- The lpad() function is used for a common index, and the index is not used for query.
gaussdb=# CREATE INDEX idx_test_c1 ON tbl_test(c1);
-- Part of the execution plan is as follows:
gaussdb=# EXPLAIN ANALYZE SELECT * FROM tbl_test WHERE lpad(c1,5,'0') = '00324';
 id |          operation           |    A-time     | A-rows | E-rows | Peak Memory | A-width | E-width | E-costs 
----+------------------------------+---------------+--------+--------+-------------+---------+---------+---------
  1 | ->  Streaming (type: GATHER) | 10.594        |      1 |      1 | 79KB        |         |       4 | 63.12
  2 |    ->  Seq Scan on tbl_test  | [1.674,2.418] |      1 |      1 | [15KB,56KB] |         |       4 | 62.99
(2 rows)

          Predicate Information (identified by plan id)           
------------------------------------------------------------------
   2 --Seq Scan on tbl_test
         Filter: (lpad((c1)::text, 5, '0'::text) = '00324'::text)
         Rows Removed by Filter: 9999
(3 rows)

-- After the index is changed to an expression index, the index is used for query.
gaussdb=# DROP INDEX idx_test_c1;
gaussdb=# CREATE INDEX idx_test_c1 ON tbl_test(lpad(c1,5,'0'));
gaussdb=# EXPLAIN ANALYZE SELECT * FROM tbl_test WHERE lpad(c1,5,'0') = '00324';
 id |                    operation                    |    A-time     | A-rows | E-rows | Peak Memory | A-width | E-width | E-costs 
----+-------------------------------------------------+---------------+--------+--------+-------------+---------+---------+---------
  1 | ->  Streaming (type: GATHER)                    | 4.632         |      1 |      1 | 80KB        |         |       4 | 8.39
  2 |    ->  Index Scan using idx_test_c1 on tbl_test | [0.168,0.194] |      1 |      1 | [70KB,70KB] |         |       4 | 8.27
(2 rows)

            Predicate Information (identified by plan id)             
----------------------------------------------------------------------
   2 --Index Scan using idx_test_c1 on tbl_test
         Index Cond: (lpad((c1)::text, 5, '0'::text) = '00324'::text)
(2 rows)
-- Delete.
gaussdb=# DROP INDEX idx_test_c1;
gaussdb=# DROP TABLE tbl_test;