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 по дате отправления:

Предыдущее
От: Denis Papathanasiou
Дата:
Сообщение: Optimal indexing of Full Text Search (ts_vector & ts_query) columns?
Следующее
От: Bruce Momjian
Дата:
Сообщение: Re: Warm Standby and resetting the primary as a standby