| From: | Denis Papathanasiou <denis(dot)papathanasiou(at)gmail(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Optimal indexing of Full Text Search (ts_vector & ts_query) columns? |
| Date: | 2010-08-21 16:23:51 |
| Message-ID: | 4C6FFD97.2090103@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | 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);
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Denis Papathanasiou | 2010-08-21 16:26:35 | Optimal indexing of Full Text Search (ts_vector & ts_query) columns? |
| Previous Message | Jonathan Bond-Caron | 2010-08-21 15:13:21 | Re: Could not Store French Accent Marks Correctly in Postgres |