[rt-users] Re: query problem after 3.4.5 -> 3.6.4 upgrade

[Kenneth Marshall]

Ruslan,

I tested the last two queries on my PostgreSQL 8.2 database. The
original query was taking so long that I killed it:

On Wed, Jul 25, 2007 at 02:33:58AM +0400, Ruslan Zakirov wrote:
>  Hello, Brian.
> Ok, I've forgotten about Status field and we're talking only about
> "Requestor.EmailAddress = tom OR Owner = tom". I think that I've found
> problem. Here are three queries I want you to compare. Other user can
> do that too, but please replace "main.Owner = ####" with some valid
> user ID as it's very important for mysql optimizer.
> 
> The first one is the query we build in 3.6.4:
> SELECT SQL_NO_CACHE COUNT(DISTINCT main.id)
> FROM Tickets main
>    CROSS JOIN Users Users_3
>    JOIN Groups Groups_1
>        ON Groups_1.Domain = 'RT::Ticket-Role'
>        AND Groups_1.Type = 'Requestor'
>        AND Groups_1.Instance = main.id
>    LEFT JOIN CachedGroupMembers CachedGroupMembers_2
>        ON CachedGroupMembers_2.MemberId = Users_3.id
>        AND CachedGroupMembers_2.GroupId = Groups_1.id
> WHERE
>    main.Status != 'deleted'
>    AND ( main.Owner = '58936'
>        OR ( Users_3.EmailAddress LIKE '%tom%'
>             AND CachedGroupMembers_2.id IS NOT NULL )
>    )
>    AND main.Type = 'ticket'
>    AND main.EffectiveId = main.id;
> 

This version took 4.8 seconds without any additional tuning:

> This is one way to fix it:
> SELECT SQL_NO_CACHE COUNT(DISTINCT main.id)
> FROM Tickets main
>    CROSS JOIN Users Users_3
>    JOIN Groups Groups_1
>        ON Groups_1.Domain = 'RT::Ticket-Role'
>        AND Groups_1.Type = 'Requestor'
>        AND Groups_1.Instance = main.id
>    LEFT JOIN CachedGroupMembers CachedGroupMembers_2
>        ON CachedGroupMembers_2.MemberId = Users_3.id
>        AND CachedGroupMembers_2.GroupId = Groups_1.id
> WHERE
>    main.Status != 'deleted'
>    AND ( main.Owner = '58936' OR CachedGroupMembers_2.id IS NOT NULL )
>    AND Users_3.EmailAddress LIKE '%tom%'
>    AND main.Type = 'ticket'
>    AND main.EffectiveId = main.id;
> 

And this one took 7.6 seconds. Just some more data points although
I know that it is not MySQL. I did have to remove the SQL_NO_CACHE
from the command.

Ken

> Another way to fix the problem:
> SELECT SQL_NO_CACHE COUNT(DISTINCT main.id)
> FROM Tickets main
>    JOIN Groups Groups_1
>        ON  Groups_1.Domain = 'RT::Ticket-Role'
>        AND Groups_1.Type = 'Requestor'
>        AND Groups_1.Instance = main.id
>    LEFT JOIN CachedGroupMembers CachedGroupMembers_2
>        ON CachedGroupMembers_2.GroupId = Groups_1.id
>    LEFT JOIN Users Users_3
>        ON CachedGroupMembers_2.MemberId = Users_3.id
> WHERE
>    main.Status != 'deleted'
>    AND main.Type = 'ticket'
>    AND main.EffectiveId = main.id
>    AND ( main.Owner = '58936' OR Users_3.EmailAddress LIKE '%tom%' );
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
> 
> 
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
> Buy a copy at http://rtbook.bestpractical.com
>