[rt-devel] extremely slow query in rt 3.0.10 due to ILIKE instead of =

Palle Girgensohn girgen at pingpong.net
Tue Mar 30 14:29:20 EST 2004


Hi,

With RT & the latest (0.99) DBIx-SearchBuilder, clicking "Home" takes 
approximately 20 second or more. Most of the time is spent running this 
query:

rt3=# explain analyze
rt3-# SELECT DISTINCT main.* FROM Tickets main , Groups Groups_1, 
CachedGroupMembers CachedGroupMembers_2, Users
rt3-#   Users_3  WHERE ((CachedGroupMembers_2.MemberId ILIKE Users_3.id)) 
AND ((Groups_1.id ILIKE CachedGroupMembers_2.GroupId)) AND
rt3-#  ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND 
((main.Type = 'ticket')) AND ((main.id ILIKE
rt3(#  Groups_1.Instance)) AND ( (  ( (Users_3.EmailAddress ILIKE 
'girgen at pingpong.net')AND(Groups_1.Domain =
rt3(#  'RT::Ticket-Role')AND(Groups_1.Type = 'Requestor') )  ) AND ( 
(main.Status = 'new')OR(main.Status = 'open') ) ) ORDER BY
rt3-#  main.Priority DESC LIMIT 10;
 
QUERY PLAN 

---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------
 Limit  (cost=729.00..729.19 rows=1 width=193) (actual 
time=20850.989..20851.960 rows=10 loops=1)
   ->  Unique  (cost=729.00..729.19 rows=1 width=193) (actual 
time=20850.975..20851.874 rows=10 loops=1)
         ->  Sort  (cost=729.00..729.01 rows=3 width=193) (actual 
time=20850.961..20851.033 rows=19 loops=1)
               Sort Key: main.priority, main.id, main.effectiveid, 
main.queue, main."type", main.issuestatement, main.resolution, 
main."owner", main.subject, main.initialpriority, main.finalpriority, 
main.timeestimated, main.timeworked, main.status, main.timeleft, main.told, 
main.starts, main.started, main.due, main.resolved, main.lastupdatedby, 
main.lastupdated, main.creator, main.created, main.disabled
               ->  Nested Loop  (cost=256.11..728.97 rows=3 width=193) 
(actual time=19122.777..20847.490 rows=58 loops=1)
                     Join Filter: (("inner".id)::text ~~* 
("outer".groupid)::text)
                     ->  Nested Loop  (cost=0.00..459.97 rows=67 width=4) 
(actual time=0.308..291.242 rows=533 loops=1)
                           Join Filter: (("inner".memberid)::text ~~* 
("outer".id)::text)
                           ->  Seq Scan on users users_3  (cost=0.00..2.69 
rows=1 width=4) (actual time=0.104..0.347 rows=1 loops=1)
                                 Filter: ((emailaddress)::text ~~* 
'girgen at pingpong.net'::text)
                           ->  Seq Scan on cachedgroupmembers 
cachedgroupmembers_2  (cost=0.00..224.83 rows=13283 width=8) (actual 
time=0.015..104.128 rows=13283 loops=1)
                     ->  Materialize  (cost=256.11..256.18 rows=7 
width=197) (actual time=0.023..36.582 rows=161 loops=533)
                           ->  Nested Loop  (cost=0.00..256.11 rows=7 
width=197) (actual time=9.800..19068.908 rows=161 loops=1)
                                 Join Filter: (("outer".id)::text ~~* 
("inner".instance)::text)
                                 ->  Seq Scan on tickets main 
(cost=0.00..77.16 rows=1 width=193) (actual time=0.083..29.923 rows=161 
loops=1)
                                       Filter: ((effectiveid = id) AND 
((status)::text <> 'deleted'::text) AND (("type")::text = 'ticket'::text) 
AND (((status)::text = 'new'::text) OR ((status)::text = 'open'::text)))
                                 ->  Seq Scan on groups groups_1 
(cost=0.00..155.50 rows=1340 width=8) (actual time=0.845..91.976 rows=1603 
loops=161)
                                       Filter: ((("domain")::text = 
'RT::Ticket-Role'::text) AND (("type")::text = 'Requestor'::text))
 Total runtime: 20853.432 ms
(19 rows)


~21 seconds... this is because of the ILIKE:s. I have postgresql intialized 
with LC_ALL=sv_SE.ISO8859-1. Indices are not used in postgresql for "LIKE" 
when the database collation order is not of locale 'C'. I usually want 
this, since I need sorting to be localized.

Looking at the query above, I can't see that ILIKE cannot be replaced with 
`=' in all cases. I'd say the way it is done now is a bug. I cannot find my 
way around the RT code, so I'd appreciate some help doing this.

Here's the same query without the ILIKE:s:

rt3=# explain analyze
rt3-# SELECT DISTINCT main.* FROM Tickets main , Groups Groups_1, 
CachedGroupMembers CachedGroupMembers_2, Users
rt3-#   Users_3  WHERE ((CachedGroupMembers_2.MemberId = Users_3.id)) AND 
((Groups_1.id = CachedGroupMembers_2.GroupId)) AND
rt3-#  ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND 
((main.Type = 'ticket')) AND ((main.id =
rt3(#  Groups_1.Instance)) AND ( (  ( (Users_3.EmailAddress = 
'girgen at pingpong.net')AND(Groups_1.Domain =
rt3(#  'RT::Ticket-Role')AND(Groups_1.Type = 'Requestor') )  ) AND ( 
(main.Status = 'new')OR(main.Status = 'open') ) ) ORDER BY
rt3-#  main.Priority DESC LIMIT 10;
 
QUERY PLAN 

---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------
 Limit  (cost=40.25..40.32 rows=1 width=193) (actual time=100.423..101.392 
rows=10 loops=1)
   ->  Unique  (cost=40.25..40.32 rows=1 width=193) (actual 
time=100.410..101.308 rows=10 loops=1)
         ->  Sort  (cost=40.25..40.26 rows=1 width=193) (actual 
time=100.395..100.468 rows=19 loops=1)
               Sort Key: main.priority, main.id, main.effectiveid, 
main.queue, main."type", main.issuestatement, main.resolution, 
main."owner", main.subject, main.initialpriority, main.finalpriority, 
main.timeestimated, main.timeworked, main.status, main.timeleft, main.told, 
main.starts, main.started, main.due, main.resolved, main.lastupdatedby, 
main.lastupdated, main.creator, main.created, main.disabled
               ->  Nested Loop  (cost=0.00..40.24 rows=1 width=193) (actual 
time=1.164..95.580 rows=58 loops=1)
                     ->  Nested Loop  (cost=0.00..32.28 rows=2 width=4) 
(actual time=0.840..64.753 rows=316 loops=1)
                           ->  Nested Loop  (cost=0.00..13.89 rows=6 
width=4) (actual time=0.351..17.903 rows=533 loops=1)
                                 ->  Seq Scan on users users_3 
(cost=0.00..2.69 rows=1 width=4) (actual time=0.117..0.356 rows=1 loops=1)
                                       Filter: ((emailaddress)::text = 
'girgen at pingpong.net'::text)
                                 ->  Index Scan using cachedgroupmembers2 
on cachedgroupmembers cachedgroupmembers_2  (cost=0.00..11.13 rows=5 
width=8) (actual time=0.198..10.070 rows=533 loops=1)
                                       Index Cond: 
(cachedgroupmembers_2.memberid = "outer".id)
                           ->  Index Scan using groups_pkey on groups 
groups_1  (cost=0.00..3.05 rows=1 width=8) (actual time=0.060..0.065 rows=1 
loops=533)
                                 Index Cond: (groups_1.id = "outer".groupid)
                                 Filter: ((("domain")::text = 
'RT::Ticket-Role'::text) AND (("type")::text = 'Requestor'::text))
                     ->  Index Scan using tickets_pkey on tickets main 
(cost=0.00..3.97 rows=1 width=193) (actual time=0.069..0.071 rows=0 
loops=316)
                           Index Cond: (main.id = "outer".instance)
                           Filter: ((effectiveid = id) AND ((status)::text 
<> 'deleted'::text) AND (("type")::text = 'ticket'::text) AND 
(((status)::text = 'new'::text) OR ((status)::text = 'open'::text)))
 Total runtime: 103.182 ms


Postgresql-7.4.2
FreeBSD 4.9 stable
DBIx-SearchBuilder 0.99
Perl 5.8.3

0,1 s, pretty darn much better... ;-)

So, in short, I'd like to exterminate unnecessesary ILIKEs. In this 
example, all are.

Any ideas how to pursue this?

/Palle




More information about the Rt-devel mailing list