pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFull 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