pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Stark <gsstark@mit.edu>
To: Rajesh Kumar Mallah <mallah@trade-india.com>
Cc: Christopher Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
Date: 30 Oct 2003 13:45:55 -0500
Message-ID: <87r80ud0nw.fsf@stark.dyndns.tv> (raw)
In-Reply-To: <3FA323C2.2060302@trade-india.com>
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>


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;
       






-- 
greg




view thread (16+ messages)  latest in thread

Message-ID: <87r80ud0nw.fsf@stark.dyndns.tv>
Permalink:  ../87r80ud0nw.fsf@stark.dyndns.tv/
Also on:    postgresql.org/message-id/87r80ud0nw.fsf@stark.dyndns.tv

 · 

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: gsstark@mit.edu, mallah@trade-india.com, cbbrowne@acm.org
  Subject: Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
  In-Reply-To: <87r80ud0nw.fsf@stark.dyndns.tv>

* 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