Fulltext search on postgres: Weak performance

Dear All,

My boss just complained that fulltext searches (i.e. Content matches)
queries are too slow. Is there any way I could improve the performance?
The server has enough free resources, so I don’t think that’s the
problem.

Thanks, Florian

This message may contain legally privileged or confidential
information and is therefore addressed to the named persons only.
The recipient should inform the sender and delete this message,
if he/she is not named as addressee.
The sender disclaims any and all liability for the integrity
and punctuality of this message.
The sender has activated an automatic virus scanning by
Messagelabs, but does not guarantee the virus free
transmission of this message.

“Florian Hochstrasser” florian.hochstrasser@swisslog.com writes:

My boss just complained that fulltext searches (i.e. Content matches)
queries are too slow. Is there any way I could improve the
performance? The server has enough free resources, so I don’t think
that’s the problem.

Have you tuned the PostgreSQL installation at all?

By default, PostgreSQL comes with a maximum of 8MB of database content
in memory at one time, and will use disk buffers for sorting anything
with > 1MB of content.

Tuning those values up can significantly improve performance on the
database, in general. This is the single most common cause of
"PostgreSQL is slow" complaints; other databases ship with much more
… realistic settings on modern machines.

Failing that, have you considered using ‘analyze explain’ to determine
where the poor performance in the query comes from? Perhaps with that
as a guide, better indexing can be created for your use case.

    Daniel

Nope, haven’t done anything to postgres so far. I’ll check out what I
can do there, thanks for the hint.

I’ll also look into the queries in detail.

Thanks, FlorianFrom: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Daniel
Pittman
Sent: Freitag, 16. September 2005 12:38
To: rt-users@lists.fsck.com
Subject: [rt-users] Re: Fulltext search on postgres: Weak performance

“Florian Hochstrasser” florian.hochstrasser@swisslog.com writes:

My boss just complained that fulltext searches (i.e. Content matches)
queries are too slow. Is there any way I could improve the
performance? The server has enough free resources, so I don’t think
that’s the problem.

Have you tuned the PostgreSQL installation at all?

By default, PostgreSQL comes with a maximum of 8MB of database content
in memory at one time, and will use disk buffers for sorting anything
with > 1MB of content.

Tuning those values up can significantly improve performance on the
database, in general. This is the single most common cause of
"PostgreSQL is slow" complaints; other databases ship with much more
… realistic settings on modern machines.

Failing that, have you considered using ‘analyze explain’ to determine
where the poor performance in the query comes from? Perhaps with that
as a guide, better indexing can be created for your use case.

    Daniel

http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Be sure to check out the RT Wiki at http://wiki.bestpractical.com

Buy your copy of our new book, RT Essentials, today!

Download a free sample chapter from http://rtbook.bestpractical.com

This message has been checked for all known viruses by Messagelabs.

This message may contain legally privileged or confidential
information and is therefore addressed to the named persons only.
The recipient should inform the sender and delete this message,
if he/she is not named as addressee.
The sender disclaims any and all liability for the integrity
and punctuality of this message.
The sender has activated an automatic virus scanning by
Messagelabs, but does not guarantee the virus free
transmission of this message.