Thread: Rapid deteriation of performance (might be caused by tsearch?) in 7.3.2

Up until a few days ago I have been running Postgresl 7.2.3 with Tsearch
from the contrib dir, but at various times the performance of the
database would suddenly and rapidly deteriate so that queries which
previously took 500ms then took 8 or 9 seconds.

The only cure is a backup and restore of the database, vacuuming and
analysing does nothing. I even tried rebuilding all indexes once which
didn't seem to help.

This was an annoying but intermittent thing, which happened the last
time this Wednesday. Since I was doing a backup and restore anyway, I
decided to upgrade to 7.3.2 in the hope this might fix the annoying
problem, however it has made it WAY worse.

Rather than going a few weeks (and sometimes months) in between having
to use this fix, I am now having to do it almost every single day. I'm
now lucky if it lasts 24 hours before it brings my website to a total
crawl.

There is nothing special about my database other than the fact that I
use the Tsearch addon. Now if I go and do a bit update to the Tsearch
indexes on a table, with for example:

    UPDATE tblmessages SET strmessageidx=txt2txtidx(strheading || '
' || strmessage);

Then that instantly brings the whole database to a crawl, which no
amount of index rebuilding, vacuuming and analysing helps.

Help! (And sorry if this is the wrong list)



Yours Unwhettedly,
Robert John Shepherd.

Editor
DVD REVIEWER
The UK's BIGGEST Online DVD Magazine
http://www.dvd.reviewer.co.uk

For a copy of my Public PGP key, email: pgp@robertsworld.org.uk

"Robert John Shepherd" <robert@reviewer.co.uk> writes:
> Help! (And sorry if this is the wrong list)

Yes, it's the wrong list.  pgsql-performance would be the place to
discuss this.  We can't help you anyway without more details: show us
the EXPLAIN ANALYZE results for some of the slow queries.  (Ideally
I'd like to see EXPLAIN ANALYZE for the same queries in both fast
and slow states ...)

            regards, tom lane