indexes for large database in postgres

Question

I have a table in postgres having ~2 million records. I need to provide some index on it such that it gives good performance for like %text% queries.

I read somewhere that Gin indexes are good for %text% searches and so tried Gin and Gist indexes, but don't know why there is no such performance improvement, and Gin index is using sequential scan instead of heap scan.

Here's my Gin index :

CREATE INDEX city_gin_idx_name
  ON city
  USING gin
  (to_tsvector('english'::regconfig, lower(name::text)));

Query performance:

"Sort (cost=117553.00..118496.71 rows=377482 width=50) (actual time=1719.660..1745.702 rows=35185 loops=1)" " Sort Key: (concat(name, ', ', state_name, ', ', country_name))" " Sort Method: external merge Disk: 2200kB" " -> Seq Scan on city (cost=0.00..56777.75 rows=377482 width=50) (actual time=0.392..1474.559 rows=35185 loops=1)" " Filter: ((lower((name)::text) ~~ '%ed%'::text) OR ((city_metaphone)::text = 'K'::text))" " Rows Removed by Filter: 1851806" "Total runtime: 1764.036 ms"

Please tell me any suitable index for this requirement.

Can you please properly format the execution plan (preserving indention)? (e.g. using <pre> tags) As it is it is not really readable. Or upload it to explain.depesz.com — a_horse_with_no_name

Denis de Bernardy Denis de Bernardy · Accepted Answer · 2014-02-13T09:34:12

You need two indexes for that query, and you need to use the exact same expressions in your query to use them:

create index … on city using GIN (to_tsvector('english', name));
create index … on city (city_metaphone);

Note that lowercasing the name is useless in the first index, since to_tsvector will ignore the case anyway when computing vectors.

The query then needs to look like this, and you should get a plan that uses bitmap index scans:

select *
from city
where city_metaphone = 'K'
   or to_tsvector('english', name) @@ to_tsquery('english', 'Katmandu');

That being said, I think your use of full text here is erroneous. Your '%ed%', in particular, indicates that you're hoping that full text will let you run some kind of LIKE comparison.

That is not how it works out of the box, but trigrams will make it work that way:

http://www.postgresql.org/docs/current/static/pgtrgm.html

indexes for large database in postgres

1 Answers