9

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.

Eddie
  • 1,436
  • 5
  • 24
  • 42
  • It looks like ~~* maps to ILIKE which may not be supported by the B-tree index; try it with LIKE instead. You may also have trouble if your db locale is not 'C'. See http://www.postgresql.org/docs/9.2/static/indexes-types.html for more details. Also see http://stackoverflow.com/questions/12126615/postgresql-ilike-versus-tsearch for a related discussion. – Dmitri Goldring Jul 13 '15 at 19:29
  • citext (case insensitive text) column type can be also helpful for ilike searches. http://www.postgresql.org/docs/9.1/static/citext.html – Tomasz Myrta Jul 13 '15 at 20:18

1 Answers1

13

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

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
  • I used `text_pattern_ops` and get `Index Scan using city_name_idx on city (cost=0.43..8.45 rows=117 width=131)`. I can not use `ILIKE` or `LOWER`, but I can deal with it. Thanks – Eddie Jul 14 '15 at 13:06