This is possibly slightly OT, if so I apologize. I'm most of the way
through getting RT to behave under postgres, however I seem to be
hitting a problem with the query optimizer.
When RT tries to pull out the ticket history for a user it runs the
following query
SELECT DISTINCT main.* FROM Tickets main JOIN Groups Groups_1 ON (
Groups_1.Instance = main.id ) JOIN CachedGroupMembers
CachedGroupMembers_2 ON ( CachedGroupMembers_2.GroupId !=
CachedGroupMembers_2.MemberId ) AND ( CachedGroupMembers_2.GroupId =
Groups_1.id ) JOIN Users Users_3 ON ( Users_3.id =
CachedGroupMembers_2.MemberId ) WHERE (Groups_1.Domain =
'RT::Ticket-Role') AND (Groups_1.Type = 'Requestor') AND ( ( Users_3.id
= '554' ) AND ( main.Status = 'open' OR main.Status = 'new' OR
main.Status = 'stalled' OR main.Status = 'resolved' ) ) AND (main.Type =
'ticket') AND (main.EffectiveId = main.id) ORDER BY main.Status ASC
limit 10;
Now with our DB this is currently taking about 10 seconds.
For some reason Postgres is getting it's row predictions way out.
-> Seq Scan on tickets main (cost=0.00..17539.97 rows=1485 width=168)
(actual time=0.040..846.740 rows=298633 loops=1)
Filter: ((((status)::text = 'open'::text) OR ((status)::text =
'new'::text) OR ((status)::text = 'stalled'::text) OR ((status)::text =
'resolved'::text)) AND (("type")::text = 'ticket'::text) AND
(effectiveid = id))
If however I remove the "main.EffectiveId=main.id" chunk of the query it
uses a far more efficient query. Only a very small proportion of our
tickets are merged
Anybody here seen this and/or know where to start looking for how to
correct this? I have run "VACUUM FULL ANALYZE" and tried playing with
the statistics on Tickets but to no avail.
Mark
--
Mark Chappell
Unix Systems Administrator
_______________________________________________
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
