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 D724F9FA13E for ; Tue, 13 Jun 2006 17:45:36 -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 24252-09 for ; Tue, 13 Jun 2006 17:45:26 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from twiddle.look.ca (beta1.look.ca [207.136.80.125]) by postgresql.org (Postfix) with ESMTP id F28159FA5FB for ; Tue, 13 Jun 2006 17:45:25 -0300 (ADT) Received: from 209-161-214-225.dsl.look.ca ([209.161.214.225]) by twiddle.look.ca with esmtp (Exim 4.20) id 1FqFlJ-0003Hf-U0; Tue, 13 Jun 2006 20:45:22 +0000 From: Rod Taylor To: Aaron Bono Cc: PostgreSQL In-Reply-To: References: Date: Tue, 13 Jun 2006 16:43:41 -0400 Message-Id: <1150231421.777.167.camel@home> Mime-Version: 1.0 X-Mailer: Evolution 2.6.2 FreeBSD GNOME Team Port X-SA-Exim-Mail-From: pg@rbt.ca Subject: Re: Efficient Searching of Large Text Fields Content-Type: text/plain Content-Transfer-Encoding: 7bit X-SA-Exim-Version: 3.1 (built Tue Feb 24 05:09:27 GMT 2004) X-SA-Exim-Scanned: Yes X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/137 X-Sequence-Number: 25373 On Tue, 2006-06-13 at 15:30 -0500, Aaron Bono wrote: > 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? It won't help at all. Fast partial matches against arrays is nearly impossible. You might take a look at tsearch though. --