[Rt-devel] 3.4.0rc1 slow

[Jesse Vincent]

>    I've looked at the postgres logs, and it seems to do many many iterations
>    of this type of query:
> 
>    LOG:  duration: 1983.993 ms  statement:                                                                                         
>               SELECT DISTINCT main.* FROM (Groups main LEFT JOIN Principals Principals_2 ON ( main.id = Principals_2.id)),
>    ACL ACL_1 WHERE ((ACL_1.RightName = 'OwnTicket')) AND ((Principals_2.Disabled = '0')) AND ( ( ACL_1.PrincipalId = main.id AND
>    ACL_1.PrincipalType = 'Group' AND ( main.Domain = 'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain =
>    'ACLEquivalence')) OR ( ( (main.Domain = 'RT::Queue-Role' AND main.Instance = 102) ) AND main.Type = ACL_1.PrincipalType) ) AND
>    (ACL_1.ObjectType = 'RT::System' OR (ACL_1.ObjectType = 'RT::Queue' AND ACL_1.ObjectId = 102) ) ORDER BY main.Name ASC
> 
>    At the time, there were about 50k+ users and 20k+ groups in the
>    database.

I'd love it if you could dig a bit more into what's killing that query.
Pull off the distinct and see if we're doing something stupid and
generating a huge cartesian porduct?
> 
> 
> 2) The method used in the Postgres version of SearchBuilder/Handle/Pg.pm
>    for insert() is suboptimal.  I've turned on postgres logging to dump
>    any queries taking > .5 seconds and the query in this method comes up
>    continually:

Interesting. At the time we did that (years ago now), that was the
recommended way to do unique ids on postgres. I'd love to see a patch
for improved behaviour.



>    ie:
> 
>    postgres[18131]: [6610-1] LOG:  duration: 881.154 ms  statement: SELECT id FROM Attachments WHERE oid = '3609306'
> 
>    From doing a bit of reading in the postgres docs, the oid should not
>    be used for this type of thing - they can/will roll, which can lead
>    to duplicates too.

But not instantly. And the only time we're doing that is immediately
after the insert.


>    The same query, selecting on 'id' from the Attachments table is
>    virtually instantaneous.
>