pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Full text search
5+ messages / 5 participants
[nested] [flat]

* Full text search
@ 2001-11-13 20:26 Jocelyn Callier <callier@vdl2.ca>
  2001-11-13 21:33 ` Re: Full text search Josh Berkus <josh@agliodbs.com>
  2001-11-13 21:46 ` Re: Full text search Jason Earl <jason.earl@simplot.com>
  0 siblings, 2 replies; 5+ messages in thread

From: Jocelyn Callier @ 2001-11-13 20:26 UTC (permalink / raw)
  To: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Full text search
  2001-11-13 20:26 Full text search Jocelyn Callier <callier@vdl2.ca>
@ 2001-11-13 21:33 ` Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Josh Berkus @ 2001-11-13 21:33 UTC (permalink / raw)
  To: Jocelyn Callier <callier@vdl2.ca>; pgsql-sql

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 information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Full text search
  2001-11-13 20:26 Full text search Jocelyn Callier <callier@vdl2.ca>
@ 2001-11-13 21:46 ` Jason Earl <jason.earl@simplot.com>
  2001-11-14 15:06   ` Re: Full text search Roberto Mello <rmello@cc.usu.edu>
  1 sibling, 1 reply; 5+ messages in thread

From: Jason Earl @ 2001-11-13 21:46 UTC (permalink / raw)
  To: Jocelyn Callier <callier@vdl2.ca>; +Cc: pgsql-sql


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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Full text search
  2001-11-13 20:26 Full text search Jocelyn Callier <callier@vdl2.ca>
  2001-11-13 21:46 ` Re: Full text search Jason Earl <jason.earl@simplot.com>
@ 2001-11-14 15:06   ` Roberto Mello <rmello@cc.usu.edu>
  2001-11-14 15:58     ` Re: Full text search Oleg Bartunov <oleg@sai.msu.su>
  0 siblings, 1 reply; 5+ messages in thread

From: Roberto Mello @ 2001-11-14 15:06 UTC (permalink / raw)
  To: Jason Earl <jason.earl@simplot.com>; +Cc: Jocelyn Callier <callier@vdl2.ca>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Full text search
  2001-11-13 20:26 Full text search Jocelyn Callier <callier@vdl2.ca>
  2001-11-13 21:46 ` Re: Full text search Jason Earl <jason.earl@simplot.com>
  2001-11-14 15:06   ` Re: Full text search Roberto Mello <rmello@cc.usu.edu>
@ 2001-11-14 15:58     ` Oleg Bartunov <oleg@sai.msu.su>
  0 siblings, 0 replies; 5+ messages in thread

From: Oleg Bartunov @ 2001-11-14 15:58 UTC (permalink / raw)
  To: Roberto Mello <rmello@cc.usu.edu>; +Cc: Jason Earl <jason.earl@simplot.com>; Jocelyn Callier <callier@vdl2.ca>; pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2001-11-14 15:58 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-13 20:26 Full text search Jocelyn Callier <callier@vdl2.ca>
2001-11-13 21:33 ` Josh Berkus <josh@agliodbs.com>
2001-11-13 21:46 ` Jason Earl <jason.earl@simplot.com>
2001-11-14 15:06   ` Roberto Mello <rmello@cc.usu.edu>
2001-11-14 15:58     ` Oleg Bartunov <oleg@sai.msu.su>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox