[Rt-devel] using standalone httpd to debug RT slowness

[Vivek Khera]

On Sep 7, 2004, at 2:07 PM, Jesse Vincent wrote:
>
> On Tue, Sep 07, 2004 at 01:52:21PM -0400, Vivek Khera wrote:
>>> FWIW, give searchbuilder 1.10 a shot
>>>
>>
>> I plan to once I get some baseline data with 1.01.
>>
>> Already it seems that the ... where lower(foo) = 'bar' stuff is 
>> killing
>> Pg because indexes are not being used.
>
> Nod. I'd forgotten whether we'd ended up with functional indexes for
> that stuff.


Ok.  Spent the whole afternoon logging queries and analyzing them, 
squeezing out the most performance I could by just adjusting the 
indexes.

Some indexes need to be removed because they are redundant, or just 
plain never used due to the queries being functional.  The latter were 
replaced with functional indexes.   Dropping redundant indexes speeds 
up inserts and updates.

Anyone running Postgres should consider trying these out.  It has made 
loading my "RT at a glance" page amazingly faster, and tickets seem to 
load faster as well.

MySQL users may want to see if the two new indexes, GroupMembers1 and 
my version of Tickets4 speed things up.

The rest of the message is suitable for cut/paste into psql when 
connected as the PG superuser to the rt3 database:


-- seems redundant this one... don't see use of queries on Parent 
either,
-- so that one may be droppable.
DROP INDEX Attachments3;

-- only query as lower().  seems confusing to allow queues with same
-- name but differing only in case.
DROP INDEX Queues1;
CREATE UNIQUE INDEX Queues1 ON Queues (lower(Name)) ;


-- unable to analyze usage of Links table indexes.

-- when Instance, Type, and Domain are all used it is usually with just 
plain
-- search so the first index will be used.  this index will cover the 
search
-- for where type is specified with lower().  Order of fields makes 
sense
-- from queries I see: sometimes only first one or two are queried.
DROP INDEX Groups2;
CREATE INDEX Groups2 On Groups (lower(Type),lower(Domain),Instance);


-- Transactions table indexes not analyzed.

-- ACL table index not analyzed.  too small to use indexes in my 
instance

-- group members needs an index!  This one has massive speedups.
CREATE INDEX GroupMembers1 ON GroupMembers (GroupID);


-- I think these are redundant since I only ever see DisGrouMem index 
used
DROP INDEX CachedgroupMembers2;
DROP INDEX CachedgroupMembers3;



-- the old Users2 is 100% redundant with Users1, and neither gets used
-- since we always query with lower().  Users3 is redundant since "id"
-- is already a PK so if that is queried, we have a unique return.
DROP INDEX Users1;
DROP INDEX Users2;
DROP INDEX Users3;
DROP INDEX Users4;

CREATE UNIQUE INDEX Users1 ON Users (lower(Name)) ;
CREATE INDEX Users2 ON Users (lower(EmailAddress));

-- redundant indexes.  if "id" is specified, we use PK
DROP INDEX Tickets4;
DROP INDEX Tickets5;
-- there are queries that make good use of this one...
CREATE INDEX Tickets4 ON Tickets (Status);

-- redundant index -- just a prefix of index 1.
DROP INDEX TicketCustomFieldValues2;

-- don't see Attributes used.  looks like an empty table for me.


-- and update the statistics...
ANALYZE;
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 2476 bytes
Desc: not available
Url : http://lists.bestpractical.com/pipermail/rt-devel/attachments/20040907/f64bff55/smime.bin