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 <postg...@2xlp.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
// Jonathan Vanasco
c. 646.729.6436 | 415.501.9815
e. jonat...@2xlp.com
w. http://findmeon.com/user/jvanasco
linkedin. http://linkedin.com/in/jonathanvanasco
blog. http://destructuring.net
