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 910DAD1B4E9 for ; Thu, 30 Oct 2003 22:01:39 +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 44529-02 for ; Thu, 30 Oct 2003 18:01:09 -0400 (AST) Received: from trade-india.com (ns5.trade-india.com [66.234.10.13]) by svr1.postgresql.org (Postfix) with SMTP id A5467D1B511 for ; Thu, 30 Oct 2003 18:01:06 -0400 (AST) Received: (qmail 25831 invoked from network); 30 Oct 2003 22:02:26 -0000 Received: from unknown (HELO trade-india.com) (203.145.130.142) by ns5.trade-india.com with SMTP; 30 Oct 2003 22:02:26 -0000 Message-ID: <3FA36F4C.2000509@trade-india.com> Date: Sat, 01 Nov 2003 03:31:08 -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: Greg Stark Cc: Christopher Browne , pgsql-performance@postgresql.org, Jesse Vincent 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> <3FA323C2.2060302@trade-india.com> <87r80ud0nw.fsf@stark.dyndns.tv> <3FA34827.1030705@trade-india.com> <87ad7icsxw.fsf@stark.dyndns.tv> In-Reply-To: <87ad7icsxw.fsf@stark.dyndns.tv> Content-Type: multipart/alternative; boundary="------------040101020809020906010105" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/780 X-Sequence-Number: 4528 This is a multi-part message in MIME format. --------------040101020809020906010105 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit The g in group had to be uppercased, the query produced the same results but performance was worse for the IN version . 2367 ms vs 600 ms rt3=# explain analyze 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 lower(users.emailaddress) = 'mallah_rajesh@yahoo.com' 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;; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=10078.18..10078.19 rows=1 width=164) (actual time=2367.084..2367.096 rows=4 loops=1) -> Sort (cost=10078.18..10078.19 rows=1 width=164) (actual time=2367.078..2367.082 rows=4 loops=1) Sort Key: tickets.priority -> Hash Join (cost=10077.65..10078.17 rows=1 width=164) (actual time=2366.870..2367.051 rows=4 loops=1) Hash Cond: (("outer".instance)::text = ("inner".id)::text) -> HashAggregate (cost=9612.02..9612.02 rows=69 width=8) (actual time=2303.792..2303.810 rows=7 loops=1) -> Hash Join (cost=4892.97..9611.85 rows=69 width=8) (actual time=1427.260..2303.685 rows=14 loops=1) Hash Cond: ("outer".memberid = "inner".id) -> Hash Join (cost=4523.65..9139.45 rows=13651 width=12) (actual time=948.960..2258.529 rows=31123 loops=1) Hash Cond: ("outer".groupid = "inner".id) -> Seq Scan on cachedgroupmembers (cost=0.00..3456.51 rows=204551 width=8) (actual time=0.048..365.147 rows=204551 loops=1) -> Hash (cost=4509.93..4509.93 rows=5488 width=12) (actual time=948.843..948.843 rows=0 loops=1) -> Hash Join (cost=1409.91..4509.93 rows=5488 width=12) (actual time=315.722..930.025 rows=10431 loops=1) Hash Cond: ("outer".objectid = "inner".id) -> Seq Scan on principals (cost=0.00..1583.76 rows=62625 width=8) (actual time=0.043..251.142 rows=62097 loops=1) Filter: ((principaltype)::text = 'Group'::text) -> Hash (cost=1359.90..1359.90 rows=7204 width=12) (actual time=315.458..315.458 rows=0 loops=1) -> Index Scan using groups_domain on groups (cost=0.00..1359.90 rows=7204 width=12) (actual time=0.325..297.403 rows=10431 loops=1) Index Cond: (("domain")::text = 'RT::Ticket-Role'::text) Filter: (("type")::text = 'Requestor'::text) -> Hash (cost=369.08..369.08 rows=101 width=4) (actual time=0.157..0.157 rows=0 loops=1) -> Index Scan using users_emailaddress_lower on users (cost=0.00..369.08 rows=101 width=4) (actual time=0.139..0.143 rows=1 loops=1) Index Cond: (lower((emailaddress)::text) = 'mallah_rajesh@yahoo.com'::text) -> Hash (cost=465.62..465.62 rows=1 width=164) (actual time=62.944..62.944 rows=0 loops=1) -> Seq Scan on tickets (cost=0.00..465.62 rows=1 width=164) (actual time=0.113..52.729 rows=3208 loops=1) Filter: ((("type")::text = 'ticket'::text) AND (effectiveid = id) AND (((status)::text = 'new'::text) OR ((status)::text = 'open'::text))) Total runtime: 2367.908 ms (27 rows) rt3=# explain analyze SELECT DISTINCT main.* FROM ((((Tickets main JOIN Groups as Groups_1 ON ( main.id = Groups_1.Instance)) rt3(# JOIN Principals as Principals_2 ON ( Groups_1.id = Principals_2.ObjectId)) JOIN CachedGroupMembers as CachedGroupMembers_3 rt3(# ON ( Principals_2.id = CachedGroupMembers_3.GroupId)) JOIN Users as Users_4 ON ( CachedGroupMembers_3.MemberId = Users_4.id)) rt3-# WHERE ((main.EffectiveId = main.id)) AND ((main.Type = 'ticket')) AND ( ( ( (Users_4.EmailAddress = 'mallah_rajesh@yahoo.com') rt3(# AND(Groups_1.Domain = 'RT::Ticket-Role')AND(Groups_1.Type = 'Requestor')AND(Principals_2.PrincipalType = 'Group') ) ) rt3(# 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) (actual time=592.406..592.529 rows=4 loops=1) -> Unique (cost=582.27..582.34 rows=1 width=164) (actual time=592.401..592.516 rows=4 loops=1) -> Sort (cost=582.27..582.28 rows=1 width=164) (actual time=592.398..592.406 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 -> Hash Join (cost=476.18..582.26 rows=1 width=164) (actual time=591.548..592.211 rows=8 loops=1) Hash Cond: ("outer".groupid = "inner".id) -> Nested Loop (cost=0.00..105.97 rows=21 width=4) (actual time=0.214..0.645 rows=37 loops=1) -> Index Scan using users4 on users users_4 (cost=0.00..3.99 rows=2 width=4) (actual time=0.107..0.112 rows=1 loops=1) 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) (actual time=0.098..0.441 rows=37 loops=1) Index Cond: (cachedgroupmembers_3.memberid = "outer".id) -> Hash (cost=476.17..476.17 rows=1 width=168) (actual time=591.121..591.121 rows=0 loops=1) -> Nested Loop (cost=0.00..476.17 rows=1 width=168) (actual time=0.391..583.085 rows=3208 loops=1) -> Nested Loop (cost=0.00..471.54 rows=1 width=168) (actual time=0.309..474.968 rows=3208 loops=1) -> Seq Scan on tickets main (cost=0.00..465.62 rows=1 width=164) (actual time=0.111..56.930 rows=3208 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.105..0.112 rows=1 loops=3208) 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.014..0.017 rows=1 loops=3208) Index Cond: ("outer".id = principals_2.objectid) Filter: ((principaltype)::text = 'Group'::text) Total runtime: 593.062 ms (22 rows) Regds Mallah. Greg Stark wrote: >Rajesh Kumar Mallah writes: > > > >>Nopes the query are not Equiv , earlier one returns 4 rows and the below one >>none, >> >> > >Sorry, i lowercased a string constant and dropped the lower() on email. > >Try this: > >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 lower(users.emailaddress) = 'mallah_rajesh@yahoo.com' > 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; > > > --------------040101020809020906010105 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit
The g in group had to be uppercased, the query produced the same results
but performance was worse  for the IN version .  2367 ms vs 600 ms
rt3=# explain analyze 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 lower(users.emailaddress) = 'mallah_rajesh@yahoo.com' 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;;

                                                                                       QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10078.18..10078.19 rows=1 width=164) (actual time=2367.084..2367.096 rows=4 loops=1)
   ->  Sort  (cost=10078.18..10078.19 rows=1 width=164) (actual time=2367.078..2367.082 rows=4 loops=1)
         Sort Key: tickets.priority
         ->  Hash Join  (cost=10077.65..10078.17 rows=1 width=164) (actual time=2366.870..2367.051 rows=4 loops=1)
               Hash Cond: (("outer".instance)::text = ("inner".id)::text)
               ->  HashAggregate  (cost=9612.02..9612.02 rows=69 width=8) (actual time=2303.792..2303.810 rows=7 loops=1)
                     ->  Hash Join  (cost=4892.97..9611.85 rows=69 width=8) (actual time=1427.260..2303.685 rows=14 loops=1)
                           Hash Cond: ("outer".memberid = "inner".id)
                           ->  Hash Join  (cost=4523.65..9139.45 rows=13651 width=12) (actual time=948.960..2258.529 rows=31123 loops=1)
                                 Hash Cond: ("outer".groupid = "inner".id)
                                 ->  Seq Scan on cachedgroupmembers  (cost=0.00..3456.51 rows=204551 width=8) (actual time=0.048..365.147 rows=204551 loops=1)
                                 ->  Hash  (cost=4509.93..4509.93 rows=5488 width=12) (actual time=948.843..948.843 rows=0 loops=1)
                                       ->  Hash Join  (cost=1409.91..4509.93 rows=5488 width=12) (actual time=315.722..930.025 rows=10431 loops=1)
                                             Hash Cond: ("outer".objectid = "inner".id)
                                             ->  Seq Scan on principals  (cost=0.00..1583.76 rows=62625 width=8) (actual time=0.043..251.142 rows=62097 loops=1)
                                                   Filter: ((principaltype)::text = 'Group'::text)
                                             ->  Hash  (cost=1359.90..1359.90 rows=7204 width=12) (actual time=315.458..315.458 rows=0 loops=1)
                                                   ->  Index Scan using groups_domain on groups  (cost=0.00..1359.90 rows=7204 width=12) (actual time=0.325..297.403 rows=10431 loops=1)
                                                         Index Cond: (("domain")::text = 'RT::Ticket-Role'::text)
                                                         Filter: (("type")::text = 'Requestor'::text)
                           ->  Hash  (cost=369.08..369.08 rows=101 width=4) (actual time=0.157..0.157 rows=0 loops=1)
                                 ->  Index Scan using users_emailaddress_lower on users  (cost=0.00..369.08 rows=101 width=4) (actual time=0.139..0.143 rows=1 loops=1)
                                       Index Cond: (lower((emailaddress)::text) = 'mallah_rajesh@yahoo.com'::text)
               ->  Hash  (cost=465.62..465.62 rows=1 width=164) (actual time=62.944..62.944 rows=0 loops=1)
                     ->  Seq Scan on tickets  (cost=0.00..465.62 rows=1 width=164) (actual time=0.113..52.729 rows=3208 loops=1)
                           Filter: ((("type")::text = 'ticket'::text) AND (effectiveid = id) AND (((status)::text = 'new'::text) OR ((status)::text = 'open'::text)))
 Total runtime: 2367.908 ms
(27 rows)



rt3=# explain analyze SELECT DISTINCT main.* FROM ((((Tickets main  JOIN Groups as Groups_1  ON ( main.id = Groups_1.Instance))
rt3(# JOIN Principals as Principals_2  ON ( Groups_1.id = Principals_2.ObjectId)) JOIN CachedGroupMembers as CachedGroupMembers_3
rt3(# ON ( Principals_2.id = CachedGroupMembers_3.GroupId))  JOIN Users as Users_4  ON ( CachedGroupMembers_3.MemberId = Users_4.id))
rt3-# WHERE ((main.EffectiveId = main.id)) AND ((main.Type = 'ticket')) AND ( (  ( (Users_4.EmailAddress = 'mallah_rajesh@yahoo.com')
rt3(# AND(Groups_1.Domain = 'RT::Ticket-Role')AND(Groups_1.Type = 'Requestor')AND(Principals_2.PrincipalType = 'Group') )  )
rt3(# 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) (actual time=592.406..592.529 rows=4 loops=1)
   ->  Unique  (cost=582.27..582.34 rows=1 width=164) (actual time=592.401..592.516 rows=4 loops=1)
         ->  Sort  (cost=582.27..582.28 rows=1 width=164) (actual time=592.398..592.406 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
               ->  Hash Join  (cost=476.18..582.26 rows=1 width=164) (actual time=591.548..592.211 rows=8 loops=1)
                     Hash Cond: ("outer".groupid = "inner".id)
                     ->  Nested Loop  (cost=0.00..105.97 rows=21 width=4) (actual time=0.214..0.645 rows=37 loops=1)
                           ->  Index Scan using users4 on users users_4  (cost=0.00..3.99 rows=2 width=4) (actual time=0.107..0.112 rows=1 loops=1)
                                 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) (actual time=0.098..0.441 rows=37 loops=1)
                                 Index Cond: (cachedgroupmembers_3.memberid = "outer".id)
                     ->  Hash  (cost=476.17..476.17 rows=1 width=168) (actual time=591.121..591.121 rows=0 loops=1)
                           ->  Nested Loop  (cost=0.00..476.17 rows=1 width=168) (actual time=0.391..583.085 rows=3208 loops=1)
                                 ->  Nested Loop  (cost=0.00..471.54 rows=1 width=168) (actual time=0.309..474.968 rows=3208 loops=1)
                                       ->  Seq Scan on tickets main  (cost=0.00..465.62 rows=1 width=164) (actual time=0.111..56.930 rows=3208 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.105..0.112 rows=1 loops=3208)
                                             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.014..0.017 rows=1 loops=3208)
                                       Index Cond: ("outer".id = principals_2.objectid)
                                       Filter: ((principaltype)::text = 'Group'::text)
 Total runtime: 593.062 ms
(22 rows)




Regds
Mallah.

Greg Stark wrote:
Rajesh Kumar Mallah <mallah@trade-india.com> writes:

  
Nopes the query are not Equiv , earlier one returns 4 rows and the below one
none,
    

Sorry, i lowercased a string constant and dropped the lower() on email. 

Try this:

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 lower(users.emailaddress) = 'mallah_rajesh@yahoo.com'
          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;

  

--------------040101020809020906010105--