Re: [PERFORM] Query puts 7.3.4 on endless loop but 7.4beta5 is fine.

[Rajesh Kumar Mallah]

explain analyze of original Query: rt3=# explain analyze SELECT DISTINCT main.* FROM Tickets main JOIN Groups as Groups_1 ON ( main.id = Groups_1.Instance) JOIN Principals as Principals_2 ON ( Groups_1.id = Principals_2.ObjectId) JOIN CachedGroupMembers as CachedGroupMembers_3 ON ( Principals_2.id = CachedGroupMembers_3.GroupId) JOIN Users as Users_4 ON ( CachedGroupMembers_3.MemberId = Users_4.id) WHERE ((main.EffectiveId = main.id)) AND ((main.Type = 'ticket')) AND ( ( ( (lower(Users_4.EmailAddress) = '[EMAIL PROTECTED]')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') ) ) ORDER BY main.Priority DESC LIMIT 10; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=619.93..620.00 rows=1 width=164) (actual time=994.570..994.683 rows=4 loops=1) -> Unique (cost=619.93..620.00 rows=1 width=164) (actual time=994.565..994.672 rows=4 loops=1) -> Sort (cost=619.93..619.93 rows=1 width=164) (actual time=994.561..994.569 rows=8 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..619.92 rows=1 width=164) (actual time=1.374..993.998 rows=8 loops=1) -> Nested Loop (cost=0.00..610.83 rows=3 width=168) (actual time=0.691..839.633 rows=9617 loops=1) -> Nested Loop (cost=0.00..476.17 rows=1 width=168) (actual time=0.524..616.937 rows=3209 loops=1) -> Nested Loop (cost=0.00..471.54 rows=1 width=168) (actual time=0.376..503.774 rows=3209 loops=1) -> Seq Scan on tickets main (cost=0.00..465.62 rows=1 width=164) (actual time=0.114..60.044 rows=3209 loops=1) Filter: ((effectiveid = id) AND (("type")::text = 'ticket'::text) AND (((status)::text = 'new'::text) OR ((status)::text = 'open'::text))) -> Index Scan using groups1 on groups groups_1 (cost=0.00..5.90 rows=1 width=12) (actual time=0.111..0.119 rows=1 loops=3209) Index Cond: (((groups_1."domain")::text = 'RT::Ticket-Role'::text) AND (("outer".id)::text = (groups_1.instance)::text) AND ((groups_1."type")::text = 'Requestor'::text)) -> Index Scan using principals2 on principals principals_2 (cost=0.00..4.62 rows=1 width=8) (actual time=0.015..0.018 rows=1 loops=3209) Index Cond: ("outer".id = principals_2.objectid) Filter: ((principaltype)::text = 'Group'::text) -> Index Scan using cachedgroupmembers3 on cachedgroupmembers cachedgroupmembers_3 (cost=0.00..134.06 rows=47 width=8) (actual time=0.015..0.026 rows=3 loops=3209) Index Cond: ("outer".id = cachedgroupmembers_3.groupid) -> Index Scan using users_pkey on users users_4 (cost=0.00..3.02 rows=1 width=4) (actual time=0.013..0.013 rows=0 loops=9617) Index Cond: ("outer".memberid = users_4.id) Filter: (lower((emailaddress)::text) = '[EMAIL PROTECTED]'::text) Total runtime: 995.326 ms (21 rows) rt3=# 999 ms is not that bad but u think it deserves this many ms? Nopes the query are not Equiv , earlier one returns 4 rows and the below one none, can you spot any obvious and resend plz. thats why i did not do an explain analyze rt3=# SELECT * rt3-# FROM tickets rt3-# WHERE id IN ( rt3(# SELECT groups.instance rt3(# FROM groups rt3(# JOIN principals ON (groups.id = principals.objectid) rt3(# JOIN cachedgroupmembers ON (principals.id = cachedgroupmembers.groupid) rt3(# JOIN users ON (cachedgroupmembers.memberid = users.id) rt3(# WHERE users.emailaddress = '[EMAIL PROTECTED]' rt3(# AND groups.domain = 'RT::Ticket-Role' rt3(# AND groups.type = 'Requestor' rt3(# AND principals.principaltype = 'group' rt3(# ) rt3-# AND type = 'ticket' rt3-# AND effectiveid = tickets.id rt3-# AND (status = 'new' OR status = 'open') rt3-# ORDER BY priority DESC rt3-# LIMIT 10; id | effectiveid | queue | type | issuestatement | resolution | owner | subject | initialpriority | finalpriority | priority | timeestimated | timeworked | status | timeleft | told | starts | started | due | resolved | lastupdatedby | lastupdated | creator | created | disabled ----+-------------+-------+------+----------------+------------+-------+---------+-----------------+---------------+----------+---------------+------------+--------+----------+------+--------+---------+-----+----------+---------------+-------------+---------+---------+---------- (0 rows) Time: 2670.85 ms rt3=# Well it may be of interest to write the query in best possible way but i am not sure if it really helps the RT application becoz i do not know whether DBIx::SearchBuilder would currently allow auto generation of such arbitrary SQLs. Regds Mallah. Greg Stark wrote: Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes: rt3=# explain SELECT DISTINCT main.* FROM ((( (Tickets main JOIN Groups as Groups_1 ON ( main.id = Groups_1.Instance)) JOIN Principals as Principals_2 ON ( Groups_1.id = Principals_2.ObjectId) ) JOIN CachedGroupMembers as CachedGroupMembers_3 ON ( Principals_2.id = CachedGroupMembers_3.GroupId) ) JOIN Users as Users_4 ON ( CachedGroupMembers_3.MemberId = Users_4.id) ) WHERE ((main.EffectiveId = main.id)) AND ((main.Type = 'ticket')) AND ((( (Users_4.EmailAddress = '[EMAIL PROTECTED]') 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')) ) ORDER BY main.Priority DESC LIMIT 10; So this query seems to be going the long way around to do the equivalent of an IN clause. Presumably because as far as I know mysql didn't support IN subqueries until recently. Can you do an "explain analyze" on the above query and the following rewritten one in 7.4? The "analyze" is important because it'll give real timing information. And it's important that it be on 7.4 as there were improvements in this area specifically in 7.4. SELECT * FROM tickets WHERE id IN ( SELECT groups.instance FROM groups JOIN principals ON (groups.id = principals.objectid) JOIN cachedgroupmembers ON (principals.id = cachedgroupmembers.groupid) JOIN users ON (cachedgroupmembers.memberid = users.id) WHERE users.emailaddress = '[EMAIL PROTECTED]' AND groups.domain = 'RT::Ticket-Role' AND groups.type = 'Requestor' AND principals.principaltype = 'group' ) AND type = 'ticket' AND effectiveid = tickets.id AND (status = 'new' OR status = 'open') ORDER BY priority DESC LIMIT 10;