Optimal indexing of Full Text Search (ts_vector & ts_query) columns?
| От | Denis Papathanasiou |
|---|---|
| Тема | Optimal indexing of Full Text Search (ts_vector & ts_query) columns? |
| Дата | |
| Msg-id | 4C6FFD97.2090103@gmail.com обсуждение исходный текст |
| Список | pgsql-general |
After reading the documentation on Full Text Search here http://www.postgresql.org/docs/8.4/interactive/textsearch-tables.html I created the following table and index: CREATE TABLE item ( pk uuid primary key, ... [more columns here] ... searchable_text text not null ); CREATE INDEX item_eng_searchable_text_idx ON item USING gin(to_tsvector('english', searchable_text)); Note that unlike the example, I did not want to define the searchable text column in the table as being being of type ts_vector(), because the text is not exclusively English. My question is: when I query the table like this, am I getting the full benefit of the index? select pk from item where searchable_text @@ plainto_tsquery('search phrase'); Also, since there will be cases where the contents of searchable_text will be known exactly, i.e., the query will be: select pk from item where searchable_text = 'exact phrase'; is there any harm in adding a second, regular index on searchable_text like this, or does the gin/ts_vector() index cover me in both types of queries? CREATE INDEX item_searchable_text_idx ON item (searchable_text);
В списке pgsql-general по дате отправления: