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

2003-10-31 Thread Rajesh Kumar Mallah
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

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

2003-10-30 Thread Greg Stark
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 wa

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

2003-10-30 Thread Rajesh Kumar Mallah
But the new version at lease works on 7.3 instead of putting it in an infinite loop. rt3=# explain analyze SELECT  * from tickets where id in (  SELECT groups.instance FROM groups rt3(#  JOIN principals ON (groups.id = principals.objectid) JOIN cachedgroupmembers ON rt3(# (principals.id = ca

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

2003-10-30 Thread Rajesh Kumar Mallah
The g in group had to be uppercased, the query produced the same results but performance was worse  for the IN version .  2367 ms vs 600 ms rt3=# explain analyze SELECT * from tickets where id in ( SELECT groups.instance FROM groups  JOIN principals ON (groups.id = principals.objectid) JOIN

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

2003-10-30 Thread Greg Stark
Rajesh Kumar Mallah <[EMAIL PROTECTED]> 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

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

2003-10-30 Thread Rajesh Kumar Mallah
explain analyze of original Query: rt3=# explain analyze 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 ( P

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

2003-10-30 Thread Greg Stark
Rajesh Kumar Mallah <[EMAIL PROTECTED]> 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 C

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

2003-10-30 Thread Greg Stark
Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes: > -> Seq Scan on tickets main (cost=0.00..465.62 rows=1 width=164) > Filter: ((effectiveid = id) AND (("type")::text = 'ticket'::text) AND > (((status)::text = 'new'::text) OR ((status)::text = 'open'::text))) This query has to read through

Re: [PERFORM] Query puts 7.3.4 on endless loop but 7.4beta5 is fine. [ with better indenting ]

2003-10-30 Thread Tom Lane
Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes: > 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)

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

2003-10-30 Thread Rajesh Kumar Mallah
Tom Lane wrote: Rajesh Kumar Mallah <[EMAIL PROTECTED]> writes: 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 = Princi

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

2003-10-30 Thread Rajesh Kumar Mallah
Hi , Here are the Execution Plans , Sorry for the delay . Regds Mallah On PostgreSQL  7.3.4 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.Object

Re: [PERFORM] Query puts 7.3.4 on endless loop but 7.4beta5 is fine. [ with better indenting ]

2003-10-30 Thread Christopher Browne
In the last exciting episode, [EMAIL PROTECTED] wrote: >> [EMAIL PROTECTED] (Rajesh Kumar Mallah) wrote: >>> Can you please have a Look at the below and suggest why it >>> apparently puts 7.3.4 on an infinite loop . the CPU utilisation of the backend >>> running it >>> approches 99%. >> >> What wo

Re: [PERFORM] Query puts 7.3.4 on endless loop but 7.4beta5 is fine. [ with better indenting ]

2003-10-30 Thread mallah
> [EMAIL PROTECTED] (Rajesh Kumar Mallah) wrote: >> Can you please have a Look at the below and suggest why it >> apparently puts 7.3.4 on an infinite loop . the CPU utilisation of the backend >> running it >> approches 99%. > > What would be useful, for this case, would be to provide the query

Re: [PERFORM] Query puts 7.3.4 on endless loop but 7.4beta5 is fine. [ with better indenting ]

2003-10-30 Thread Christopher Browne
[EMAIL PROTECTED] (Rajesh Kumar Mallah) wrote: > Can you please have a Look at the below and suggest why it > apparently puts 7.3.4 on an infinite loop . the CPU utilisation of > the backend running it approches 99%. What would be useful, for this case, would be to provide the query plan, perhaps

Re: [PERFORM] Query puts 7.3.4 on endless loop but 7.4beta5 is fine. [ with better indenting ]

2003-10-30 Thread Rajesh Kumar Mallah
Dear Tom, Can you please have a Look at the below and suggest why it apparently puts 7.3.4 on an infinite loop . the CPU utilisation of the backend running it approches 99%. Query: I have tried my best to indent it :) SELECT DISTINCT main.* FROM ( ( ( ( Tickets