[ PROBLEM SOLVED ] Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.

Well Sorry everyone ,

The problem was tracked down to a silly
datatype mismatch between two join columns
in table Groups(instance) and Tickets(id)
(int vs varchar )

7.4b5 is automatically taking care of this
mismatch hence it was getting executed there.

But , The problem is will this behaviour not
allow to go such mistakes unnoticed?

Regards
Mallah.

On Friday 31 Oct 2003 4:08 am, Greg Stark wrote:
> 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;