Skip site navigation (1) Skip section navigation (2)

Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Rajesh Kumar Mallah <mallah(at)trade-india(dot)com>
Cc: Christopher Browne <cbbrowne(at)acm(dot)org>,pgsql-performance(at)postgresql(dot)org
Subject: Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
Date: 2003-10-30 18:45:55
Message-ID: 87r80ud0nw.fsf@stark.dyndns.tv (view raw or flat)
Thread:
Lists: pgsql-performance
Rajesh Kumar Mallah <mallah(at)trade-india(dot)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(at)yahoo(dot)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(at)yahoo(dot)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;
       






-- 
greg


In response to

Responses

pgsql-performance by date

Next:From: alexandre :: aldeia digitalDate: 2003-10-30 19:49:08
Subject: Pg+Linux swap use
Previous:From: Bruce MomjianDate: 2003-10-30 18:34:24
Subject: Re: vacuum locking

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group