I frequently rant on this about full text searching.
The fulltextsearch package under contrib is an interesting approach. As anyone
rolling their eyes at my frequent posts, know that I am also working on a full
text search system.
The two packages offer two very different approaches.
"/contrib/fulltextsearch" uses a table of word/oid pairs to find words. Such
that a full text search query would look something like this:
select * from foo where fti_foo.string = 'word' and fti_foo.id = foo.oid;
(or something like that)
Mine has, as yet, has not been contributed. I am dubious that it will happen in
the near future for various reasons. However, it is designed around a
completely different strategy, it is more like a search engine.
Like the fti package, my system parses out a table into component words,
however, rather than storing them word/oid pairs, I use an external file format
which stores a searchable dictionary of words. Each word is associated with a
bitmap.
The bitmap contains a bit for each record. Multiple words in a query are parsed
and the bitmaps retrieved. The bitmaps are then combined with boolean
operations (and/or/not).
Each bit (0...count) is associated with an integer key. The integer key can be
an oid or some other unique table ID.
Being an external system, it is implemented using 'C' based extensions to
postgres. A query on my system looks something like:
select * from table, (select ftss_direct('all { word1 word2 }') as id) as
result, where table.id = result.id;
The advantages of fti is that it, by being tied closely to Postgres, updates
automatically on inserts and updates. The disadvantage is that it is relatively
slow to perform complex searches.
The advantage of my system is that it is very fast, a loaded system can search
the bitmap index of roughly 4 words within 4 million records in about 20ms~50ms
depending on the popularity of the words. (Performance lags at startup) The
disadvantage of my system is that it is not designed to be very dynamic, it
relies extensively on the virtual memory management of the system (and is thus
limited), and not being tied too closely to PostgreSQL, has been somewhat
frustrating to getting it working as well as I would like.
I would love to find a way to get a bitmap like index native to Postgres. I
would, in fact, love and expect to do an amount of it. The problem is to do it
"right" will require a lot of work at very low levels of Postgres.
Is anyone interested in pursuing this?
How should it look?
How deeply rooted in postgres does it need to be?
