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 B05E99F9316 for ; Tue, 13 Jun 2006 18:04:16 -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 24608-10 for ; Tue, 13 Jun 2006 18:04:13 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.174]) by postgresql.org (Postfix) with ESMTP id 167AC9FA13E for ; Tue, 13 Jun 2006 18:04:12 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id e2so2730038ugf for ; Tue, 13 Jun 2006 14:04:11 -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:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth; b=YWYrwuJ2fjl4jUyrPut14bdgK7wztiY5c8ouKC7eXfidNHq/0guJdShROhlNCBSUtjK2mgvwB5fG6ed8FlEwPaSWG/hHiBGPxAHTK01r5T2Bd6DJ7T/BrwCfMaDRxRU1W7CaOkMVComXMQ+eYP041xdYqGOHJ2TzLu0yhU8nBNo= Received: by 10.66.252.4 with SMTP id z4mr6656416ugh; Tue, 13 Jun 2006 14:04:11 -0700 (PDT) Received: by 10.67.20.12 with HTTP; Tue, 13 Jun 2006 14:04:11 -0700 (PDT) Message-ID: Date: Tue, 13 Jun 2006 16:04:11 -0500 From: "Aaron Bono" To: "Rod Taylor" Subject: Re: Efficient Searching of Large Text Fields Cc: PostgreSQL In-Reply-To: <1150231421.777.167.camel@home> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <1150231421.777.167.camel@home> X-Google-Sender-Auth: de2807d9527da276 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/139 X-Sequence-Number: 25375 I will look that tsearch (at . It appears their 8.1.x version is still in development and I use PostgreSQL 8.1.3 but it is worth trying - I'm not in a hurry for that feature anyway. I also looked at PHPBB a little - it appears their database stores words but the code is so difficult to dig through I was not sure about their implementation or even what they used it for. Would it be worth the work to save the text into a separate searchable table that kept individual words and word counts or would that be more work and eat up more space than it is worth? You could actually index the words that way and get much quicker searches. Then again, as I read through tsearch, it may make this approach unnecessary... I have also seen what looks like people using search results tables that, after a search is performed, save a list of the results. For example, if I were doing a search of a forum, I could save the search in a table like this: forum_topic forum_topic_id (PK) forum_topic_name etc... forum_topic_search forum_topic_search_id (PK) forum_topic_search_dt forum_topic_search_desc forum_topic_search_results forum_topic_search_results_id (PK) forum_topic_search_id (FK) sort_index (int to tell us the order the results are returned in) forum_topic_id (FK) This way you can allow users to page through the results without having to constantly research or cache the results somewhere in memory. Has anyone tried an approach like this? When do you clean these search tables out? They could get quite large after a while. Thanks! Aaron On 6/13/06, Rod Taylor wrote: > It won't help at all. Fast partial matches against arrays is nearly > impossible. You might take a look at tsearch though.