[rt-users] postgres slowdowns? (pg 7.3.4, rt 3.0.4)

[Justin Hawkins]

On Thu, Sep 25, 2003 at 03:11:25PM +0930, Justin Hawkins wrote:
> 
> I too am having performance issues. I tried DBIx::SearchBuilder 0.90
> without any benefit.

Ahh! I think I've nailed this proper. See the highlighted part
of the query below:

> SELECT DISTINCT main.*
> FROM   Tickets main, 
>        Groups Groups_1, 
>        Principals Principals_2, 
>        CachedGroupMembers CachedGroupMembers_3, 
>        Principals Principals_4, 
>        Users Users_5
> WHERE  ((main.EffectiveId = main.id)) AND ((main.Type = 'ticket'))
>   AND  (
>         (
>          (
>           (lower(Users_5.EmailAddress) = 'email at address.here') AND 
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^
>           (Groups_1.Domain = 'RT::Ticket-Role') AND 
>           (Groups_1.Type = 'Requestor') AND 
>           (Principals_2.PrincipalType = 'Group') 
>          ) 
>         ) AND ( (main.Status = 'new') OR (main.Status = 'open') ) 
>        )
>   AND     main.id = Groups_1.Instance 
>   AND     Groups_1.id = Principals_2.ObjectId 
>   AND     Principals_2.id = CachedGroupMembers_3.GroupId 
>   AND     CachedGroupMembers_3.MemberId = Principals_4.id  
>   AND     Principals_4.ObjectId = Users_5.id
> ORDER BY main.Priority DESC LIMIT 10;

Changing that line to read:

(Users_5.EmailAddress = 'email at address.here') AND

Changes the query time from several minutes (!) to less than a second.

Obviously the query is not quite right then, since I am guessing we
do care about case insensitivity. So perhaps:

(Users_5.EmailAddress ILIKE 'email at address.here') AND

though I haven't tested that yet. I will and report back.

I'm guessing this would need to be changed in SearchBuilder?

	- Justin