Re: Performance Question Followup No.2

[comments inline...]
On Wed, Nov 07, 2001 at 01:16:57PM +0000, Gordan Bobic wrote:
> After just having split the action into two parts (FTI delete + Master
> delete), it would appear that most of the delay does come from the triggers
> executing.

If I understand correctly, you experience delays when doing UPDATEs or
DELETEs on the master table? (Due to triggers updating the fti table)

I am still using 7.1.3 but am using contrib/fulltextindex from CVS as of a
couple days ago.  One thing worth noting is a change in the documentation that
comes with it. There are also some functionality changes.

In 7.1.3 (release) they suggest ONE index on ("string", "id") while in CVS
they suggest TWO separate indexes.  After doing this change on my database,
I do see a big difference in the time it takes to make an UPDATE.
My understanding is that it is now able to use the 2nd index (on "id") to
remove the old entries in the fti table, while it previously couldn't use
an index. I may be wrong, but I do see a good speedup.

OTOH, I'm also using the newest fti.c (which supports more than 2 arguments,
to index multiple fields into the same fti table), with the old Makefile,
so I'm not sure what makes the biggest difference.

My biggest consumer of fti is a master table with 10963140 records, with a
corresponding fti table of ~350000000 records. (yes, 350 M records) :)

My next step is probably to add an extra argument to fti() before the
list of fields to include, to control whether substrings are included or not.
In my application, complete words would be sufficient..

Has anybody got a patch to fti.c to disable that? My C pointers logic is
rusty...

> Is there an explanation for this? Anything that I should check? Any
> performance tweaks that would improve this situation?

In my case, doing UPDATEs one record at a time on the master table, in
separate transactions, seems to work very well now that I have separate
indexes on the fti table. Perhaps you could try it in a test database,
do some measurements (one index spanning 2 fields vs. 2 separate indexes)
and post your results here?

I'm really looking forward to contrib/tsearch in 7.2. However ISTR the docs
clearly saying it will only work with >= 7.2, but I think it can solve many
of our problems.. I haven't tried beta2 yet, but will setup a test machine
probably next week. I could probably get to a point where I can do some
measurements. Anybody interested in seeing my numbers?

> Sorry to go on about this, but I'm totally puzzled by this.
>
> Regards.
>
> Gordan

HTH
Antoine