X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 8A6CDD1B4E1 for ; Wed, 8 Oct 2003 15:49:05 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 83121-06 for ; Wed, 8 Oct 2003 12:48:19 -0300 (ADT) Received: from buckaroo.freeuk.net (buckaroo.freeuk.net [212.126.144.5]) by svr1.postgresql.org (Postfix) with ESMTP id 63555D1B573 for ; Wed, 8 Oct 2003 12:48:18 -0300 (ADT) Received: from claranet by buckaroo.freeuk.net with local (Exim 4.24) id 1A7GXx-0007J5-PM for pgsql-performance@postgresql.org; Wed, 08 Oct 2003 16:48:17 +0100 To: pgsql-performance@postgresql.org Reply-To: psql-mail@freeuk.com Subject: Large Text Search Help Cc: X-Remote_Addr: 212.126.153.165 Precedence: normal Message-Id: From: psql-mail@freeuk.com Date: Wed, 08 Oct 2003 16:48:17 +0100 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/447 X-Sequence-Number: 4195 Hi, I am trying to design a large text search database. It will have upwards of 6 million documents, along with meta data on each. I am currently looking at tsearch2 to provide fast text searching and also playing around with different hardware configurations. 1. With tsearch2 I get very good query times up until I insert more records. For example with 100,000 records tsearch2 returns in around 6 seconds, with 200,000 records tsearch2 returns in just under a minute. Is this due to the indices fitting entirely in memory with 100,000 records? 2. As well as whole word matching i also need to be able to do substring matching. Is the FTI module the way to approach this? 3. I have just begun to look into distibuted queries. Is there an existing solution for distibuting a postgresql database amongst multiple servers, so each has the same schema but only a subset of the total data? Any other helpful comments or sugestions on how to improve query times using different hardware or software techniques would be appreciated. Thanks, Mat