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

Well, you might want to try the EXISTS version. I'm not sure if it'll be
faster or slower though. In theory it should be the same.

Hum, I didn't realize the principals table was the largest table. But Postgres
knew that so one would expect it to have found a better plan. The IN/EXISTS
handling was recently much improved but perhaps there's still room :)

SELECT *
FROM tickets
WHERE EXISTS (
SELECT 1
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(at)yahoo(dot)com'
AND groups.domain = 'RT::Ticket-Role'
AND groups.type = 'Requestor'
AND principals.principaltype = 'group'
AND groups.instance = tickets.id
)
AND type = 'ticket'
AND effectiveid = tickets.id
AND (status = 'new' OR status = 'open')
ORDER BY priority DESC
LIMIT 10;

--
greg