PostgreSQL index for like 'abc%' searching

Question

I have postgreSQL table with city list (>1M) and I need to search over this table by pattern like 'abc%'. I created B-tree index on city.name column, and here is what i got:

EXPLAIN SELECT * FROM city WHERE NAME ~~* 'Мос%' 
Seq Scan on city  (cost=0.00..44562.62 rows=117 width=131)

And the exact select:

EXPLAIN SELECT * FROM city WHERE NAME = 'Москва' 
Index Scan using city_name_idx on city  (cost=0.43..12.33 rows=2 width=131)

Is there any way to use standart index to achieve good performance on first select?

I am using Symfony2/Doctrine2, so it's not very easy (and I do not want) to implement db-specific things here.

Answer 1

• To speed up LIKE (case sensitive), create an index like this:

  create index indexname on city (name text_pattern_ops);
  

• To speed up ILIKE or ~~* , in addition to LIKE, assuming PostgreSQL 9.1 or newer, create an index like this:

  create index indexname on city  using gin(name gin_trgm_ops);
  

  gin_trgm_ops is provided by the pg_trgm extension that should be added if not already present in the database.