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

[Jesse Vincent]

Vivek,
	This is all very cool. I'll look at getting these into 3.2.2

Jesse


On Wed, Sep 08, 2004 at 03:41:10PM -0400, Vivek Khera wrote:
> 
> 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--
> 



> _______________________________________________
> Rt-devel mailing list
> Rt-devel at lists.bestpractical.com
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel


--