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

[Kenneth Marshall]

On Wed, Jan 05, 2011 at 07:31:37PM +0900, Georgi Georgiev wrote:
> 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?
> 

Hi Georgi,

What are your postgresql.conf parameters? What are the EXPLAIN ANALYZE
results for the fast query (3.6.x) and the slow (3.8.8) query? That
should also point out where an index may help.

Cheers,
Ken