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
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
(
(
(
(
[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
[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,
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 would be useful,
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 =
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 =
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)
)
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 every
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
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 (
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
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 =
13 matches
Mail list logo
