Updated Pg indexes (was Re: [Rt-devel] using standalone httpd to debug RT slowness)

[Vivek Khera]

On Sep 7, 2004, at 4:40 PM, Vivek Khera wrote:

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

Well, Jesse says SearchBuilder 1.10 does queries differently, so I 
re-evaluated the changes I made.  Turns out they're still valid.  I did 
notice that the only query I found that used the Parent field of the 
Attachments table didn't use the index specific to it, so I decided 
that I can also drop the Attachments1 index.

Speed improvements with SB 1.10:

Main page speed is still about the same, perhaps .5 seconds faster 
(still under 3 seconds)

The display of a ticket is a bit faster.  A 4.5 second load time went 
down to 3.3 seconds.

The searches are faster.  The searches I tested were clicking on the 
queue names to list all open/new tix in those queues.  One list went 
from about 4.6 seconds down to 1.3 seconds.

In any case, the functional indexes are necessary in postgres to even 
consider using them the way SB builds the queries.

I'm sticking with these indexes.  In case anyone tries out my updated 
indexes, here they are.  I also have the antidote script that restores 
the original indexes, below... :-)


--cut here--
-- only query I see that uses Parent field doesn't use index1 or index3.
-- i could have missed something, though.
DROP INDEX Attachments1;
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)) ;
-- i'm assuming this one needs to be lower() as well; haven't seen it 
used
CREATE INDEX Users2 ON Users (lower(EmailAddress));

-- redundant indexes.  if "id" is specified, we use PK
DROP INDEX Tickets4;
DROP INDEX Tickets5;
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;
--cut here--

The antidote:


--cut here--
CREATE INDEX Attachments1 ON Attachments (Parent) ;
CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;

DROP INDEX Queues1;
CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;

DROP INDEX Groups2;
CREATE INDEX Groups2 On Groups  (Type, Instance, Domain);

DROP INDEX GroupMembers1;

CREATE INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId);
CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (GroupId);

DROP INDEX Users1;
DROP INDEX Users2;
CREATE UNIQUE INDEX Users1 ON Users (Name) ;
CREATE INDEX Users2 ON Users (Name);
CREATE INDEX Users3 ON Users (id, EmailAddress);
CREATE INDEX Users4 ON Users (EmailAddress);


DROP INDEX Tickets4;
CREATE INDEX Tickets4 ON Tickets (id, Status) ;
CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ;

CREATE INDEX TicketCustomFieldValues2 ON TicketCustomFieldValues 
(CustomField,Ticket);

ANALYZE;
--cut here--

-------------- 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/20040908/e84ded30/smime.bin