I have been looking at the performance of our RT-3.4.2 which uses
Oracle9i. I got some complaints that searching took so long and had a
look myself and found a problem which at least at our site kills
performance for searching on content.
This is a search for problems on a machine called TZP065.
This construct I made myself and is how Oracle most efficiently searches
CLOB columns and has always worked like a charm.
AND (( (contains (attachments_2.content, ‘tzp065’) > 0)
/* Formatted on 2005/06/24 09:11 (Formatter Plus v4.8.5) */
SELECT COUNT (DISTINCT main.ID)
FROM tickets main, transactions transactions_1, attachments attachments_2
WHERE ((transactions_1.objecttype = ‘RT::Ticket’))
AND ((main.effectiveid = main.ID))
AND ((main.status != ‘deleted’))
AND ((main.TYPE = ‘ticket’))
AND (( (contains (attachments_2.content, ‘tzp065’) > 0)
AND (attachments_2.transactionid = transactions_1.ID)
AND (main.ID = transactions_1.objectid)
)
)
The problem is in this line:
AND ((main.TYPE = ‘ticket’))
I made several extra indices among them one on Ticket.Type. This turns
out to be not neccesary since there is only one Type, ‘ticket’ and thus
causes a full table scan against all Tickets ;-((
Eliminating that line gives me reasonable query times, around 5sec.
Now the question:
Are there gonna be other types in the ticket table?
If NO then this line can be removed, if YES can it be disabled until the
time that there are other types in the ticket table?
Now the question:
Are there gonna be other types in the ticket table?
If NO then this line can be removed, if YES can it be disabled until the
time that there are other types in the ticket table?
If you’re using the Approvals feature, there can also be entries with
type approval in the table. This is already possible right now, so the
test can not be removed.
Can you optimize the query in a way, that the test is only done later,
like on the result of the query without that condition? However, even if
you find a solution, I’m not sure, RT’s query can be changed to match
that. IMHO it’s a serious flaw in Oracle’s query optimizer that adding a
condition that’s always true significantly slows down a query.
The problem is in this line:
AND ((main.TYPE = ‘ticket’))
I made several extra indices among them one on Ticket.Type. This turns
out to be not neccesary since there is only one Type, ‘ticket’ and thus
causes a full table scan against all Tickets ;-((
Eliminating that line gives me reasonable query times, around 5sec.
Now the question:
Are there gonna be other types in the ticket table?
If NO then this line can be removed, if YES can it be disabled until the
time that there are other types in the ticket table?
Well, various things, such as the approvals system do add other ticket
types now. (Also, RT 3.5’s reminders system and the RT-Todo extension)
that. IMHO it’s a serious flaw in Oracle’s query optimizer that adding a
condition that’s always true significantly slows down a query.
Well, it still has to visit every row. There’s no way to know in
advance that the condition is always true, because the optimiser is
cost-based (and therefore can only use statistics for this). It’s
possible that the planning step would take a different strategy
(using an index, say); but it’d still have to visit every row, and
using an index in that case would be even more expensive.
The simple problem is that the condition isn’t a selective one, and this
suggests to me that the query is a bad one, given the structure. (My
experience overall with RT is that the automatic generation of SQL
causes all sorts of poorly-written queries like this, that generate
needless conditions that slow everything down).
A
Andrew Sullivan | ajs@crankycanuck.ca
The fact that technology doesn’t work is no bar to success in the marketplace.
–Philip Greenspun
that. IMHO it’s a serious flaw in Oracle’s query optimizer that adding a
condition that’s always true significantly slows down a query.
Well, it still has to visit every row. There’s no way to know in
advance that the condition is always true, because the optimiser is
cost-based (and therefore can only use statistics for this). It’s
possible that the planning step would take a different strategy
(using an index, say); but it’d still have to visit every row, and
using an index in that case would be even more expensive.
The point is, it doesn’t need to use the condition on all records, only
those that match the other conditions. Also, for most searches (matching
text in some field like in the given query) the database needs to look
at every row anyway.
The simple problem is that the condition isn’t a selective one, and this
suggests to me that the query is a bad one, given the structure. (My
experience overall with RT is that the automatic generation of SQL
causes all sorts of poorly-written queries like this, that generate
needless conditions that slow everything down).
Like Jesse and I said before, the condition is not needless.
that. IMHO it’s a serious flaw in Oracle’s query optimizer that adding a
condition that’s always true significantly slows down a query.
Well, it still has to visit every row. There’s no way to know in
advance that the condition is always true, because the optimiser is
cost-based (and therefore can only use statistics for this). It’s
possible that the planning step would take a different strategy
(using an index, say); but it’d still have to visit every row, and
using an index in that case would be even more expensive.
The point is, it doesn’t need to use the condition on all records, only
those that match the other conditions. Also, for most searches (matching
text in some field like in the given query) the database needs to look
at every row anyway.
The simple problem is that the condition isn’t a selective one, and this
suggests to me that the query is a bad one, given the structure. (My
experience overall with RT is that the automatic generation of SQL
causes all sorts of poorly-written queries like this, that generate
needless conditions that slow everything down).
Like Jesse and I said before, the condition is not needless.
Rolf
I started this thread before I had a talk with my DBA and since then I
got to understand better what is happening.
The query I showed was looking for a piece of text from the body of an
email message which is stored in attachments.content. This column is
normally not indexed but I have added what is called a Context (domain) index
which Oracle uses to index CLOB/BLOB columns which can contain plain
text but also Word documents and almost anything Oracle has a filter for.
I also rewrite the query in Searchbuilder so that this index is used
whenever there is a search for CONTENT and ‘contains’ is used in RT
This query is only slow because it has no other restrictions besides the
ones added automatically like status!=‘deleted’ and effectiveid=id.
If I understand correctly the last one is used to distinguish merged
tickets from normal ones? and this conditions is even worse in our
environment because we have about 20 merged tickets out of 15000 tickets
total.
Adding a queue to the search will bring down this endless search to
about 5 secs for the first execution and less then 1sec for subsequent
searches. We do understand why the query is build as it is and it is
impossible to make use of only the strong points of a database without
going to write queries specific for that database.
For those of you using Oracle:
We thought about using ‘buckets’ on some indices but because of our
search pattern this is probably not going to work. YMMV. ( what this
means is you could one of the six statuses assign a different weight in
which case the index might get used more often instead of a full table
scan)
The point is, it doesn’t need to use the condition on all records, only
those that match the other conditions. Also, for most searches (matching
Sure doesn’t look that way to me, a priori. You happen to know
that once you have the join condition satisfied, the two other
conditions are more or less always true. But there’s no way for a
database to know that in advance. If I were doing this in Postgres,
I’d try to convince the planner to prefer satisfying the join
condition first, and use partial indexes to show it that many.TYPE =
‘ticket’ is almost always true (we have used a number of strategies
of this sort to make things go from “uselessly slow” to merely
“sluggish” on a largeish database). I’m sure that there are similar
knobs to be turned in Oracle, but I’m not familiar enough with Oracle
administration to say what they are.
text in some field like in the given query) the database needs to look
at every row anyway.
To the extent that’s true, I have a feeling that the database design
needs some fixing up. On any database of any appreciable size, table
scans are going to suck.
suggests to me that the query is a bad one, given the structure. (My
experience overall with RT is that the automatic generation of SQL
causes all sorts of poorly-written queries like this, that generate
needless conditions that slow everything down).
Like Jesse and I said before, the condition is not needless.
It’s not needless in the generic case, but it happens to be
needless here. From the point of view of supporting largish systems,
using the generic case in every event makes the system hard on the
users. What they’ll do in this case, for instance, since it’s taking
too long, is hit “cancel” in their browser and re-submit the query.
Now you have two expensive queries running. On a system with 20
users, this quickly becomes a serious problem.
A
Andrew Sullivan | ajs@crankycanuck.ca
I remember when computers were frustrating because they did exactly what
you told them to. That actually seems sort of quaint now.
–J.D. Baldwin