X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 986E6D1B54E for ; Tue, 14 Oct 2003 17:16:02 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 50906-01 for ; Tue, 14 Oct 2003 14:15:31 -0300 (ADT) Received: from davinci.ethosmedia.com (unknown [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 9828CD1B542 for ; Tue, 14 Oct 2003 14:15:30 -0300 (ADT) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3767292; Tue, 14 Oct 2003 10:16:08 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: psql-mail@freeuk.com, pgsql-performance@postgresql.org Subject: Re: Large Text Search Help Date: Tue, 14 Oct 2003 10:12:50 -0700 User-Agent: KMail/1.4.3 References: In-Reply-To: MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200310141012.50848.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/457 X-Sequence-Number: 4205 Mat, > 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? Maybe, maybe not. If you want a difinitive answer, post your EXPLAIN ANALYZE results with the original query. I assume that you have run VACUUM ANALYZE, first? Don't bother to respond until you have. > 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? Yes. > 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? No, it would be ad-hoc. So far, Moore's law has prevented us from needing to devote serious effort to the above approach. > Any other helpful comments or sugestions on how to improve query times > using different hardware or software techniques would be appreciated. Read the archives of this list. -- Josh Berkus Aglio Database Solutions San Francisco