[rt-users] Custom field queries are very slow after 3.8.8 upgrade (from 3.6.10)

[Georgi Georgiev]

I am looking for some advice on how to speed up some queries using
custom fields that got unusably slow after an upgrade from the RT 3.6 series
to 3.8.

We are currently running RT 3.6.10 on CentOS 5.5 with a postgresql
back end (used to be PgSQL 8.1 but we upgraded to 8.4 when CentOS 5.5
came out). The RT package is from EPEL and everything is fine.

When trying to upgrade to RT 3.8.8 some of our saved queries using
custom fields get very, *very* slow. I tried this twelve months ago with
RT 3.8.7 on CentOS 5.x and PgSQL 8.1 (whatever was "current" then), and
also again today, on RHEL6 with PgSQL 8.4.

The problematic queries look something like this:

Queue = 'somequeue' AND Status = 'stalled' AND (
'CF.{MyCF}' = 'value1' OR
'CF.{MyCF}' = 'value2' OR
'CF.{MyCF}' = 'value3' OR
'CF.{MyCF}' = 'value4' OR
'CF.{MyCF}' = 'value5' OR
'CF.{MyCF}' = 'value6' OR
'CF.{MyCF}' = 'value7' )

where the listed values are 3-4 characters long and their number varies.

After the ugprade, and with logging of slow queries enabled, I saw these
numbers for queries with 5, 6, 7, and 8 OR statements for the custom
field in the postgresql logs:
- query for 5 possible CF values - 0.6 seconds
- query for 6 possible CF values - 6 seconds
- query for 7 possible CF values - 65 seconds
- query for 8 possible CF values - 681 seconds

In comparison, the corresponding query in RT 3.6 executes in less than 2
milliseconds.

One thing I noticed is that the SQL that is generated by RT 3.6 and 3.8
is substantially different.

In 3.6 there is a single SELECT on the ObjectCustomFieldValues table, so
the generated SQL looks like this:

SELECT DISTINCT main.*
FROM Tickets main
JOIN ObjectCustomFieldValues ObjectCustomFieldValues_1
ON  ( ObjectCustomFieldValues_1.CustomField = '1' )
AND ( ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket' )
AND ( ObjectCustomFieldValues_1.Disabled = '0' )
AND ( ObjectCustomFieldValues_1.ObjectId = main.id )
WHERE (main.Status != 'deleted')
AND (main.Queue = '11' AND main.Status = 'stalled' AND
(
     ( ( ObjectCustomFieldValues_1.Content = 'value1' )  )
 OR  ( ( ObjectCustomFieldValues_1.Content = 'value2' )  )
...
)
)
AND (main.Type = 'ticket')
AND (main.EffectiveId = main.id)
ORDER BY main.id ASC

In RT 3.8.8 the generated SQL looks like this:

SELECT DISTINCT main.*
FROM Tickets main
LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_1
ON  ( ObjectCustomFieldValues_1.CustomField = '1' )
AND ( ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket' )
AND ( ObjectCustomFieldValues_1.Disabled =  '0' )
AND ( ObjectCustomFieldValues_1.ObjectId = main.id )
LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_2
ON  ( ObjectCustomFieldValues_2.CustomField = '1' )
AND ( ObjectCustomFieldValues_2.ObjectType = 'RT::Ticket' )
AND ( ObjectCustomFieldValues_2.Disabled =  '0' )
AND ( ObjectCustomFieldValues_2.ObjectId = main.id )
....
WHERE (main.Status != 'deleted')
AND (
main.Queue = '11' AND main.Status = 'stalled' AND
 (
    (  (  ( ObjectCustomFieldValues_1.Content = 'value1' )  )  )
OR  (  (  ( ObjectCustomFieldValues_2.Content = 'value2' )  )  )
....
 )
)
AND (main.Type = 'ticket')
AND (main.EffectiveId = main.id)
ORDER BY main.id ASC

I ran the queries through "explain analyze" and tried disabling nested
loops, then disabling hash joins (I tried disabling whatever the planner
was trying to use hoping it will try something else that is faster) but
the query speed did not change considerably.

Our database is not that big and can easily fit in the memory of the
machine (400MB filesize), and we have less than 6000 tickets total. The
queries I tried out return only a total of 4 to 5 tickets in the end. The
custom field that I am referring to above is a mandatory field so it is
assigned for almost all tickets, and with multiple values most of the
time.

Any advice?

-- 
\    Georgi Georgiev   \  Ever notice that even the busiest people   \
/     Sysadmin Head    /  are never too busy to tell you just how    /
\    -SBI Japannext-   \  busy they are?                             \