[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