[rt-devel] PgSQL 7.3.2 queries in rt 3.0.2pre4, using DBIx-SearchBuilder-0.81_04

[Phil Regnauld]

Hi people,

I'm seeing some unusually high query times using the setup mentioned above.
The system is a FreeBSD 4.8 PIII-550 with 256 MB RAM and running PgSQL 7.3.2,
and Apache 1.3.27 with mod_fastcgi

After importing our production rt2 data into the test rt3 (767 tickets),
such simple queries as viewing open tickets or replying to an open ticket
will send Apache into Internal Server Error as the CGI times out after
30 seconds.  And yes, the DB has been vacuumed after import.

Example of a query generated by RT:

2003-05-02 14:14:44 LOG: query: SELECT DISTINCT main.* FROM
Users main JOIN Principals as Principals_3 ON main.id =
Principals_3.id JOIN CachedGroupMembers as CachedGroupMembers_6
ON Principals_3.Id = CachedGroupMembers_6.MemberId JOIN
Principals as Principals_1 ON main.id = Principals_1.id JOIN
CachedGroupMembers as CachedGroupMembers_7 ON Principals_1.id =
CachedGroupMembers_7.MemberId JOIN Principals as Principals_4 ON
CachedGroupMembers_6.GroupId = Principals_4.Id, Groups Groups_2,
ACL ACL_5 WHERE ((ACL_5.RightName = 'SuperUser')OR(ACL_5.RightName
= 'OwnTicket')) AND ((CachedGroupMembers_7.GroupId = '4')) AND
((Principals_1.Disabled = '0')) AND ((Principals_1.PrincipalType
= 'User')) AND ((Principals_3.PrincipalType = 'User')) AND
(ACL_5.ObjectType = 'RT::System' OR (ACL_5.ObjectType = 'RT::Queue' AND
ACL_5.ObjectId = '5') ) AND ( (ACL_5.PrincipalId = Principals_4.Id AND
Principals_4.id = Groups_2.Id AND ACL_5.PrincipalType = 'Group' AND
(Groups_2.Domain = 'SystemInternal' OR Groups_2.Domain = 'UserDefined'
OR Groups_2.Domain = 'ACLEquivalence')) OR ( ( (Groups_2.Domain =
'RT::Queue-Role' AND Groups_2.Instance = '5') OR ( Groups_2.Domain =
'RT::Ticket-Role' AND Groups_2.Instance = '771') ) AND Groups_2.Type
= ACL_5.PrincipalType AND Groups_2.Id = Principals_4.id AND
Principals_4.PrincipalType = 'Group') ) ORDER BY main.Name ASC

2003-05-02 14:15:12 LOG:  duration: 27.121106 sec


This is to say the least a bit on the complex side.  I know the idea
is to have as much database abstract queries as possible (since MySQL 3
for example doesn't implement subselects, among other things, while
PgSQL, Oracle, etc... do), but this is obviously _very_ MySQL-oriented.
This would be much more efficient (27 seconds!) using subselects...

Any ideas on how this can be improved ?  I'll have to stick to RT 2
then, or convince myself to use MySQL 4 (we use Postgres for everything
else...).

-- 
  _ _ |_ | regnauld at catpipe.net                   catpipe Systems ApS   |
 (_(_||_ |          *BSD solutions, consulting, development             |
         | Tlf.: +45 7021 0050                  http://www.catpipe.net/ |