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 D0A0B9FA5C3 for ; Wed, 14 Jun 2006 04:08:11 -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 13646-05 for ; Wed, 14 Jun 2006 04:08:01 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from ra.sai.msu.su (ra.sai.msu.su [158.250.29.2]) by postgresql.org (Postfix) with ESMTP id 241879FA3C0 for ; Wed, 14 Jun 2006 04:07:54 -0300 (ADT) Received: from ra (ra [158.250.29.2]) by ra.sai.msu.su (8.13.4/8.13.4) with ESMTP id k5E77gYl000862; Wed, 14 Jun 2006 11:07:42 +0400 (MSD) Date: Wed, 14 Jun 2006 11:07:41 +0400 (MSD) From: Oleg Bartunov X-X-Sender: megera@ra.sai.msu.su To: Aaron Bono cc: PostgreSQL Subject: Re: Efficient Searching of Large Text Fields In-Reply-To: Message-ID: References: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/151 X-Sequence-Number: 25387 Don't invent a wheel and use contrib/tsearch2 for that. On Tue, 13 Jun 2006, 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? > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > Regards, Oleg _____________________________________________________________ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83