On Fri, Nov 20, 2009 at 10:30:32AM -0600, Kenneth Marshall wrote: > On Fri, Nov 20, 2009 at 04:14:52PM +0000, Dominic Hargreaves wrote:
> > I've attached our postgresql.conf. > > > > The indexes we have defined are the standard ones from the 3.8.6 > > schemas, plus one of the two I already posted: > > > > CREATE INDEX Groups3 ON Groups (LOWER(Domain), LOWER(Type)); > > > > I've just noticed that this one wasn't created on the particular test > > instance I'm talking about, but the query in question doesn't use > > emailaddress, so that's probably not relevant: > > > > CREATE INDEX users5 ON users (LOWER(emailaddress)); > > > > For completeness, the indexes defined on the relevant tables are: > > > > users: > > "users_pkey" PRIMARY KEY, btree (id) > > "users1" UNIQUE, btree (name) > > "users3" btree (id, emailaddress) > > "users4" btree (emailaddress) > > > > acl: > > "acl_pkey" PRIMARY KEY, btree (id) > > "acl1" btree (rightname, objecttype, objectid, principaltype, > > principalid) > > > > principals: > > "principals_pkey" PRIMARY KEY, btree (id) > > "principals2" btree (objectid) > > > > cachedgroupmembers: > > "cachedgroupmembers_pkey" PRIMARY KEY, btree (id) > > "cachedgroupmembers2" btree (memberid) > > "cachedgroupmembers3" btree (groupid) > > "disgroumem" btree (groupid, memberid, disabled) > > > > groups: > > "groups_pkey" PRIMARY KEY, btree (id) > > "groups1" UNIQUE, btree (domain, instance, type, id, name) > > "groups2" btree (type, instance, domain) > > "groups3" btree (lower(domain::text), lower(type::text)) > > > > > Also, what is your statistics target for your tables? > > > > default_statistics_target = 10 > > > > and no per-table changes. I'm not familiar with tuning this; would > > you suggest a different value? > Here are the indexes that we have that differ from your > setup: > > "users1" UNIQUE, btree (lower(name::text)) > > instead of: > > "groups3" btree (lower(domain::text), lower(type::text)) > > we have: > > "groups2" btree (lower(type::text), lower(domain::text), instance) > > You also should definitely raise the statistics target to > at least 100, which is the new default in 8.4. We also have > the random_page_cost set to 2.0 since we are mainly memory > resident. I know that the index order needs to match the > query to be used, so maybe these index changes would help. Thanks. Bizzarely, I can't reproduce the problematic query now; I wonder if it skewed severely by the hammering of another database on the same server (a test run of rt2tort3, as it happens). I think your suggested new index in group is correct; that's eliminated some more slow queries. I think I'll try and put up a set of annotated additional postgres indexes on the wiki, in lieu of future updates to the indexes created by the schemas shipped with RT. -- Dominic Hargreaves, Systems Development and Support Team Computing Services, University of Oxford
signature.asc
Description: Digital signature
_______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users 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
