How to do a prefix lookup in PostgeSQL so that an index is used?

Question

PostgreSQL

I need to search by prefix, by exact match.
The result can be obtained by such queries.
SELECT * FROM regions WHERE name ILIKE 'екат%';

SELECT * FROM regions WHERE LOWER(name) LIKE 'екат%';

A simple btree field index works with LIKE, but doesn't work with ILIKE.
Functional index lower(name), works on exact match, but does not work with "LOWER(name) LIKE 'ecat%';".
You can also use full text search:

SELECT * FROM geobase_region WHERE fts @@ to_tsquery('simple', 'заречный:*');

But in the case of full-text, crap is added with updating the index, well, there are some proeduras to write. Maybe it's possible without it?

Answer 1

My index works in this case:

CREATE INDEX addr_lower_idx
  ON public.addr
  USING btree
  (lower(factaddr) COLLATE pg_catalog."default" text_pattern_ops);

explain analyze
select * from addr 
where lower(factaddr) like lower('Моск%');

Result:

Bitmap Heap Scan on addr  (cost=22.40..490.53 rows=194 width=150) (actual time=0.110..0.110 rows=0 loops=1)
  Filter: (lower(factaddr) ~~ 'моск%'::text)
  ->  Bitmap Index Scan on addr_lower_idx  (cost=0.00..22.36 rows=194 width=0) (actual time=0.108..0.108 rows=0 loops=1)
        Index Cond: ((lower(factaddr) ~>=~ 'моск'::text) AND (lower(factaddr) ~<~ 'мосл'::text))
Planning time: 0.174 ms
Execution time: 0.173 ms

Perhaps your index was not being pulled up because text_pattern_ops was not used ?
Experimented with text search here