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 [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 > _______________________________________________ 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