Re: two questions about fulltext searchign / tsvector indexes

[Jonathan Vanasco <postgres@xxxxxxxx>]

On Jun 10, 2014, at 8:26 AM, Vick Khera wrote:

Thanks so much for this.

We do a lot of searching on this column, so pre-computing seems to be the way.

I'm not worried about disk space for now, and can revisit that later if there is a problem

Just for clarification on this:

Option A (less fast):

create gin index on tsvector(searchable_column)

Option B (faster):

create tsvector column for `searchable_column`

create gin index on searchable_column

On Mon, Jun 9, 2014 at 8:55 PM, Jonathan Vanasco <postgres@xxxxxxxx> wrote:

       I can't figure out which one to use.  This is on a steadily growing table of around 20MM rows that gets 20-80k new records a day, but existing records are rarely updated.

The question as always is a time-space trade-off. How frequently do
you make the full text search? If you do it frequently, then with a
pre-computed tsv column you save all that time per row of computing
the tsvector on every search. If you do it infrequently, the space
savings (and not needing to maintain that column) may benefit you.

Personally in these days of cheap disks I'd go with the dedicated
column. Given that, you want to just have a GIN index on that one
column, and the query you want, given some plain text string like
"fluffy dog" is this:

select plainto_tsquery('fluffy dog') @@ my_tsv_column;

I always use a trigger on insert and update to maintain the ts_vector
column, so there is no doubt of how it was computed by various
programs.


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

// Jonathan Vanasco

c. 646.729.6436  |  415.501.9815

e. jonathan@xxxxxxxx

w. http://findmeon.com/user/jvanasco 

linkedin. http://linkedin.com/in/jonathanvanasco

blog. http://destructuring.net