Re: Big table with UNION ALL or partitioning with Tsearch2

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