Kenneth Marshall wrote:
I sent you our list of indexes. You can see how your setup compares and
see if any of the missing ones help your performance. "EXPLAIN ANALYZE..."
can give you detailed information about your query plans.
Good luck with your pruning, but I suspect that that may not have
much of an effect if your indexes are correct.
That's true, it didn't make any significant changes after fixing the
indexes. For the archives, though, I did get rtx-shredder to work with
Ruslan's advice:
rtx-shredder --plugin
'Users=no_tickets,true;limit,20000;status,any;replace_relations,Nobody'
I used the command above to remove all of the users who presumably were
created by spammers, after removing all of the deleted tickets (also
using rtx-shredder).
Kenneth's index list improved performance on Postgresql immensely.
Perhaps the attached patch could be applied to the distribution? Should
I submit it elsewhere for consideration?
Existing postgresql users should be able to:
DROP INDEX Queues1;
CREATE UNIQUE INDEX Queues1 ON Queues (lower((Name)::text)) ;
DROP INDEX Groups2;
CREATE INDEX Groups2 On Groups (lower((Type)::text),
lower((Domain)::text), Instance);
CREATE INDEX GroupMembers1 ON GroupMembers (GroupId);
DROP INDEX Users1;
CREATE UNIQUE INDEX Users1 ON Users (lower(Name)::text) ;
DROP INDEX Users2;
DROP INDEX Users4;
CREATE INDEX Users4 ON Users (lower(EmailAddress)::text);
DROP INDEX ObjectCustomFieldValues2;
Many thanks to everyone who helped. I really appreciate it.
--- schema.Pg 2007-11-14 10:23:29.000000000 -0800
+++ schema.Pg.newindexes 2007-12-04 10:32:30.000000000 -0800
@@ -60,7 +60,7 @@
PRIMARY KEY (id)
);
-CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;
+CREATE UNIQUE INDEX Queues1 ON Queues (lower((Name)::text)) ;
-- }}}
@@ -138,7 +138,7 @@
);
CREATE UNIQUE INDEX Groups1 ON Groups (Domain,Instance,Type,id, Name);
-CREATE INDEX Groups2 On Groups (Type, Instance, Domain);
+CREATE INDEX Groups2 On Groups (lower((Type)::text), lower((Domain)::text), Instance);
-- }}}
@@ -282,6 +282,8 @@
);
+CREATE INDEX GroupMembers1 ON GroupMembers (GroupId);
+
-- }}}
-- {{{ GroupMembersCache
@@ -362,10 +364,9 @@
);
-CREATE UNIQUE INDEX Users1 ON Users (Name) ;
-CREATE INDEX Users2 ON Users (Name);
+CREATE UNIQUE INDEX Users1 ON Users (lower(Name)::text) ;
CREATE INDEX Users3 ON Users (id, EmailAddress);
-CREATE INDEX Users4 ON Users (EmailAddress);
+CREATE INDEX Users4 ON Users (lower(EmailAddress)::text);
-- }}}
@@ -507,7 +508,6 @@
);
CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId,Content);
-CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
-- }}}
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
SAVE THOUSANDS OF DOLLARS ON RT SUPPORT:
If you sign up for a new RT support contract before December 31, we'll take
up to 20 percent off the price. This sale won't last long, so get in touch
today.
Email us at [EMAIL PROTECTED] or call us at +1 617 812 0745.
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
