[rt-users] RT related Postgres question

[Kenneth Marshall]

Mark,

Here is a list of indexes that we use here. Check your schema
to see if you are missing any. You should not be getting a
sequential scan.

Ken

-----------------INDEXes from rt schema------------------
CREATE INDEX acl1 ON acl USING btree (rightname, objecttype, objectid, principaltype, principalid);
CREATE INDEX attachments1 ON attachments USING btree (parent);
CREATE INDEX attachments2 ON attachments USING btree (transactionid);
CREATE INDEX attachments3 ON attachments USING btree (parent, transactionid);
CREATE INDEX attributes1 ON attributes USING btree (name);
CREATE INDEX attributes2 ON attributes USING btree (objecttype, objectid);
CREATE INDEX cachedgroupmembers2 ON cachedgroupmembers USING btree (memberid);
CREATE INDEX cachedgroupmembers3 ON cachedgroupmembers USING btree (groupid);
CREATE INDEX customfieldvalues1 ON customfieldvalues USING btree (customfield);
CREATE INDEX disgroumem ON cachedgroupmembers USING btree (groupid, memberid, disabled);
CREATE INDEX groupmembers1 ON groupmembers USING btree (groupid);
CREATE UNIQUE INDEX groups1 ON groups USING btree ("domain", instance, "type", id, name);
CREATE INDEX groups2 ON groups USING btree (lower(("type")::text), lower(("domain")::text), instance);
CREATE UNIQUE INDEX links1 ON links USING btree (base, target, "type");
CREATE INDEX links4 ON links USING btree ("type", localbase);
CREATE INDEX objectcustomfieldvalues1 ON objectcustomfieldvalues USING btree (customfield, objecttype, objectid, content);
CREATE INDEX objectcustomfieldvalues2 ON objectcustomfieldvalues USING btree (customfield, objecttype, objectid);
CREATE INDEX objectcustomfieldvalues3 ON objectcustomfieldvalues USING btree (objectid, objecttype);
CREATE INDEX principals2 ON principals USING btree (objectid);
CREATE UNIQUE INDEX queues1 ON queues USING btree (lower((name)::text));
CREATE INDEX ticketcustomfieldvalues1 ON objectcustomfieldvalues USING btree (customfield, objectid, content);
CREATE INDEX tickets1 ON tickets USING btree (queue, status);
CREATE INDEX tickets2 ON tickets USING btree ("owner");
CREATE INDEX tickets3 ON tickets USING btree (effectiveid);
CREATE INDEX tickets4 ON tickets USING btree (status);
CREATE INDEX transactions1 ON transactions USING btree (objecttype, objectid);
CREATE UNIQUE INDEX users1 ON users USING btree (lower((name)::text));
CREATE INDEX users2 ON users USING btree (lower((emailaddress)::text));


On Fri, Nov 30, 2007 at 03:25:27PM +0000, Mark Chappell wrote:
> 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 sales at bestpractical.com or call us at +1 617 812 
> 0745.
>
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a 
> copy at http://rtbook.bestpractical.com
>