[ 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 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) = '[EMAIL PROTECTED]'
   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;


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

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 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 ( Principals_2.id = 
CachedGroupMembers_3.GroupId)
   )  JOIN
   Users as Users_4  ON ( CachedGroupMembers_3.MemberId = Users_4.id)
)   WHERE

( (main.EffectiveId = main.id) ) AND
( (main.Type = 'ticket') ) AND

(
   (
  (
(Users_4.EmailAddress = '[EMAIL PROTECTED]')AND
(Groups_1.Domain = 'RT::Ticket-Role')AND
(Groups_1.Type = 'Requestor')AND
(Principals_2.PrincipalType = 'Group')
  )
   )
   AND
   (
  (main.Status = 'new')OR(main.Status = 'open')
   )
)  ORDER BY main.Priority DESC LIMIT 10


On Thursday 30 Oct 2003 2:17 am, [EMAIL PROTECTED] wrote:
 ok this time it constructs a query which puts 7.3.4 on a infinite loop
 but 7.4b5 is able to come out of it.

 since it may be of interest to the pgsql people i am Ccing it to the
 pgsql-performance list i hope its ok.



 Pgsql 7.3.4 on an endless loop:

 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 ( Principals_2.id = CachedGroupMembers_3.GroupId))
  JOIN Users as Users_4  ON ( CachedGroupMembers_3.MemberId = Users_4.id))  
 WHERE ((main.EffectiveId = main.id)) AND ((main.Type = 'ticket')) AND ( ( 
 ( (Users_4.EmailAddress = '[EMAIL PROTECTED]')AND(Groups_1.Domain =
 'RT::Ticket-Role')AND(Groups_1.Type =
 'Requestor')AND(Principals_2.PrincipalType = 'Group') )  ) AND (
 (main.Status = 'new')OR(main.Status = 'open') ) )  ORDER BY main.Priority
 DESC LIMIT 10


 But 7.4 beta5  seems to be able to handle it:

 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 ( Principals_2.id = CachedGroupMembers_3.GroupId))
  JOIN Users as Users_4  ON ( CachedGroupMembers_3.MemberId = Users_4.id))  
 WHERE ((main.EffectiveId = main.id)) AND ((main.Type = 'ticket')) AND ( ( 
 ( (Users_4.EmailAddress = '[EMAIL PROTECTED]')AND(Groups_1.Domain =
 'RT::Ticket-Role')AND(Groups_1.Type =
 'Requestor')AND(Principals_2.PrincipalType = 'Group') )  ) AND (
 (main.Status = 'new')OR(main.Status = 'open') ) )  ORDER BY main.Priority
 DESC LIMIT 10;  id  | effectiveid | queue |  type  | issuestatement |
 resolution | owner | subject

 | initialpriority | finalpriority | priority | timeestimated |
 | timeworked | status | timeleft
   |
   | told |   starts|   started   | due
   | |  resolved   |

   lastupdatedby | lastupdated | creator |   created   |
 disabled--+-+---+++
+---+-+-+---+---
---+---+++--+--+
-+-+-+-+
---+-+-+-+--
   13 |  13 |23 | ticket |  0 |  0 | 31122 |
 General Discussion

  |   0 | 0 |0 | 0 |
  |  0 | new|0
  |
|  | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 | 1970-01-01 00:00:00
|  | | 1970-01-01 00:00:00
|
| 31122 | 2001-11-22 04:19:10 |   31122 | 2001-11-22 04:19:07 | 
|   0 6018 |6018 |19 | ticket |  0 |
|  0 |10 | EYP Prospective

  Clients |   0 | 0 |0 | 0 |
  0 | new| 0 |  | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 |
 2002-09-11 18:29:37 | 1970-01-01 00:00:00 | 31122 | 2002-09-11
 18:29:39 |   31122 | 2002-09-11 18:29:37 |0 6336 |6336 |   
 19 | ticket |  0 |  0 |10 | EYP Prospective Clients
 |   0 | 0 |0 | 0 |  0 |
 new| 0 |  | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 | 2002-09-20
 12:31:02 | 1970-01-01 00:00:00 | 31122 | 2002-09-20 12:31:09 |  
 31122 | 2002-09-20 12:31:02 |0 6341 |6341 |19 | ticket
 |  0 |  0 |10 | IP Prospective Clients  |  
 0 | 0 |0 | 0 |  0 | new| 0
 |  | 1970-01-01

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 via

  EXPLAIN [Big Long Query].

The difference between that EXPLAIN and what you get on 7.4 might be
quite interesting.

I would think it quite unlikely that it is truly an infinite loop;
it is rather more likely that the plan winds up being pretty bad and
doing something [a bunch of nested loops, maybe?] that run longer than
your patience will permit.
-- 
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','acm.org').
http://www3.sympatico.ca/cbbrowne/lsf.html
Rules of the Evil Overlord #81. If I am fighting with the hero atop a
moving platform, have disarmed him, and am about to finish him off and
he glances behind  me and drops flat, I too will  drop flat instead of
quizzically turning around to find out what he saw.
http://www.eviloverlord.com/

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

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 plan, perhaps via

  EXPLAIN [Big Long Query].

 The difference between that EXPLAIN and what you get on 7.4 might be quite 
 interesting.

 I would think it quite unlikely that it is truly an infinite loop; it is rather 
 more likely
 that the plan winds up being pretty bad and doing something [a bunch of nested 
 loops, maybe?]
 that run longer than your patience will permit.

:-)   ok i will leave it running and try to get it.

Regds
Mallah.





 --
 wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','acm.org').
 http://www3.sympatico.ca/cbbrowne/lsf.html
 Rules of the Evil Overlord #81. If I am fighting with the hero atop a moving 
 platform, have
 disarmed him, and am about to finish him off and he glances behind  me and drops 
 flat, I too
 will  drop flat instead of quizzically turning around to find out what he saw.
 http://www.eviloverlord.com/

 ---(end of broadcast)--- TIP 6: Have 
 you
 searched our list archives?

   http://archives.postgresql.org



-
Over 1,00,000 exporters are waiting for your order! Click below to get
in touch with leading Indian exporters listed in the premier
trade directory Exporters Yellow Pages.
http://www.trade-india.com/dyn/gdh/eyp/



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

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 would be useful, for this case, would be to provide the query plan, perhaps via

  EXPLAIN [Big Long Query].

 The difference between that EXPLAIN and what you get on 7.4 might be quite 
 interesting.

 I would think it quite unlikely that it is truly an infinite loop; it is rather 
 more likely
 that the plan winds up being pretty bad and doing something [a bunch of nested 
 loops, maybe?]
 that run longer than your patience will permit.

 :-)   ok i will leave it running and try to get it.

No, if you just do EXPLAIN (and not EXPLAIN ANALYZE), that returns
without executing the query.

If the query runs for a really long time, then we _know_ that there is
something troublesome.  EXPLAIN (no ANALYZE) should provide some
insight without having anything run for a long time.

If EXPLAIN [big long query] turns into what you are terming an
infinite loop, then you have a quite different problem, and it would
be very useful to know that.
-- 
cbbrowne,@,ntlug.org
http://www3.sympatico.ca/cbbrowne/oses.html
This is Linux country.  On a quiet night, you can hear NT re-boot.

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

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.ObjectId)) JOIN CachedGroupMembers as CachedGroupMembers_3  
ON ( Principals_2.id = CachedGroupMembers_3.GroupId))  JOIN Users as Users_4  ON ( CachedGroupMembers_3.MemberId = Users_4.id))   
WHERE ((main.EffectiveId = main.id)) AND ((main.Type = 'ticket')) AND ( (  ( (Users_4.EmailAddress = '[EMAIL PROTECTED]')
AND(Groups_1.Domain = 'RT::Ticket-Role')AND(Groups_1.Type = 'Requestor')AND(Principals_2.PrincipalType = 'Group') )  ) 
AND ( (main.Status = 'new')OR(main.Status = 'open') ) )  ORDER BY main.Priority DESC LIMIT 10;



Limit  (cost=2044.52..2044.58 rows=1 width=195)
   -  Unique  (cost=2044.52..2044.58 rows=1 width=195)
 -  Sort  (cost=2044.52..2044.52 rows=1 width=195)
   Sort Key: main.priority, main.id, main.effectiveid, main.queue, main."type", main.issuestatement, main.resolution, main."owner", main.subject, main.initialpriority, main.finalpriority, main.timeestimated, main.timeworked, main.status, main.timeleft, main.told, main.starts, main.started, main.due, main.resolved, main.lastupdatedby, main.lastupdated, main.creator, main.created, main.disabled
   -  Hash Join  (cost=3.98..2044.51 rows=1 width=195)
 Hash Cond: ("outer".memberid = "inner".id)
 -  Nested Loop  (cost=0.00..2040.51 rows=2 width=191)
   -  Nested Loop  (cost=0.00..1914.41 rows=1 width=183)
 -  Nested Loop  (cost=0.00..1909.67 rows=1 width=175)
   Join Filter: (("outer".id)::text = ("inner".instance)::text)
   -  Seq Scan on tickets main  (cost=0.00..465.62 rows=1 width=163)
 Filter: ((effectiveid = id) AND ("type" = 'ticket'::character varying) AND ((status = 'new'::character varying) OR (status = 'open'::character varying)))
   -  Index Scan using groups_domain on groups groups_1  (cost=0.00..1338.03 rows=7068 width=12)
 Index Cond: ("domain" = 'RT::Ticket-Role'::character varying)
 Filter: ("type" = 'Requestor'::character varying)
 -  Index Scan using principals2 on principals principals_2  (cost=0.00..4.73 rows=1 width=8)
   Index Cond: ("outer".id = principals_2.objectid)
   Filter: (principaltype = 'Group'::character varying)
   -  Index Scan using cachedgroupmembers3 on cachedgroupmembers cachedgroupmembers_3  (cost=0.00..125.54 rows=45 width=8)
 Index Cond: ("outer".id = cachedgroupmembers_3.groupid)
 -  Hash  (cost=3.98..3.98 rows=1 width=4)
   -  Index Scan using users4 on users users_4  (cost=0.00..3.98 rows=1 width=4)
 Index Cond: (emailaddress = '[EMAIL PROTECTED]'::character varying)
(23 rows)


On PostgreSQL 7.4 beta 5

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 CachedGroupMembers as CachedGroupMembers_3  
ON ( Principals_2.id = CachedGroupMembers_3.GroupId))  JOIN Users as Users_4  ON ( CachedGroupMembers_3.MemberId = Users_4.id))   
WHERE ((main.EffectiveId = main.id)) AND ((main.Type = 'ticket')) AND ( (  ( (Users_4.EmailAddress = '[EMAIL PROTECTED]')
AND(Groups_1.Domain = 'RT::Ticket-Role')AND(Groups_1.Type = 'Requestor')AND(Principals_2.PrincipalType = 'Group') )  ) 
AND ( (main.Status = 'new')OR(main.Status = 'open') ) )  ORDER BY main.Priority DESC LIMIT 10;
   QUERY PLAN
---
 Limit  (cost=582.27..582.34 rows=1 width=164)
   -  Unique  (cost=582.27..582.34 rows=1 width=164)
 -  Sort  (cost=582.27..582.28 rows=1 width=164)
   Sort Key: main.priority, main.id, main.effectiveid, main.queue, main."type", main.issuestatement, main.resolution, main."owner", main.subject, main.initialpriority, main.finalpriority, main.timeestimated, main.timeworked, main.status, main.timeleft, main.told, main.starts, main.started, main.due, main.resolved, main.lastupdatedby, main.lastupdated, main.creator, main.created, main.disabled
   -  Hash Join  (cost=476.18..582.26 rows=1 width=164)

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 = Principals_2.ObjectId)
  ) JOIN
  CachedGroupMembers as CachedGroupMembers_3  ON ( Principals_2.id = CachedGroupMembers_3.GroupId)
   )  JOIN
   Users as Users_4  ON ( CachedGroupMembers_3.MemberId = Users_4.id)
)   WHERE
 ...

  
  
I think the reason for the performance difference is that 7.3 treats
JOIN syntax as forcing a particular join order, while 7.4 doesn't.


Just out of curiosity , how does 7.4 determine the optimal Join Order?
is it GEQO in case of 7.4 although i did not enable it explicitly?
Thanks for the reply , I sent the EXPLAINs also just now. 

What i really want is to help improving the Pg specific Component
for DBIx::SearchBuilder. The module is being widely used in 
the mod_perl world and has impact on the performance perception
of PostgreSQL.

  

			regards, tom lane

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)
   ) JOIN
   CachedGroupMembers as CachedGroupMembers_3  ON ( Principals_2.id = 
 CachedGroupMembers_3.GroupId)
)  JOIN
Users as Users_4  ON ( CachedGroupMembers_3.MemberId = Users_4.id)
 )   WHERE
  ...

I think the reason for the performance difference is that 7.3 treats
JOIN syntax as forcing a particular join order, while 7.4 doesn't.

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

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 every ticket in the system and check if the
current user has access to it? It seems like this isn't going to be a terribly
fast query no matter how you slice it.

One thing that might help keep its run-time from growing is a partial index
  WHERE type = 'ticket' and (status = 'new' OR status = 'open')

(I'm not sure what the point of the effectiveid=id clause is)

That at least might help when 99% of your tickets are old closed tickets. But
it will still have to scan through every new and open ticket which on some
systems could be a large number.

-- 
greg


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

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 CachedGroupMembers as CachedGroupMembers_3  ON ( Principals_2.id = 
 CachedGroupMembers_3.GroupId)
 ) JOIN Users as Users_4  ON ( CachedGroupMembers_3.MemberId = Users_4.id)
)
  WHERE ((main.EffectiveId = main.id))
AND ((main.Type = 'ticket'))
AND ((((Users_4.EmailAddress = '[EMAIL PROTECTED]')
   AND (Groups_1.Domain = 'RT::Ticket-Role')
   AND (Groups_1.Type = 'Requestor')
   AND (Principals_2.PrincipalType = 'Group')
  ))
  AND ((main.Status = 'new') OR (main.Status = 'open'))
)
  ORDER BY main.Priority DESC LIMIT 10;

So this query seems to be going the long way around to do the equivalent of an
IN clause. Presumably because as far as I know mysql didn't support IN
subqueries until recently.

Can you do an explain analyze on the above query and the following rewritten
one in 7.4? The analyze is important because it'll give real timing
information. And it's important that it be on 7.4 as there were improvements
in this area specifically in 7.4.

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 users.emailaddress = '[EMAIL PROTECTED]'
  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


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

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 ( Principals_2.id = CachedGroupMembers_3.GroupId)   JOIN Users as Users_4  ON ( CachedGroupMembers_3.MemberId = Users_4.id)WHERE ((main.EffectiveId = main.id)) AND ((main.Type = 'ticket')) AND ( (  ( (lower(Users_4.EmailAddress) = '[EMAIL PROTECTED]')AND(Groups_1.Domain = 'RT::Ticket-Role')AND(Groups_1.Type = 'Requestor')AND(Principals_2.PrincipalType = 'Group') )  ) AND ( (main.Status = 'new')OR(main.Status = 'open') ) )  ORDER BY main.Priority DESC LIMIT 10;
   QUERY PLAN

 Limit  (cost=619.93..620.00 rows=1 width=164) (actual time=994.570..994.683 rows=4 loops=1)
   -  Unique  (cost=619.93..620.00 rows=1 width=164) (actual time=994.565..994.672 rows=4 loops=1)
 -  Sort  (cost=619.93..619.93 rows=1 width=164) (actual time=994.561..994.569 rows=8 loops=1)
   Sort Key: main.priority, main.id, main.effectiveid, main.queue, main."type", main.issuestatement, main.resolution, main."owner", main.subject, main.initialpriority, main.finalpriority, main.timeestimated, main.timeworked, main.status, main.timeleft, main.told, main.starts, main.started, main.due, main.resolved, main.lastupdatedby, main.lastupdated, main.creator, main.created, main.disabled
   -  Nested Loop  (cost=0.00..619.92 rows=1 width=164) (actual time=1.374..993.998 rows=8 loops=1)
 -  Nested Loop  (cost=0.00..610.83 rows=3 width=168) (actual time=0.691..839.633 rows=9617 loops=1)
   -  Nested Loop  (cost=0.00..476.17 rows=1 width=168) (actual time=0.524..616.937 rows=3209 loops=1)
 -  Nested Loop  (cost=0.00..471.54 rows=1 width=168) (actual time=0.376..503.774 rows=3209 loops=1)
   -  Seq Scan on tickets main  (cost=0.00..465.62 rows=1 width=164) (actual time=0.114..60.044 rows=3209 loops=1)
 Filter: ((effectiveid = id) AND (("type")::text = 'ticket'::text) AND (((status)::text = 'new'::text) OR ((status)::text = 'open'::text)))
   -  Index Scan using groups1 on groups groups_1  (cost=0.00..5.90 rows=1 width=12) (actual time=0.111..0.119 rows=1 loops=3209)
 Index Cond: (((groups_1."domain")::text = 'RT::Ticket-Role'::text) AND (("outer".id)::text = (groups_1.instance)::text) AND ((groups_1."type")::text = 'Requestor'::text))
 -  Index Scan using principals2 on principals principals_2  (cost=0.00..4.62 rows=1 width=8) (actual time=0.015..0.018 rows=1 loops=3209)
   Index Cond: ("outer".id = principals_2.objectid)
   Filter: ((principaltype)::text = 'Group'::text)
   -  Index Scan using cachedgroupmembers3 on cachedgroupmembers cachedgroupmembers_3  (cost=0.00..134.06 rows=47 width=8) (actual time=0.015..0.026 rows=3 loops=3209)
 Index Cond: ("outer".id = cachedgroupmembers_3.groupid)
 -  Index Scan using users_pkey on users users_4  (cost=0.00..3.02 rows=1 width=4) (actual time=0.013..0.013 rows=0 loops=9617)
   Index Cond: ("outer".memberid = users_4.id)
   Filter: (lower((emailaddress)::text) = '[EMAIL PROTECTED]'::text)
 Total runtime: 995.326 ms
(21 rows)
rt3=#

999 ms is not that bad but u think it deserves this many ms?


Nopes the query are not Equiv , earlier one returns 4 rows and the
below one none,
can you spot any obvious and resend plz. thats why i did not do an
explain analyze
rt3=# SELECT *
rt3-#   FROM tickets
rt3-#  WHERE id IN (
rt3(#SELECT groups.instance
rt3(#  FROM groups
rt3(#  JOIN principals ON (groups.id = principals.objectid)
rt3(#  JOIN cachedgroupmembers ON (principals.id = cachedgroupmembers.groupid)
rt3(#  JOIN users ON (cachedgroupmembers.memberid = users.id)
rt3(# WHERE users.emailaddress = '[EMAIL PROTECTED]'
rt3(#   AND groups.domain = 'RT::Ticket-Role'
rt3(#   AND

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 cachedgroupmembers ON 
(principals.id = cachedgroupmembers.groupid) JOIN users ON (cachedgroupmembers.memberid = users.id)  
WHERE lower(users.emailaddress) = '[EMAIL PROTECTED]' 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;;

   QUERY PLAN
-
 Limit  (cost=10078.18..10078.19 rows=1 width=164) (actual time=2367.084..2367.096 rows=4 loops=1)
   -  Sort  (cost=10078.18..10078.19 rows=1 width=164) (actual time=2367.078..2367.082 rows=4 loops=1)
 Sort Key: tickets.priority
 -  Hash Join  (cost=10077.65..10078.17 rows=1 width=164) (actual time=2366.870..2367.051 rows=4 loops=1)
   Hash Cond: (("outer".instance)::text = ("inner".id)::text)
   -  HashAggregate  (cost=9612.02..9612.02 rows=69 width=8) (actual time=2303.792..2303.810 rows=7 loops=1)
 -  Hash Join  (cost=4892.97..9611.85 rows=69 width=8) (actual time=1427.260..2303.685 rows=14 loops=1)
   Hash Cond: ("outer".memberid = "inner".id)
   -  Hash Join  (cost=4523.65..9139.45 rows=13651 width=12) (actual time=948.960..2258.529 rows=31123 loops=1)
 Hash Cond: ("outer".groupid = "inner".id)
 -  Seq Scan on cachedgroupmembers  (cost=0.00..3456.51 rows=204551 width=8) (actual time=0.048..365.147 rows=204551 loops=1)
 -  Hash  (cost=4509.93..4509.93 rows=5488 width=12) (actual time=948.843..948.843 rows=0 loops=1)
   -  Hash Join  (cost=1409.91..4509.93 rows=5488 width=12) (actual time=315.722..930.025 rows=10431 loops=1)
 Hash Cond: ("outer".objectid = "inner".id)
 -  Seq Scan on principals  (cost=0.00..1583.76 rows=62625 width=8) (actual time=0.043..251.142 rows=62097 loops=1)
   Filter: ((principaltype)::text = 'Group'::text)
 -  Hash  (cost=1359.90..1359.90 rows=7204 width=12) (actual time=315.458..315.458 rows=0 loops=1)
   -  Index Scan using groups_domain on groups  (cost=0.00..1359.90 rows=7204 width=12) (actual time=0.325..297.403 rows=10431 loops=1)
 Index Cond: (("domain")::text = 'RT::Ticket-Role'::text)
 Filter: (("type")::text = 'Requestor'::text)
   -  Hash  (cost=369.08..369.08 rows=101 width=4) (actual time=0.157..0.157 rows=0 loops=1)
 -  Index Scan using users_emailaddress_lower on users  (cost=0.00..369.08 rows=101 width=4) (actual time=0.139..0.143 rows=1 loops=1)
   Index Cond: (lower((emailaddress)::text) = '[EMAIL PROTECTED]'::text)
   -  Hash  (cost=465.62..465.62 rows=1 width=164) (actual time=62.944..62.944 rows=0 loops=1)
 -  Seq Scan on tickets  (cost=0.00..465.62 rows=1 width=164) (actual time=0.113..52.729 rows=3208 loops=1)
   Filter: ((("type")::text = 'ticket'::text) AND (effectiveid = id) AND (((status)::text = 'new'::text) OR ((status)::text = 'open'::text)))
 Total runtime: 2367.908 ms
(27 rows)



rt3=# explain analyze SELECT DISTINCT main.* FROM Tickets main  JOIN Groups as Groups_1  ON ( main.id = Groups_1.Instance))
rt3(# JOIN Principals as Principals_2  ON ( Groups_1.id = Principals_2.ObjectId)) JOIN CachedGroupMembers as CachedGroupMembers_3
rt3(# ON ( Principals_2.id = CachedGroupMembers_3.GroupId))  JOIN Users as Users_4  ON ( CachedGroupMembers_3.MemberId = Users_4.id))
rt3-# WHERE ((main.EffectiveId = main.id)) AND ((main.Type = 'ticket')) AND ( (  ( (Users_4.EmailAddress = '[EMAIL PROTECTED]')
rt3(# AND(Groups_1.Domain = 'RT::Ticket-Role')AND(Groups_1.Type = 'Requestor')AND(Principals_2.PrincipalType = 'Group') )  )
rt3(# AND ( (main.Status = 'new')OR(main.Status = 'open') ) )  ORDER BY main.Priority DESC LIMIT 10;

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 = cachedgroupmembers.groupid) JOIN users ON
(cachedgroupmembers.memberid = users.id)
rt3(# WHERE lower(users.emailaddress) = '[EMAIL PROTECTED]' AND
groups.domain = 'RT::Ticket-Role'
rt3(# AND groups.type = 'Requestor' AND principals.principaltype =
'Group' ) AND type = 'ticket' AND
rt3-# effectiveid = tickets.id AND (status = 'new' OR status = 'open')
ORDER BY priority DESC LIMIT 10;



QUERY PLAN
-
Limit (cost=88073404.73..88073404.73 rows=1 width=163) (actual
time=2859.05..2859.07 rows=4 loops=1)
 - Sort (cost=88073404.73..88073404.73 rows=1 width=163)
(actual time=2859.05..2859.05 rows=4 loops=1)
 Sort Key: priority
 - Seq Scan on tickets (cost=0.00..88073404.72 rows=1
width=163) (actual time=2525.48..2858.95 rows=4 loops=1)
 Filter: (("type" = 'ticket'::character varying) AND
(effectiveid = id) AND ((status = 'new'::character varying) OR (status
= 'open'::character varying)) AND (subplan))
 SubPlan
 - Materialize (cost=8443.38..8443.38 rows=66
width=32) (actual time=0.79..0.81 rows=14 loops=3209)
 - Hash Join (cost=3698.35..8443.38 rows=66
width=32) (actual time=1720.53..2525.07 rows=14 loops=1)
 Hash Cond: ("outer".memberid = "inner".id)
 - Hash Join (cost=3329.03..7973.87
rows=13247 width=28) (actual time=1225.83..2458.48 rows=31123 loops=1)
 Hash Cond: ("outer".groupid =
"inner".id)
 - Seq Scan on
cachedgroupmembers (cost=0.00..3456.51 rows=204551 width=8) (actual
time=0.06..638.91 rows=204551 loops=1)
 - Hash (cost=3315.71..3315.71
rows=5325 width=20) (actual time=1225.51..1225.51 rows=0 loops=1)
 - Hash Join
(cost=1355.70..3315.71 rows=5325 width=20) (actual time=529.02..1191.94
rows=10431 loops=1)
 Hash Cond:
("outer".objectid = "inner".id)
 - Seq Scan on
principals (cost=0.00..1583.76 rows=61940 width=8) (actual
time=0.02..450.42 rows=62097 loops=1)
 Filter:
(principaltype = 'Group'::character varying)
 - Hash
(cost=1338.03..1338.03 rows=7068 width=12) (actual time=528.58..528.58
rows=0 loops=1)
 - Index Scan
using groups_domain on groups (cost=0.00..1338.03 rows=7068 width=12)
(actual time=0.18..498.04 rows=10431 loops=1)
 Index Cond:
("domain" = 'RT::Ticket-Role'::character varying)
 Filter:
("type" = 'Requestor'::character varying)
 - Hash (cost=369.08..369.08 rows=101
width=4) (actual time=0.10..0.10 rows=0 loops=1)
 - Index Scan using
users_emailaddress on users (cost=0.00..369.08 rows=101 width=4)
(actual time=0.09..0.10 rows=1 loops=1)
 Index Cond:
(lower((emailaddress)::text) = '[EMAIL PROTECTED]'::text)
Total runtime: 2859.34 msec
(25 rows)





Greg Stark wrote:

  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
 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) = '[EMAIL PROTECTED]'
  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;