[rt-users] Slow queries building list of privileged users (Postgres)

[Dominic Hargreaves]

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:

> > 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 for the pointers. I'll give them a spin on Monday and report
back.

-- 
Dominic Hargreaves, Systems Development and Support Team
Computing Services, University of Oxford
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 197 bytes
Desc: Digital signature
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20091120/a507ac43/attachment.sig>