Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. - Mailing list pgsql-performance

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;

--
greg