The FTI trigger code that's distributed with PostgreSQL now actually breaks
the words up into two character substrings.
I re-wrote it to eliminate duplicates and only split up the words based on
whitespace and delimiters -- if you did this you could still use LIKE to
match based on substrings, then you would have the added speed of an index
scan..
Jules:
select * from table_a where foo like '%bar%'
Depending on the table type of foo, that doesn't have to do a seq scan... If
it's anything but text, you can create an index on it -- LIKE can use
indexes. If it is type text then I would look into using the FTI stuff in
contrib. If you want mine, let me know however it sounds like the
distributed version would be more suited to what you'd like to do.
Good luck!
-Mitch
----- Original Message -----
From: "Steve Heaven" <steve@thornet.co.uk>
To: "Jules Bean" <jules@jellybean.co.uk>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, July 26, 2000 7:39 AM
Subject: Re: [GENERAL] Performance for seq. scans
> At 12:28 26/07/00 +0100, Jules Bean wrote:
> >> We were in a similar position and went for the 'Full Text Indexing"
extra.
> >> You'll find it in contrib/fulltextindex.
> >> It creates a function which you call on a trigger to produce an index
of
> >> words for specified fields. These indexes do get _very_ large (one of
ours
> >> is ~800 MB), but it does work very well and speeds searches up
enormously.
> >
> >If I understand you correctly, that's word-based? It's just splitting
> >on whitespace and punctuation? Unfortunately, that's not quite what
> >we need --- our wildcard searches needn't have their '%' on word
> >boundaries.
> >
>
> There is a function in the source called breakup(). This can be customised
> to create the index entries on sub-word strings.
>
> Steve
>
> --
> thorNET - Internet Consultancy, Services & Training
> Phone: 01454 854413
> Fax: 01454 854412
> http://www.thornet.co.uk
>