CODEWITHSUNDEEP
postgresqlsymfonydoctrine-ormdoctrine-orm-postgres
Eddie
Eddie

Reputation: 1643

PostgreSQL index for like 'abc%' searching

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.

Upvotes: 15

Views: 9903

Answers (1)

Daniel Vérité
Daniel Vérité

Reputation: 61656

  • 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.

Upvotes: 19

Related Questions