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 3D439D1B4E1 for ; Thu, 30 Oct 2003 19:16:59 +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 11759-08 for ; Thu, 30 Oct 2003 15:16:29 -0400 (AST) Received: from trade-india.com (ns5.trade-india.com [66.234.10.13]) by svr1.postgresql.org (Postfix) with SMTP id 5C145D1B536 for ; Thu, 30 Oct 2003 15:16:27 -0400 (AST) Received: (qmail 6549 invoked from network); 30 Oct 2003 19:17:36 -0000 Received: from unknown (HELO trade-india.com) (203.145.130.142) by ns5.trade-india.com with SMTP; 30 Oct 2003 19:17:36 -0000 Message-ID: <3FA34827.1030705@trade-india.com> Date: Sat, 01 Nov 2003 00:44:07 -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> In-Reply-To: <87r80ud0nw.fsf@stark.dyndns.tv> Content-Type: multipart/alternative; boundary="------------070306070203080407090902" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/772 X-Sequence-Number: 4520 This is a multi-part message in MIME format. --------------070306070203080407090902 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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) = '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=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) = 'mallah_rajesh@yahoo.com'::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 = 'mallah_rajesh@yahoo.com' 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 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 = '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; >> >> > >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 = '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; > > > > > > > > > --------------070306070203080407090902 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit

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) = '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=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) = 'mallah_rajesh@yahoo.com'::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 = 'mallah_rajesh@yahoo.com'
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 <mallah@trade-india.com> 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 = '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;
    

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 = '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;
       






  

--------------070306070203080407090902--