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 B13F1D1B50E for ; Thu, 30 Oct 2003 22:21:30 +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 44626-10 for ; Thu, 30 Oct 2003 18:21:00 -0400 (AST) Received: from trade-india.com (ns5.trade-india.com [66.234.10.13]) by svr1.postgresql.org (Postfix) with SMTP id C57A2D1B8A0 for ; Thu, 30 Oct 2003 18:20:57 -0400 (AST) Received: (qmail 27522 invoked from network); 30 Oct 2003 22:22:17 -0000 Received: from unknown (HELO trade-india.com) (203.145.130.142) by ns5.trade-india.com with SMTP; 30 Oct 2003 22:22:17 -0000 Message-ID: <3FA373F3.9040103@trade-india.com> Date: Sat, 01 Nov 2003 03:50:59 -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="------------060402080107040008000108" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/781 X-Sequence-Number: 4529 This is a multi-part message in MIME format. --------------060402080107040008000108 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit But the new version at lease works on 7.3 instead of putting it in an infinite loop. rt3=# explain analyze SELECT * from tickets where id in ( SELECT groups.instance FROM groups rt3(# JOIN principals ON (groups.id = principals.objectid) JOIN cachedgroupmembers ON rt3(# (principals.id = cachedgroupmembers.groupid) JOIN users ON (cachedgroupmembers.memberid = users.id) rt3(# WHERE lower(users.emailaddress) = 'mallah_rajesh@yahoo.com' AND groups.domain = 'RT::Ticket-Role' rt3(# AND groups.type = 'Requestor' AND principals.principaltype = 'Group' ) AND type = 'ticket' AND rt3-# effectiveid = tickets.id AND (status = 'new' OR status = 'open') ORDER BY priority DESC LIMIT 10; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=88073404.73..88073404.73 rows=1 width=163) (actual time=2859.05..2859.07 rows=4 loops=1) -> Sort (cost=88073404.73..88073404.73 rows=1 width=163) (actual time=2859.05..2859.05 rows=4 loops=1) Sort Key: priority -> Seq Scan on tickets (cost=0.00..88073404.72 rows=1 width=163) (actual time=2525.48..2858.95 rows=4 loops=1) Filter: (("type" = 'ticket'::character varying) AND (effectiveid = id) AND ((status = 'new'::character varying) OR (status = 'open'::character varying)) AND (subplan)) SubPlan -> Materialize (cost=8443.38..8443.38 rows=66 width=32) (actual time=0.79..0.81 rows=14 loops=3209) -> Hash Join (cost=3698.35..8443.38 rows=66 width=32) (actual time=1720.53..2525.07 rows=14 loops=1) Hash Cond: ("outer".memberid = "inner".id) -> Hash Join (cost=3329.03..7973.87 rows=13247 width=28) (actual time=1225.83..2458.48 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.06..638.91 rows=204551 loops=1) -> Hash (cost=3315.71..3315.71 rows=5325 width=20) (actual time=1225.51..1225.51 rows=0 loops=1) -> Hash Join (cost=1355.70..3315.71 rows=5325 width=20) (actual time=529.02..1191.94 rows=10431 loops=1) Hash Cond: ("outer".objectid = "inner".id) -> Seq Scan on principals (cost=0.00..1583.76 rows=61940 width=8) (actual time=0.02..450.42 rows=62097 loops=1) Filter: (principaltype = 'Group'::character varying) -> Hash (cost=1338.03..1338.03 rows=7068 width=12) (actual time=528.58..528.58 rows=0 loops=1) -> Index Scan using groups_domain on groups (cost=0.00..1338.03 rows=7068 width=12) (actual time=0.18..498.04 rows=10431 loops=1) Index Cond: ("domain" = 'RT::Ticket-Role'::character varying) Filter: ("type" = 'Requestor'::character varying) -> Hash (cost=369.08..369.08 rows=101 width=4) (actual time=0.10..0.10 rows=0 loops=1) -> Index Scan using users_emailaddress on users (cost=0.00..369.08 rows=101 width=4) (actual time=0.09..0.10 rows=1 loops=1) Index Cond: (lower((emailaddress)::text) = 'mallah_rajesh@yahoo.com'::text) Total runtime: 2859.34 msec (25 rows) 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; > > > --------------060402080107040008000108 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit
But the new version at lease works on 7.3 instead of putting
it in an infinite loop.


rt3=# explain analyze SELECT  * from tickets where id in (  SELECT groups.instance FROM groups
rt3(#  JOIN principals ON (groups.id = principals.objectid) JOIN cachedgroupmembers ON
rt3(# (principals.id = cachedgroupmembers.groupid) JOIN users ON (cachedgroupmembers.memberid = users.id)
rt3(# WHERE lower(users.emailaddress) = 'mallah_rajesh@yahoo.com' AND groups.domain = 'RT::Ticket-Role'
rt3(# AND groups.type   = 'Requestor' AND principals.principaltype = 'Group' ) AND type = 'ticket' AND
rt3-# effectiveid = tickets.id AND (status = 'new' OR status = 'open') ORDER BY priority DESC LIMIT 10;


                                                                                       QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=88073404.73..88073404.73 rows=1 width=163) (actual time=2859.05..2859.07 rows=4 loops=1)
   ->  Sort  (cost=88073404.73..88073404.73 rows=1 width=163) (actual time=2859.05..2859.05 rows=4 loops=1)
         Sort Key: priority
         ->  Seq Scan on tickets  (cost=0.00..88073404.72 rows=1 width=163) (actual time=2525.48..2858.95 rows=4 loops=1)
               Filter: (("type" = 'ticket'::character varying) AND (effectiveid = id) AND ((status = 'new'::character varying) OR (status = 'open'::character varying)) AND (subplan))
               SubPlan
                 ->  Materialize  (cost=8443.38..8443.38 rows=66 width=32) (actual time=0.79..0.81 rows=14 loops=3209)
                       ->  Hash Join  (cost=3698.35..8443.38 rows=66 width=32) (actual time=1720.53..2525.07 rows=14 loops=1)
                             Hash Cond: ("outer".memberid = "inner".id)
                             ->  Hash Join  (cost=3329.03..7973.87 rows=13247 width=28) (actual time=1225.83..2458.48 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.06..638.91 rows=204551 loops=1)
                                   ->  Hash  (cost=3315.71..3315.71 rows=5325 width=20) (actual time=1225.51..1225.51 rows=0 loops=1)
                                         ->  Hash Join  (cost=1355.70..3315.71 rows=5325 width=20) (actual time=529.02..1191.94 rows=10431 loops=1)
                                               Hash Cond: ("outer".objectid = "inner".id)
                                               ->  Seq Scan on principals  (cost=0.00..1583.76 rows=61940 width=8) (actual time=0.02..450.42 rows=62097 loops=1)
                                                     Filter: (principaltype = 'Group'::character varying)
                                               ->  Hash  (cost=1338.03..1338.03 rows=7068 width=12) (actual time=528.58..528.58 rows=0 loops=1)
                                                     ->  Index Scan using groups_domain on groups  (cost=0.00..1338.03 rows=7068 width=12) (actual time=0.18..498.04 rows=10431 loops=1)
                                                           Index Cond: ("domain" = 'RT::Ticket-Role'::character varying)
                                                           Filter: ("type" = 'Requestor'::character varying)
                             ->  Hash  (cost=369.08..369.08 rows=101 width=4) (actual time=0.10..0.10 rows=0 loops=1)
                                   ->  Index Scan using users_emailaddress on users  (cost=0.00..369.08 rows=101 width=4) (actual time=0.09..0.10 rows=1 loops=1)
                                         Index Cond: (lower((emailaddress)::text) = 'mallah_rajesh@yahoo.com'::text)
 Total runtime: 2859.34 msec
(25 rows)





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;

  

--------------060402080107040008000108--