Re: Tsearch2 / Create rule on select

On Thu, Mar 08, 2007 at 09:56:23PM +0100, Jean-Michel Pouré wrote:
> Dear Friends,
>
> I am very impressed by TSearch2 and would like to thank Oleg and the
> team for their hard work.
>
> I would like to migrate a phpBB forum with more that 200.000 messages to
> TSeach. Full text searches have become a bottleneck. When there are more
> than 200 users, the server slow dramatically. I need TSearch!
>
> So ... I installed TSearch2, a French dictionnary, everything is okay.
> My database is PostgreSQL 8.2.3, UTF-8.
>
> My questions now:
> 1) Should I migrate phpBB to TSeach2 in PHP code, rewriting the queries,
> which is quite obvious or easy.

Yes.

> OR
>
> 2) Is there a smart way to catch queries on the fly in PostgreSQL, for
> example using
>
> "CREATE RULE "_RETURN" AS
> ON SELECT TO t1
> DO INSTEAD "
>
> or the like.

I don't think you can do that on-the-fly. But even if you can, if it's
reasonably easy to rewrite the query in the PHP, you should do that
instead IMHO.

> 3) Gin indexes
> Are Gin indexes recommended for large databases?

Yes. At least if you have lots of search activity, they can be a *lot*
faster than GiST (see for example
http://people.planetpostgresql.org/mha/index.php?/archives/112-GIN-performance-postgresql.org-websearch-part-2.html)

> How to calculate the size in memory of an index?

No idea. Assuming you want to do it beforehand. otherwise, just create
the index and see how large it got?

//Magnus