Re: Big table with UNION ALL or partitioning with Tsearch2

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

RAID 5 with 16 spindles? RAID 10 will give you better performance I
would think.

> -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)

Assuming 8.1+ I would try something much more aggressive, like 4GB.
Dont' forget your effective_cache_size.

Joshua D. Drake

> -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
>

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/