We are in the process of upgrading our RT installation from 3.4.2 (to 3.6.4).
As part of the upgrade, we were planning to restructure RT to allow additional
groups within our lab use RT (previously we just used it within our own group).
Part of that plan involved moving from having multiple queues for our group to
using just a single queue and using a custom field to store what was previously
the queue name. I performed that change with no problem -- however, performance
on SOME queries that include custom fields is so slow that it is unusable.
Here is the system background:
RT 3.6.4
RHEL 5 (with all updates through last week)
Postgres 8.1.9-1.el5
Apache 2.2.3.7.el5
mod_perl 2.0.2-6.3.el5
SearchBuilder 1.49
The system has only about 7000 tickets and about 100 or so users (only about 10
have rights to RT).
The following query takes something like 165 seconds and returns three records:
SELECT DISTINCT main.* FROM Tickets main JOIN ObjectCustomFields
ObjectCustomFields_1
ON ( ObjectCustomFields_1.ObjectId = '0' ) OR ( ObjectCustomFields_1.ObjectId =
main.Queue )
JOIN CustomFields CustomFields_2 ON ( CustomFields_2.id =
ObjectCustomFields_1.CustomField )
JOIN ObjectCustomFieldValues ObjectCustomFieldValues_3 ON (
ObjectCustomFieldValues_3.ObjectId = main.id )
AND ( ObjectCustomFieldValues_3.Disabled = '0' )
AND ( ObjectCustomFieldValues_3.ObjectType = 'RT::Ticket' )
AND ( ObjectCustomFieldValues_3.CustomField = CustomFields_2.id )
WHERE (CustomFields_2.Name = 'Category') AND (main.Status != 'deleted') AND
(main.Owner = '368'
AND ( main.Status = 'new' OR main.Status = 'open' OR main.Status = 'stalled' )
AND main.Queue = '5' AND ( ObjectCustomFieldValues_3.Content = 'General' ) )
AND (main.Type = 'ticket') AND (main.EffectiveId = main.id)
ORDER BY main.id ASC
Here is the Postgres explain plan output:
QUERY PLAN
----------
Unique (cost=17.16..17.23 rows=1 width=620) (actual
time=165267.134..165267.161 rows=3 loops=1)
-> Sort (cost=17.16..17.17 rows=1 width=620) (actual
time=165267.127..165267.133 rows=3 loops=1)
Sort Key: main.id, main.effectiveid, main.queue, main."type",
main.issuestatement, main.resolution, main."owner", main.subject,
main.initialpriority, main.finalpriority, main.priority, main.timeestimated,
main.timeworked, main.status, main.timeleft, main.told, main.starts,
main.started, main.due, main.resolved, main.lastupdatedby, main.lastupdated,
main.creator, main.created, main.disabled
-> Nested Loop (cost=5.02..17.15 rows=1 width=620) (actual
time=32924.858..165266.929 rows=3 loops=1)
Join Filter: ((("outer".objectid = 0) OR ("outer".objectid =
"inner".queue)) AND ("outer".objectid = "inner".id))
-> Nested Loop (cost=0.00..8.09 rows=1 width=8) (actual
time=0.124..555.995 rows=53712 loops=1)
-> Nested Loop (cost=0.00..2.07 rows=1 width=12) (actual
time=0.032..0.180 rows=9 loops=1)
Join Filter: ("outer".id = "inner".customfield)
-> Seq Scan on customfields customfields_2
(cost=0.00..1.02 rows=1 width=4) (actual time=0.018..0.036 rows=1 loops=1)
Filter: ((name)::text = 'Category'::text)
-> Seq Scan on objectcustomfields
objectcustomfields_1 (cost=0.00..1.02 rows=2 width=8) (actual
time=0.005..0.063 rows=17 loops=1)
-> Index Scan using objectcustomfieldvalues2 on
objectcustomfieldvalues objectcustomfieldvalues_3 (cost=0.00..6.00 rows=1
width=8) (actual time=0.044..35.073 rows=5968 loops=9)
Index Cond: ((objectcustomfieldvalues_3.customfield
= "outer".id) AND ((objectcustomfieldvalues_3.objecttype)::text =
'RT::Ticket'::text))
Filter: ((disabled = 0) AND ((content)::text =
'General'::text))
-> Bitmap Heap Scan on tickets main (cost=5.02..9.05 rows=1
width=620) (actual time=3.002..3.052 rows=3 loops=53712)
Recheck Cond: (("owner" = 368) AND (queue = 5))
Filter: (((status)::text <> 'deleted'::text) AND
(((status)::text = 'new'::text) OR ((status)::text = 'open'::text) OR
((status)::text = 'stalled'::text)) AND (("type")::text = 'ticket'::text) AND
(effectiveid = id))
-> BitmapAnd (cost=5.02..5.02 rows=1 width=0) (actual
time=2.513..2.513 rows=0 loops=53712)
-> Bitmap Index Scan on tickets2 (cost=0.00..2.39
rows=110 width=0) (actual time=0.148..0.148 rows=910 loops=53712)
Index Cond: ("owner" = 368)
-> Bitmap Index Scan on tickets1 (cost=0.00..2.39
rows=110 width=0) (actual time=2.325..2.325 rows=19050 loops=53712)
Index Cond: (queue = 5)
Total runtime: 165267.372 ms
(23 rows)
----------
At this point, I think this could be a Postgres bug. If I change the part of
the query that
specifies the custom field (the "ObjectCustomFieldValues_3.Content =
'General'") to instead
EXCLUDE all non-matching fields, the query executes almost immediately -- e.g.
using something
like:
(ObjectCustomFieldValues_3.Content != 'FY07' AND
ObjectCustomFieldValues_3.Content != 'Upgrades' AND
[etc.])
Should I simply try upgrading Postgres, or does anyone have any ideas about what
the REAL problem might be?
Thanks,
Matt Goheen
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
SAVE THOUSANDS OF DOLLARS ON RT SUPPORT:
If you sign up for a new RT support contract before December 31, we'll take
up to 20 percent off the price. This sale won't last long, so get in touch
today.
Email us at [EMAIL PROTECTED] or call us at +1 617 812 0745.
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
