pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: Greg Stark <gsstark@mit.edu>
Cc: Christopher Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org, Jesse Vincent <jesse@bestpractical.com>
Subject: Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
Date: Sat, 01 Nov 2003 03:50:59 -0500
Message-ID: <3FA373F3.9040103@trade-india.com> (raw)
In-Reply-To: <87ad7icsxw.fsf@stark.dyndns.tv>
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>
	<m33cdakhv7.fsf@wolfe.cbbrowne.com>
	<13042.61.16.154.82.1067523120.squirrel@mail.trade-india.com>
	<m3smlaiww8.fsf@wolfe.cbbrowne.com>
	<3FA323C2.2060302@trade-india.com>
	<87r80ud0nw.fsf@stark.dyndns.tv>
	<3FA34827.1030705@trade-india.com>
	<87ad7icsxw.fsf@stark.dyndns.tv>


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

view thread (16+ messages)

Message-ID: <3FA373F3.9040103@trade-india.com>
Permalink:  ../3FA373F3.9040103@trade-india.com/
Also on:    postgresql.org/message-id/3FA373F3.9040103@trade-india.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: mallah@trade-india.com, gsstark@mit.edu, cbbrowne@acm.org, jesse@bestpractical.com
  Subject: Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
  In-Reply-To: <3FA373F3.9040103@trade-india.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox