Slow dashboard query caused by bad ticket search query

RT Users
1

Hi RT users,

I was tracking down a slow query in the postgres query log and traced it to the following search: Status != ‘resolved’ AND Queue = ‘AESS-Security’ AND Subject NOT LIKE ‘Notification of OnBoarding’. The full query ran quickly but when limited to the default 10 items for the dashboard, it took almost 2s. Here is the query that actually hit the DB:

LOG: duration: 1753.314 ms statement: SELECT main.* FROM Tickets main WHERE (main.IsMerged IS NULL) AND (main.Status != ‘deleted’) AND (main.Type = ‘ticket’) AND (LOWER(main.Status) != ‘resolved’ AND main.Queue = ‘269’ AND main.Subject NOT ILIKE ‘%Notification of OnBoarding%’) ORDER BY main.id DESC LIMIT 10

After much testing, I finally noticed that the status != resolved check was wrapped in a LOWER(). This meant that the index could not be used for that check since all of the indexes just use the base status and not LOWER(status). I created a new index for tickets on LOWER(status) and then the query completed in 3ms. Does anyone know where that LOWER() is coming from in the code? I would prefer to fix that than add the additional index.

Regards,
Ken

2

It funny because I just found where it is originating from a post I made to the forum in 2009. The cause is the _MakeClauseCaseInsensitive in DBIx::SearchBuilder/Handle/Pg.pm which has the following code:

elsif ( $operator =~ /=/ ) {
if (howmany() >= 4) {
return ( “LOWER($field)”, $operator, $value, “LOWER(?)”);
}
# RT 3.0.x and earlier don’t know how to cope with a “LOWER” function
# on the value. they only expect field, operator, value.
#
else {
return ( “LOWER($field)”, $operator, lc($value));

    }
}

Now that I found the source…again, I wonder what other queries are failing to use an index because of this function.

Regards,
Ken