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

Hi ,

Here are the Execution Plans ,
Sorry for the delay .

Regds
Mallah

On PostgreSQL 7.3.4

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 = 'mallah_rajesh(at)yahoo(dot)com')
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;

Limit (cost=2044.52..2044.58 rows=1 width=195)
-> Unique (cost=2044.52..2044.58 rows=1 width=195)
-> Sort (cost=2044.52..2044.52 rows=1 width=195)
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
-> Hash Join (cost=3.98..2044.51 rows=1 width=195)
Hash Cond: ("outer".memberid = "inner".id)
-> Nested Loop (cost=0.00..2040.51 rows=2 width=191)
-> Nested Loop (cost=0.00..1914.41 rows=1 width=183)
-> Nested Loop (cost=0.00..1909.67 rows=1 width=175)
Join Filter: (("outer".id)::text = ("inner".instance)::text)
-> Seq Scan on tickets main (cost=0.00..465.62 rows=1 width=163)
Filter: ((effectiveid = id) AND ("type" = 'ticket'::character varying) AND ((status = 'new'::character varying) OR (status = 'open'::character varying)))
-> Index Scan using groups_domain on groups groups_1 (cost=0.00..1338.03 rows=7068 width=12)
Index Cond: ("domain" = 'RT::Ticket-Role'::character varying)
Filter: ("type" = 'Requestor'::character varying)
-> Index Scan using principals2 on principals principals_2 (cost=0.00..4.73 rows=1 width=8)
Index Cond: ("outer".id = principals_2.objectid)
Filter: (principaltype = 'Group'::character varying)
-> Index Scan using cachedgroupmembers3 on cachedgroupmembers cachedgroupmembers_3 (cost=0.00..125.54 rows=45 width=8)
Index Cond: ("outer".id = cachedgroupmembers_3.groupid)
-> Hash (cost=3.98..3.98 rows=1 width=4)
-> Index Scan using users4 on users users_4 (cost=0.00..3.98 rows=1 width=4)
Index Cond: (emailaddress = 'mallah_rajesh(at)yahoo(dot)com'::character varying)
(23 rows)

On PostgreSQL 7.4 beta 5

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 = 'mallah_rajesh(at)yahoo(dot)com')
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=582.27..582.34 rows=1 width=164)
-> Unique (cost=582.27..582.34 rows=1 width=164)
-> Sort (cost=582.27..582.28 rows=1 width=164)
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
-> Hash Join (cost=476.18..582.26 rows=1 width=164)
Hash Cond: ("outer".groupid = "inner".id)
-> Nested Loop (cost=0.00..105.97 rows=21 width=4)
-> Index Scan using users4 on users users_4 (cost=0.00..3.99 rows=2 width=4)
Index Cond: ((emailaddress)::text = 'mallah_rajesh(at)yahoo(dot)com'::text)
-> Index Scan using cachedgroupmembers2 on cachedgroupmembers cachedgroupmembers_3 (cost=0.00..50.81 rows=14 width=8)
Index Cond: (cachedgroupmembers_3.memberid = "outer".id)
-> Hash (cost=476.17..476.17 rows=1 width=168)
-> Nested Loop (cost=0.00..476.17 rows=1 width=168)
-> Nested Loop (cost=0.00..471.54 rows=1 width=168)
-> Seq Scan on tickets main (cost=0.00..465.62 rows=1 width=164)
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)
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)
Index Cond: ("outer".id = principals_2.objectid)
Filter: ((principaltype)::text = 'Group'::text)
(21 rows)

rt3=#

Christopher Browne wrote:

>In the last exciting episode, mallah(at)trade-india(dot)com wrote:
>
>
>>>mallah(at)trade-india(dot)com (Rajesh Kumar Mallah) wrote:
>>>
>>>
>>>>Can you please have a Look at the below and suggest why it
>>>>apparently puts 7.3.4 on an infinite loop . the CPU utilisation of the backend running it
>>>>approches 99%.
>>>>
>>>>
>>>What would be useful, for this case, would be to provide the query plan, perhaps via
>>>
>>> EXPLAIN [Big Long Query].
>>>
>>>The difference between that EXPLAIN and what you get on 7.4 might be quite interesting.
>>>
>>>I would think it quite unlikely that it is truly an "infinite" loop; it is rather more likely
>>>that the plan winds up being pretty bad and doing something [a bunch of nested loops, maybe?]
>>>that run longer than your patience will permit.
>>>
>>>
>>:-) ok i will leave it running and try to get it.
>>
>>
>
>No, if you just do EXPLAIN (and not EXPLAIN ANALYZE), that returns
>without executing the query.
>
>If the query runs for a really long time, then we _know_ that there is
>something troublesome. EXPLAIN (no ANALYZE) should provide some
>insight without having anything run for a long time.
>
>If EXPLAIN [big long query] turns into what you are terming an
>"infinite loop," then you have a quite different problem, and it would
>be very useful to know that.
>
>