[rt-users] Re: [rt-devel] DBIx-SearchBuilder 0.81 released

[Christian Zagrodnick]

so now.. 

-- issued on viewing a ticket, unformatted
SELECT DISTINCT main.* FROM Tickets main  JOIN Groups as Groups_1  ON  main.id = Groups_1.Instance JOIN Principals
 as Principals_2  ON  Groups_1.id = Principals_2.ObjectId JOIN CachedGroupMembers as CachedGroupMembers_3  ON  Principals_2.id =
 CachedGroupMembers_3.GroupId JOIN Principals as Principals_4  ON  CachedGroupMembers_3.MemberId = Principals_4.id JOIN Users as
 Users_5  ON  Principals_4.ObjectId = Users_5.id  WHERE ((main.EffectiveId = main.id)) AND ((main.Type = 'ticket')) AND ( (  (
 (lower(Users_5.EmailAddress) = 'sales at tastaturen.com')AND(Groups_1.Domain = 'RT::Ticket-Role')AND(Groups_1.Type =
 'Requestor')AND(Principals_2.PrincipalType = 'Group') )  ) AND ( (main.Status = 'open')OR(main.Status = 'new') ) )  ORDER BY
 main.Priority DESC LIMIT 10
-- duration: 20.513569 sec

-- basicly the same just readable
SELECT DISTINCT 
    main.* 
FROM 
    Tickets main  
    JOIN Groups as Groups_1  ON  main.id = Groups_1.Instance 
    JOIN Principals as Principals_2  ON  Groups_1.id = Principals_2.ObjectId 
    JOIN CachedGroupMembers as CachedGroupMembers_3  ON  
        Principals_2.id = CachedGroupMembers_3.GroupId 
    JOIN Principals as Principals_4  ON  
        CachedGroupMembers_3.MemberId = Principals_4.id 
    JOIN Users as Users_5  ON  Principals_4.ObjectId = Users_5.id  
WHERE
    main.EffectiveId = main.id 
    AND main.Type = 'ticket'
    AND lower(Users_5.EmailAddress) = 'sales at tastaturen.com'
    AND Groups_1.Domain = 'RT::Ticket-Role'
    AND Groups_1.Type = 'Requestor'
    AND Principals_2.PrincipalType = 'Group'
    AND (main.Status = 'open' OR main.Status = 'new')
ORDER BY 
    main.Priority DESC 
LIMIT 10
-- 20 secs still


-- explain analyze showed the problem: one small change in joining et voila.
SELECT DISTINCT 
    main.* 
FROM 
    Tickets main  
    JOIN (
        Groups as Groups_1 
        JOIN Principals as Principals_2  ON  Groups_1.id = Principals_2.ObjectId 
        JOIN CachedGroupMembers as CachedGroupMembers_3  ON  
            Principals_2.id = CachedGroupMembers_3.GroupId 
        JOIN Principals as Principals_4  ON  
            CachedGroupMembers_3.MemberId = Principals_4.id 
        JOIN Users as Users_5  ON  Principals_4.ObjectId = Users_5.id
        ) ON  main.id = Groups_1.Instance 
WHERE
    main.EffectiveId = main.id 
    AND main.Type = 'ticket'
    AND lower(Users_5.EmailAddress) = 'sales at tastaturen.com'
    AND Groups_1.Domain = 'RT::Ticket-Role'
    AND Groups_1.Type = 'Requestor'
    AND Principals_2.PrincipalType = 'Group'
    AND (main.Status = 'open' OR main.Status = 'new')
ORDER BY 
    main.Priority DESC 
LIMIT 10
-- duration: 0.526543 sec

I suspect this is aplicaple in several situations where the ticket is
joined.


On Sun, May 04, 2003 at 08:48:50AM -0400, Jesse Vincent wrote:
> 
> 
> > > A number of folks wrote me to say that they saw dramatic speedups with
> > > the new searchbuilder. I would, of course, appreciate additional help
> > > tuning for postgres. I know there's been a suggested column type change
> > > in the ACL system for faster querying which I haven't had a chance to
> > > lookup, but more work by the folks who know postgres well would be
> > > appreciated.
> > 
> > Well yes, it dropped from `i-don't-know' to 8 minutes with this giant
> > query.  Creating two additional indexes dropped it further to about 1
> > minute -- which is still too slow.  Certainly I could rewrite some query
> > manually to get some speedups if it helps (i.e. if it is integratable
> > into RT or SearchBuilder).
> 
> Actually, I'd appreciate it if you could have a look at:
> 
> http://lists.fsck.com/pipermail/rt-devel/2003-April/003697.html
> 
> 
> I think we're currently around aidan's partially-optimized query. 
> Would you mind having a look and seeing if you can improve on his work?
> I'm happy to put in more effort to speed postgres up, if I can get more
> input about what will help.
> 
> 	-j
> 
> 
> > -- 
> > Christian Zagrodnick
> > 
> > gocept gmbh & co. kg - schalaunische strasse 6 - 06366 koethen/anhalt
> > fon. +49 3496 3099112, +49 179 1463644
> > fax. +49 3496 3099118
> > 
> > 
> 
> 
> 
> -- 
> http://www.bestpractical.com/rt  -- Trouble Ticketing. Free.
> 

-- 
Christian Zagrodnick

gocept gmbh & co. kg - schalaunische strasse 6 - 06366 koethen/anhalt
fon. +49 3496 3099112, +49 179 1463644
fax. +49 3496 3099118