Re: OK, does anyone have any better ideas?

Oleg Bartunov wrote:
>
> On Sat, 9 Dec 2000, Edmar Wiggers wrote:
>
> > Date: Sat, 9 Dec 2000 14:20:17 -0200
> > From: Edmar Wiggers <edmar(at)brasmap(dot)com>
> > To: mlw <markw(at)mohawksoft(dot)com>, Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
> > Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,
> > Hackers List <pgsql-hackers(at)postgresql(dot)org>
> > Subject: RE: [HACKERS] OK, does anyone have any better ideas?
> >
> >
> > One possible idea for SQL integration: can one use index access-method
> > functions to query the FTS outside the database? Yes, it would require some
> > work, but the results I guess it would be wonderful. Ok, Tom, not so fast as
> > an index sitting inside Postgres, but AFAICS that's not going to happen
> > anytime soon.
>
> We did external indexing using suffix arrays ( http://sary.namazu.org )
> for one project because of limiting time :-) But we had to do a lot
> of work like ACID (well, we already have some technology) and
> everything we get usually from SQL.
> Now we're trying to implement fast indexing using GiST.

I think I have the answer, or at least as good as I think I can get in
the near term.

The syntax is as follows:

create temp table search_results as select ts_key(10) as "key",
ts_rank(10) as "rank" from ts_template where ts_search('bla bla bla',
10)>0;

select * from table where search_results.key = table.field;

It is not ideal, obviously, but it solves a couple problems, and should
not be too big a performance hit. (If ANYONE can come up with a better
way, I'd really really love to hear it.)

The first call to ts_search(...) does the search and saves the results.
Each successive call simply advances the result number. ts_key() and
ts_rank() work on the current result number and return the respective
value. ts_template is a table of some maximum number of records plus 1,
say 1001.

When the total number of results have been returned (or maximum has been
reached), ts_search frees the search results (because they should be
saved in the table) and returns 0, stopping the select call.

Anyone see any problems with this approach? It is not ideal, but it is
as efficient as I can come up with, without spending a year or two
creating a new Postgres index type.

--
http://www.mohawksoft.com