X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 23D399FA13E for ; Tue, 13 Jun 2006 17:31:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 23543-07 for ; Tue, 13 Jun 2006 17:30:50 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.170]) by postgresql.org (Postfix) with ESMTP id A94329F9316 for ; Tue, 13 Jun 2006 17:30:50 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id j40so728633ugd for ; Tue, 13 Jun 2006 13:30:46 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition:x-google-sender-auth; b=KZieHgB61/hqW8QBAe7SrhSLAlXJiQUbku0DgZS9I3MESjqnnM+BTRUUf/jnjVM3PrvpEV78bOE+HsZzdWZBQ5MGzEdEGPLaAkX5RXv1sa1rXECh5xR5QoDgX85hX0wwv3hJUAcQGv9A8B6l1C+KnjymAyw6jVpJx8BUUeYI/oY= Received: by 10.66.216.6 with SMTP id o6mr6664711ugg; Tue, 13 Jun 2006 13:30:44 -0700 (PDT) Received: by 10.67.20.12 with HTTP; Tue, 13 Jun 2006 13:30:44 -0700 (PDT) Message-ID: Date: Tue, 13 Jun 2006 15:30:44 -0500 From: "Aaron Bono" To: PostgreSQL Subject: Efficient Searching of Large Text Fields MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Google-Sender-Auth: ab481ed7b4973266 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/136 X-Sequence-Number: 25372 In another post on a different topic, Rod Taylor said the following: "A\tcat in the\nhat" might be stored as ARRAY['A', 'cat', 'in', 'the', 'hat']. This got me thinking. I have a discussion forum for gamers and want to provide searching capabilities so the user can type in a phrase like "magical bow" and get all posts, sorted by relevance that contain these words. My questions are: 1. Will storing the posts in an ARRAY help improve performance of these searches? If so, by how much? 2. What functions or libraries are available to make such searching easy to implement well? 3. What is the best way to sort by relevance? Thanks, Aaron Bono