FTS GIN Index Question

From: Christian Ramseyer <rc(at)networkz(dot)ch>
To: pgsql-general(at)postgresql(dot)org
Subject: FTS GIN Index Question
Date: 2010-09-29 01:08:07
Message-ID: 4CA29177.8040606@networkz.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi List

I have a largish partitioned table, it has ~60 million records in each
of 12 partitions. It appears that a Full Text Index could speed up some
user queries a lot.

A quick test with an additional tsvector column revealed that this would
take up around 35 GB of space for this column and then maybe 5 more for
the gin index on it. As this is a lot of space (~ 480 GB), I'm a bit
tempted to use a gin index without the separate tsvector column.
However, the doc says that this will be slower.

Does anyone have an idea of how much slower we're talking here? The
index defintion would be a concatenation of two setweights(), i.e.:

... using gin(
(setweight(to_tsvector('config',coalesce(col1,'')), 'A') ||
setweight(to_tsvector('config',coalesce(col2,'')), 'B')))

Also, general recommendations regarding full text search configurations
of that size are very welcome.

Christian

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Itagaki Takahiro 2010-09-29 01:11:00 Re: [HACKERS] Commitfest: The Good, The Bad, and the Ugly
Previous Message Adam Wizon 2010-09-29 00:39:44 Restore problem from 8.4 backup to 9.0