Re: Big table with UNION ALL or partitioning with Tsearch2

Oleg,

I am running the latest 8.2.4. I am using GIN. The data is static. I do
a batch upload every week of about 500GB and the ata is never touched
again, it is always add and never delete or update.

From your slides you state:

GIN_FUZZY_SEARCH_LIMIT - maximum number of
returned rows
– GIN_FUZZY_SEARCH_LIMIT=0, disabled on
default

When I do a search with say LIMIT 100 isn't this essentially the same thing?

Benjamin

> Benjamin,
>
> what version of postgres and what type of index you used ?
> The best setup is to use partitioning with rather small table for
> new data and GiST index and big archive table with static data and
> GIN index. I have some slides from PGDay
> http://www.sai.msu.su/~megera/postgres/talks/fts-pgday-2007.pdf
> Also, did you consider using dblink/dbilink to scale your search ?
>
> Oleg
> On Thu, 12 Jul 2007, Benjamin Arai wrote:
>
>> Hi,
>>
>> I have a really big Tsearch2 table (100s GB) that takes a while to
>> perform
>> queries and takes days to index. Is there any way to fix these issues
>> using UNIONs or partitioning? I was thinking that I could partition the
>> data by date but since I am always performing queries on the Tsearch2
>> field I do not know if this will help performance. I think paritioning
>> will help the indexing problem since I can incrementally re-index the
>> data
>> but again I figured it would be better to ask.
>>
>> Any suggestions will be greatly appreciated. Thanks in advance.
>>
>> System I am running on:
>>
>> -Raid 5 with 16x drives
>> -Quad core XEON
>> 16 GB of memory (Any suggestion on the postgresql.conf setup would also
>> be
>> great! Currently I am just setting shared mem to 8192MB)
>> -x86_64 but Redhat 5 Ent
>>
>> Benjamin
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: if posting/reading through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>> message can get through to the mailing list cleanly
>>
>
> Regards,
> Oleg
> _____________________________________________________________
> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru)
> Sternberg Astronomical Institute, Moscow University, Russia
> Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
>
>