Обсуждение: Full text search
Hi all. I'm using Postgres 7.1 to store emails archives. I want to perform a full text search in emails' contents. Emails' content can be quite big (up to 15000 characters). I've already seen that Regex match function '~' is way faster than the classical sql 'LIKE' function. Is there another tip to make the search faster ? Something like automatic indexation of words when insering the text ? Or, I don't know, optimization stuff... ? Any help would be appreciate. Thanks. -------------- Jocelyn Callier callier@vdl2.ca
Jocelyn, > Is there another tip to make the search faster ? Something like > automatic indexation of words when insering the text ? Or, I don't > know, > optimization stuff... ? Unfortunately, Postgres does not currently have a "full text index" feature. I've had an idea how to write one for some time, but haven't had the budget. If anyone wants to fund it ... However, there are some things you can do immediately: 1. Put the e-mail messages in their own table, seperate from the header information. Then move that table to a seperate drive so that that drive can be devoted to only searching e-mail text. (This assumes you're not already using SCSI RAID) 2. Add lots more RAM and increase the sort_mem setting in postgresql.conf. Also the number and size of WAL_FILES (see Techdocs). 3. Alternatively, you can store your e-mails as files and use a 3rd-party full text indexing product. There are several of these on Sourceforge/Freshmeat. However, since that means moving the text our of the database, it may not improve things for you much. -Josh Berkus > > Any help would be appreciate. > > Thanks. > > -------------- > Jocelyn Callier > callier@vdl2.ca > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
You might want to take a look at 7.2 new tsearch module. It sounds like precisely what you are looking for. http://openfts.sourceforge.net/ It's what the PostgreSQL project is using to index their mailing lists. Check out fts.postgresql.org for a nifty demonstration. Jason Jocelyn Callier <callier@vdl2.ca> writes: > Hi all. > I'm using Postgres 7.1 to store emails archives. I want to perform a > full text search in emails' contents. Emails' content can be quite big > (up to 15000 characters). I've already seen that Regex match function > '~' is way faster than the classical sql 'LIKE' function. > > Is there another tip to make the search faster ? Something like > automatic indexation of words when insering the text ? Or, I don't > know, optimization stuff... ? > > Any help would be appreciate. > > Thanks. > > -------------- > Jocelyn Callier > callier@vdl2.ca > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
On Tue, Nov 13, 2001 at 02:46:08PM -0700, Jason Earl wrote: > > You might want to take a look at 7.2 new tsearch module. It sounds > like precisely what you are looking for. > > http://openfts.sourceforge.net/ OpenFTS will work with 7.1 BTW. -Roberto -- +----| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer A seminar on Time Travel will be held two weeks ago
On Wed, 14 Nov 2001, Roberto Mello wrote: > On Tue, Nov 13, 2001 at 02:46:08PM -0700, Jason Earl wrote: > > > > You might want to take a look at 7.2 new tsearch module. It sounds > > like precisely what you are looking for. > > > > http://openfts.sourceforge.net/ > > OpenFTS will work with 7.1 BTW. We plan to use tsearch module in next version of OpenFTS. > > -Roberto > > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83