[rt-users] RT related Postgres question

[Mark Chappell]

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