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.