X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 76963D1B949 for ; Thu, 30 Oct 2003 16:39:27 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 88821-01 for ; Thu, 30 Oct 2003 12:38:56 -0400 (AST) Received: from trade-india.com (ns5.trade-india.com [66.234.10.13]) by svr1.postgresql.org (Postfix) with SMTP id 2FEE8D1B58D for ; Thu, 30 Oct 2003 12:38:54 -0400 (AST) Received: (qmail 23901 invoked from network); 30 Oct 2003 16:40:09 -0000 Received: from unknown (HELO trade-india.com) (203.145.130.142) by ns5.trade-india.com with SMTP; 30 Oct 2003 16:40:09 -0000 Message-ID: <3FA323C2.2060302@trade-india.com> Date: Fri, 31 Oct 2003 22:08:50 -0500 From: Rajesh Kumar Mallah User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030630 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Christopher Browne Cc: pgsql-performance@postgresql.org Subject: Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. References: <33298.203.145.130.142.1067457626.squirrel@mail.trade-india.com> <20031029202920.GZ7337@pallas.fsck.com> <33402.203.145.130.142.1067460429.squirrel@mail.trade-india.com> <200310301702.00275.mallah@trade-india.com> <13042.61.16.154.82.1067523120.squirrel@mail.trade-india.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------000303000106050002000202" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/761 X-Sequence-Number: 4509 This is a multi-part message in MIME format. --------------000303000106050002000202 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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@yahoo.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@yahoo.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@yahoo.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@yahoo.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@trade-india.com wrote: > > >>>mallah@trade-india.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. > > --------------000303000106050002000202 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit
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@yahoo.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@yahoo.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@yahoo.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@yahoo.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@trade-india.com wrote:
  
mallah@trade-india.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.
  

--------------000303000106050002000202--