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? \