Re: two questions about fulltext searchign / tsvector indexes

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(at)2xlp(dot)com> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

// Jonathan Vanasco

c. 646.729.6436 | 415.501.9815
e. jonathan(at)2xlp(dot)com
w. http://findmeon.com/user/jvanasco
linkedin. http://linkedin.com/in/jonathanvanasco
blog. http://destructuring.net