Slow Keywords

RT Users
1

Well, I posted this several days ago and got no hits, so I’ll try one more
time.

I’m having an issue with speed on keyword searches.
I can use a query like the following:

SELECT t.* FROM (tickets t LEFT JOIN objectkeywords k ON t.id=k.objectid)
WHERE
k.keyword=10 AND k.keywordselect=3 AND k.objecttype=‘Ticket’ AND t.queue=7;

(I’m assuming this is the query used by the search (or something similar)
This query runs in 2-3 seconds using psql, but the search using the webui
takes at least a minute if not more, then combine that with the time it takes
to move to the next page of results, and it gets very frustrating for the
users. They say that a search on subject content takes less time.
I was wondering if anyone else had noticed this or know how to fix it.

I’m running rt 2.0.13 and PostgreSQL 7.2.1, and Perl 5.6.1
on a box using RedHat 7.2 with 1Gb of RAM and 1.6 Ghz athlon.

Any help is greatly appreciated.

-Michael

2

Michael,

A common solution to slow performance is indexing. Have you applied them?
I realize direct querying is a few orders of magnitude faster, but it will
not hurt. A few other suggestions I’ve seen with positive results are to
turn down the logging level and make sure SearchBuilder is up to date. Have
you tried this?

George-----Original Message-----
From: Michael Grubb [mailto:mgrubb@fifthvision.net]
Sent: Thursday, May 16, 2002 1:42 PM
To: rt-users@lists.fsck.com
Subject: [rt-users] Slow Keywords

Well, I posted this several days ago and got no hits, so I’ll try one more
time.

I’m having an issue with speed on keyword searches.
I can use a query like the following:

SELECT t.* FROM (tickets t LEFT JOIN objectkeywords k ON t.id=k.objectid)
WHERE
k.keyword=10 AND k.keywordselect=3 AND k.objecttype=‘Ticket’ AND t.queue=7;

(I’m assuming this is the query used by the search (or something similar)
This query runs in 2-3 seconds using psql, but the search using the webui
takes at least a minute if not more, then combine that with the time it
takes
to move to the next page of results, and it gets very frustrating for the
users. They say that a search on subject content takes less time.
I was wondering if anyone else had noticed this or know how to fix it.

I’m running rt 2.0.13 and PostgreSQL 7.2.1, and Perl 5.6.1
on a box using RedHat 7.2 with 1Gb of RAM and 1.6 Ghz athlon.

Any help is greatly appreciated.

-Michael

rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm

This email message may contain information that is confidential and
proprietary to Babcock & Brown or a third party. If you are not the
intended recipient, please contact the sender and destroy the original and
any copies of the original message. Babcock & Brown takes measures to
protect the content of its communications. However, Babcock & Brown cannot
guarantee that email messages will not be intercepted by third parties or
that email messages will be free of errors or viruses.

3

I have looked at the indexes, and added a couple that were suggested in the
list archives (most were already present)
The current indexes on the objectkeywords table are:
(objectid, objecttype), (keyword),
unique(objectid,objecttype,keywordselect,keyword),
and of course the primary key (id).
As for the logging level, no I haven’t turned this down (Thank you for the
suggestion)
I just now upgraded the SearchBuilder module, so we’ll see how that fares.

Thanks for your suggestions, I’ll let you know.

-MichaelOn Thursday 16 May 2002 01:06 pm, George Warnagiris wrote:

Michael,

A common solution to slow performance is indexing. Have you applied them?
I realize direct querying is a few orders of magnitude faster, but it will
not hurt. A few other suggestions I’ve seen with positive results are to
turn down the logging level and make sure SearchBuilder is up to date.
Have you tried this?

George

-----Original Message-----
From: Michael Grubb [mailto:mgrubb@fifthvision.net]
Sent: Thursday, May 16, 2002 1:42 PM
To: rt-users@lists.fsck.com
Subject: [rt-users] Slow Keywords

Well, I posted this several days ago and got no hits, so I’ll try one more
time.

I’m having an issue with speed on keyword searches.
I can use a query like the following:

SELECT t.* FROM (tickets t LEFT JOIN objectkeywords k ON t.id=k.objectid)
WHERE
k.keyword=10 AND k.keywordselect=3 AND k.objecttype=‘Ticket’ AND t.queue=7;

(I’m assuming this is the query used by the search (or something similar)
This query runs in 2-3 seconds using psql, but the search using the webui
takes at least a minute if not more, then combine that with the time it
takes
to move to the next page of results, and it gets very frustrating for the
users. They say that a search on subject content takes less time.
I was wondering if anyone else had noticed this or know how to fix it.

I’m running rt 2.0.13 and PostgreSQL 7.2.1, and Perl 5.6.1
on a box using RedHat 7.2 with 1Gb of RAM and 1.6 Ghz athlon.

Any help is greatly appreciated.

-Michael

rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm

This email message may contain information that is confidential and
proprietary to Babcock & Brown or a third party. If you are not the
intended recipient, please contact the sender and destroy the original and
any copies of the original message. Babcock & Brown takes measures to
protect the content of its communications. However, Babcock & Brown cannot
guarantee that email messages will not be intercepted by third parties or
that email messages will be free of errors or viruses.

rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm