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 EFBE39FA5C3 for ; Wed, 14 Jun 2006 04:17:39 -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 14503-08 for ; Wed, 14 Jun 2006 04:17:36 -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 648D19FA3C0 for ; Wed, 14 Jun 2006 04:17:30 -0300 (ADT) Received: from ra (ra [158.250.29.2]) by ra.sai.msu.su (8.13.4/8.13.4) with ESMTP id k5E7HJ03001407; Wed, 14 Jun 2006 11:17:19 +0400 (MSD) Date: Wed, 14 Jun 2006 11:17:19 +0400 (MSD) From: Oleg Bartunov X-X-Sender: megera@ra.sai.msu.su To: Aaron Bono cc: Rod Taylor , PostgreSQL Subject: Re: Efficient Searching of Large Text Fields In-Reply-To: Message-ID: References: <1150231421.777.167.camel@home> 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/153 X-Sequence-Number: 25389 On Tue, 13 Jun 2006, Aaron Bono wrote: > 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. You might be surprized, but queries in general are very similar. Analyze your search log, after normalization you could estimate the total number of distinct queries. We developed search daemon for the big blog and it worked quite well. > > 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. > > ---------------------------(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