AW: Re: [rt-users] clean up user table

[Gordon Messmer]

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.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: schema.Pg.diff
Type: text/x-patch
Size: 1245 bytes
Desc: not available
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20071204/c2ad0ab3/attachment.bin>