pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
16+ messages / 5 participants
[nested] [flat]

* Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
@ 2003-10-29 20:47  mallah@trade-india.com
  0 siblings, 1 reply; 16+ messages in thread

From: mallah@trade-india.com @ 2003-10-29 20:47 UTC (permalink / raw)
  To: jesse@fsck.com; +Cc: pgsql-performance


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 = 'mallah_rajesh@yahoo.com')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 = 'mallah_rajesh@yahoo.com')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 00:00:00 | 1970-01-01 00:00:00 | 2002-09-20 14:34:25 | 1970-01-01
 00:00:00 |         31122 | 2002-09-20 14:34:26 |   31122 | 2002-09-20 14:34:25 |        0(4 rows)

Time: 900.930 ms



With The explain analyze below:

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 ( (  (
(Users_4.EmailAddress = 'mallah_rajesh@yahoo.com')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) (actual time=854.302..854.433 rows=4 loops=1)
   ->  Unique  (cost=582.27..582.34 rows=1 width=164) (actual time=854.297..854.418 rows=4 loops=1)
         ->  Sort  (cost=582.27..582.28 rows=1 width=164) (actual time=854.294..854.303 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               ->  Hash Join  (cost=476.18..582.26 rows=1 width=164) (actual time=853.025..854.056
               rows=8 loops=1)                     Hash Cond: ("outer".groupid = "inner".id)
                     ->  Nested Loop  (cost=0.00..105.97 rows=21 width=4) (actual
                     time=0.372..1.073 rows=37 loops=1)                           ->  Index Scan using users4 on users users_4  (cost=0.00..3.99 rows=2
                           width=4) (actual time=0.182..0.188 rows=1 loops=1)                                 Index Cond: ((emailaddress)::text = 'mallah_rajesh@yahoo.com'::text)
                           ->  Index Scan using cachedgroupmembers2 on cachedgroupmembers
                           cachedgroupmembers_3  (cost=0.00..50.81 rows=14 width=8) (actual
                           time=0.165..0.703 rows=37 loops=1)                                 Index Cond: (cachedgroupmembers_3.memberid = "outer".id)
                     ->  Hash  (cost=476.17..476.17 rows=1 width=168) (actual
                     time=852.267..852.267 rows=0 loops=1)                           ->  Nested Loop  (cost=0.00..476.17 rows=1 width=168) (actual
                           time=0.684..842.401 rows=3209 loops=1)                                 ->  Nested Loop  (cost=0.00..471.54 rows=1 width=168) (actual
                                 time=0.571..704.492 rows=3209 loops=1)                                       ->  Seq Scan on tickets main  (cost=0.00..465.62 rows=1
                                       width=164) (actual time=0.212..87.100 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.158..0.168
                                       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.019..0.022 rows=1
                                 loops=3209)                                       Index Cond: ("outer".id = principals_2.objectid)
                                       Filter: ((principaltype)::text = 'Group'::text)
 Total runtime: 855.472 ms
(22 rows)

Time: 895.739 ms
rt3=#









> http://backpan.cpan.org/authors/id/J/JE/JESSE/DBIx-SearchBuilder-0.90.tar.gz
>
>
> On Thu, Oct 30, 2003 at 01:57:31AM +0530, mallah@trade-india.com wrote:
>> >
>> >
>> >
>> > On Thu, Oct 30, 2003 at 01:30:26AM +0530, mallah@trade-india.com wrote:
>> >>
>> >> Dear Jesse,
>> >>
>> >> I really want to add a Pg specific better query builder
>> >> the generic one is messing with postgresql.
>> >
>> > I've removed the CC to ivan, to my knowledge, he has nothing to do with SB these days
>> > anymore.
>> >
>> >
>> >> i think i have to work in :
>> >> DBIx/SearchBuilder/Handle/Pg.pm
>> >>
>> >> i hope u read my recent emails to pgsql-performance list.
>>
>> Yes.
>>
>>
>>
>> >>
>> > And I hope you read my reply. There _is_ a postgres specific query builder. And there was a
>> > bug in 0.90 that caused a possible endless loop. The bugfix disabled some of the
>> > optimization. If you can tell me that 0.90 improves your performance (as it generated more
>> > correct queries for pg) then we can work on just fixing the little bug.
>>
>> where is .90 ? i dont see it in
>>
>> http://www.fsck.com/pub/rt/devel/
>>
>>
>> regds
>> mallah.
>>
>>
>> >
>> >
>> >
>> >> =================================================================================== # this
>> >> code is all hacky and evil. but people desperately want _something_ and I'm # super tired.
>> >> refactoring gratefully appreciated.
>> >> ===================================================================================
>> >>
>> >> sub _BuildJoins {
>> >>     my $self = shift;
>> >>     my $sb   = shift;
>> >>     my %seen_aliases;
>> >>
>> >>     $seen_aliases{'main'} = 1;
>> >>
>> >>     my $join_clause =$sb->{'table'} . " main " ;
>> >>
>> >>     my @keys = ( keys %{ $sb->{'left_joins'} } );
>> >>
>> >>     while ( my $join = shift @keys ) {
>> >>         if ( $seen_aliases{ $sb->{'left_joins'}{$join}{'depends_on'} } ) {
>> >>             $join_clause  = "(" . $join_clause;
>> >>             $join_clause .= $sb->{'left_joins'}{$join}{'alias_string'} . " ON (";
>> >>             $join_clause .=
>> >>               join ( ') AND( ', values %{ $sb->{'left_joins'}{$join}{'criteria'} } );
>> >>             $join_clause .= ")) ";
>> >>
>> >>             $seen_aliases{$join} = 1;
>> >>         }
>> >>         else {
>> >>             push ( @keys, $join );
>> >>         }
>> >>
>> >>     }
>> >>     return (
>> >>                     join ( ", ", ($join_clause, @{ $sb->{'aliases'} }))) ;
>> >>
>> >> }
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> -----------------------------------------
>> >> 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/
>> >>
>> >>
>> >
>> > --
>> > jesse reed vincent -- root@eruditorum.org -- jesse@fsck.com
>> > 70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90
>> >
>> > . . . when not in doubt, get in doubt.  -- Old Discordian Proveb
>>
>>
>> -----------------------------------------
>> 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/
>>
>>
>
> --
> jesse reed vincent -- root@eruditorum.org -- jesse@fsck.com
> 70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90
>
> . . . when not in doubt, get in doubt.  -- Old Discordian Proveb


-----------------------------------------
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/





^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. [ with better indenting ]
@ 2003-10-30 11:32  Rajesh Kumar Mallah <mallah@trade-india.com>
  parent: mallah@trade-india.com
  0 siblings, 2 replies; 16+ messages in thread

From: Rajesh Kumar Mallah @ 2003-10-30 11:32 UTC (permalink / raw)
  To: pgsql-performance


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 = 'mallah_rajesh@yahoo.com')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, mallah@trade-india.com 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 = 'mallah_rajesh@yahoo.com')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 = 'mallah_rajesh@yahoo.com')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 00:00:00 | 1970-01-01 00:00:00 | 2002-09-20 14:34:25 |
> 1970-01-01 00:00:00 |         31122 | 2002-09-20 14:34:26 |   31122 |
> 2002-09-20 14:34:25 |        0(4 rows)
>
> Time: 900.930 ms
>
>
>
> With The explain analyze below:
>
> 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 ( (  ( (Users_4.EmailAddress =
> 'mallah_rajesh@yahoo.com')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) (actual time=854.302..854.433 rows=4 loops=1) ->  Unique 
> (cost=582.27..582.34 rows=1 width=164) (actual time=854.297..854.418 rows=4
> loops=1) ->  Sort  (cost=582.27..582.28 rows=1 width=164) (actual
> time=854.294..854.303 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              
> ->  Hash Join  (cost=476.18..582.26 rows=1 width=164) (actual
> time=853.025..854.056 rows=8 loops=1)                     Hash Cond:
> ("outer".groupid = "inner".id) ->  Nested Loop  (cost=0.00..105.97 rows=21
> width=4) (actual time=0.372..1.073 rows=37 loops=1)                        
>   ->  Index Scan using users4 on users users_4  (cost=0.00..3.99 rows=2
> width=4) (actual time=0.182..0.188 rows=1 loops=1)                         
>        Index Cond: ((emailaddress)::text = 'mallah_rajesh@yahoo.com'::text)
> ->  Index Scan using cachedgroupmembers2 on cachedgroupmembers
> cachedgroupmembers_3  (cost=0.00..50.81 rows=14 width=8) (actual
> time=0.165..0.703 rows=37 loops=1)                                 Index
> Cond: (cachedgroupmembers_3.memberid = "outer".id) ->  Hash 
> (cost=476.17..476.17 rows=1 width=168) (actual time=852.267..852.267 rows=0
> loops=1)                           ->  Nested Loop  (cost=0.00..476.17
> rows=1 width=168) (actual time=0.684..842.401 rows=3209 loops=1)           
>                      ->  Nested Loop  (cost=0.00..471.54 rows=1 width=168)
> (actual time=0.571..704.492 rows=3209 loops=1)                             
>          ->  Seq Scan on tickets main  (cost=0.00..465.62 rows=1 width=164)
> (actual time=0.212..87.100 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.158..0.168 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.019..0.022 rows=1
> loops=3209)                                       Index Cond: ("outer".id =
> principals_2.objectid) Filter: ((principaltype)::text = 'Group'::text)
> Total runtime: 855.472 ms
> (22 rows)
>
> Time: 895.739 ms
> rt3=#
>
> > http://backpan.cpan.org/authors/id/J/JE/JESSE/DBIx-SearchBuilder-0.90.tar
> >.gz
> >
> > On Thu, Oct 30, 2003 at 01:57:31AM +0530, mallah@trade-india.com wrote:
> >> > On Thu, Oct 30, 2003 at 01:30:26AM +0530, mallah@trade-india.com wrote:
> >> >> Dear Jesse,
> >> >>
> >> >> I really want to add a Pg specific better query builder
> >> >> the generic one is messing with postgresql.
> >> >
> >> > I've removed the CC to ivan, to my knowledge, he has nothing to do
> >> > with SB these days anymore.
> >> >
> >> >> i think i have to work in :
> >> >> DBIx/SearchBuilder/Handle/Pg.pm
> >> >>
> >> >> i hope u read my recent emails to pgsql-performance list.
> >>
> >> Yes.
> >>
> >> > And I hope you read my reply. There _is_ a postgres specific query
> >> > builder. And there was a bug in 0.90 that caused a possible endless
> >> > loop. The bugfix disabled some of the optimization. If you can tell me
> >> > that 0.90 improves your performance (as it generated more correct
> >> > queries for pg) then we can work on just fixing the little bug.
> >>
> >> where is .90 ? i dont see it in
> >>
> >> http://www.fsck.com/pub/rt/devel/
> >>
> >>
> >> regds
> >> mallah.
> >>
> >> >> =====================================================================
> >> >>============== # this code is all hacky and evil. but people
> >> >> desperately want _something_ and I'm # super tired. refactoring
> >> >> gratefully appreciated.
> >> >> =====================================================================
> >> >>==============
> >> >>
> >> >> sub _BuildJoins {
> >> >>     my $self = shift;
> >> >>     my $sb   = shift;
> >> >>     my %seen_aliases;
> >> >>
> >> >>     $seen_aliases{'main'} = 1;
> >> >>
> >> >>     my $join_clause =$sb->{'table'} . " main " ;
> >> >>
> >> >>     my @keys = ( keys %{ $sb->{'left_joins'} } );
> >> >>
> >> >>     while ( my $join = shift @keys ) {
> >> >>         if ( $seen_aliases{ $sb->{'left_joins'}{$join}{'depends_on'}
> >> >> } ) { $join_clause  = "(" . $join_clause;
> >> >>             $join_clause .=
> >> >> $sb->{'left_joins'}{$join}{'alias_string'} . " ON ("; $join_clause .=
> >> >>               join ( ') AND( ', values %{
> >> >> $sb->{'left_joins'}{$join}{'criteria'} } ); $join_clause .= ")) ";
> >> >>
> >> >>             $seen_aliases{$join} = 1;
> >> >>         }
> >> >>         else {
> >> >>             push ( @keys, $join );
> >> >>         }
> >> >>
> >> >>     }
> >> >>     return (
> >> >>                     join ( ", ", ($join_clause, @{ $sb->{'aliases'}
> >> >> }))) ;
> >> >>
> >> >> }
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> -----------------------------------------
> >> >> 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/
> >> >
> >> > --
> >> > jesse reed vincent -- root@eruditorum.org -- jesse@fsck.com
> >> > 70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90
> >> >
> >> > . . . when not in doubt, get in doubt.  -- Old Discordian Proveb
> >>
> >> -----------------------------------------
> >> 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/
> >
> > --
> > jesse reed vincent -- root@eruditorum.org -- jesse@fsck.com
> > 70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90
> >
> > . . . when not in doubt, get in doubt.  -- Old Discordian Proveb
>
> -----------------------------------------
> 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 8: explain analyze is your friend




^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. [ with better indenting ]
@ 2003-10-30 12:52  Christopher Browne <cbbrowne@acm.org>
  parent: Rajesh Kumar Mallah <mallah@trade-india.com>
  1 sibling, 1 reply; 16+ messages in thread

From: Christopher Browne @ 2003-10-30 12:52 UTC (permalink / raw)
  To: pgsql-performance

mallah@trade-india.com (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/;



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. [ with better indenting ]
@ 2003-10-30 14:12  mallah@trade-india.com
  parent: Christopher Browne <cbbrowne@acm.org>
  0 siblings, 1 reply; 16+ messages in thread

From: mallah@trade-india.com @ 2003-10-30 14:12 UTC (permalink / raw)
  To: cbbrowne@acm.org; +Cc: pgsql-performance




> mallah@trade-india.com (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/





^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. [ with better indenting ]
@ 2003-10-30 15:10  Christopher Browne <cbbrowne@acm.org>
  parent: mallah@trade-india.com
  0 siblings, 1 reply; 16+ messages in thread

From: Christopher Browne @ 2003-10-30 15:10 UTC (permalink / raw)
  To: pgsql-performance

In the last exciting episode, mallah@trade-india.com wrote:
>> mallah@trade-india.com (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.



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. [ with better indenting ]
@ 2003-10-30 16:11  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Rajesh Kumar Mallah <mallah@trade-india.com>
  1 sibling, 1 reply; 16+ messages in thread

From: Tom Lane @ 2003-10-30 16:11 UTC (permalink / raw)
  To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-performance

Rajesh Kumar Mallah <mallah@trade-india.com> 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



^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
@ 2003-10-30 18:18  Greg Stark <gsstark@mit.edu>
  parent: Rajesh Kumar Mallah <mallah@trade-india.com>
  1 sibling, 0 replies; 16+ messages in thread

From: Greg Stark @ 2003-10-30 18:18 UTC (permalink / raw)
  To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: Christopher Browne <cbbrowne@acm.org>; pgsql-performance

Rajesh Kumar Mallah <mallah@trade-india.com> 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




^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
@ 2003-10-30 18:45  Greg Stark <gsstark@mit.edu>
  parent: Rajesh Kumar Mallah <mallah@trade-india.com>
  1 sibling, 1 reply; 16+ messages in thread

From: Greg Stark @ 2003-10-30 18:45 UTC (permalink / raw)
  To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: Christopher Browne <cbbrowne@acm.org>; pgsql-performance


Rajesh Kumar Mallah <mallah@trade-india.com> 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 = 'mallah_rajesh@yahoo.com')
>           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 = 'mallah_rajesh@yahoo.com'
          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




^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
@ 2003-10-30 21:32  Greg Stark <gsstark@mit.edu>
  parent: Rajesh Kumar Mallah <mallah@trade-india.com>
  1 sibling, 2 replies; 16+ messages in thread

From: Greg Stark @ 2003-10-30 21:32 UTC (permalink / raw)
  To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: Greg Stark <gsstark@mit.edu>; Christopher Browne <cbbrowne@acm.org>; pgsql-performance@postgresql.org, Jesse Vincent <jesse@bestpractical.com>

Rajesh Kumar Mallah <mallah@trade-india.com> 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) = 'mallah_rajesh@yahoo.com'
          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




^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
@ 2003-10-30 22:38  Greg Stark <gsstark@mit.edu>
  parent: Rajesh Kumar Mallah <mallah@trade-india.com>
  1 sibling, 1 reply; 16+ messages in thread

From: Greg Stark @ 2003-10-30 22:38 UTC (permalink / raw)
  To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: Greg Stark <gsstark@mit.edu>; Christopher Browne <cbbrowne@acm.org>; pgsql-performance@postgresql.org, Jesse Vincent <jesse@bestpractical.com>


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@yahoo.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




^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
@ 2003-11-01 03:08  Rajesh Kumar Mallah <mallah@trade-india.com>
  parent: Christopher Browne <cbbrowne@acm.org>
  0 siblings, 2 replies; 16+ messages in thread

From: Rajesh Kumar Mallah @ 2003-11-01 03:08 UTC (permalink / raw)
  To: Christopher Browne <cbbrowne@acm.org>; +Cc: pgsql-performance


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 = 'mallah_rajesh@yahoo.com')
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 = 'mallah_rajesh@yahoo.com'::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 = 'mallah_rajesh@yahoo.com')
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)
                     Hash Cond: ("outer".groupid = "inner".id)
                     ->  Nested Loop  (cost=0.00..105.97 rows=21 width=4)
                           ->  Index Scan using users4 on users users_4  (cost=0.00..3.99 rows=2 width=4)
                                 Index Cond: ((emailaddress)::text = 'mallah_rajesh@yahoo.com'::text)
                           ->  Index Scan using cachedgroupmembers2 on cachedgroupmembers cachedgroupmembers_3  (cost=0.00..50.81 rows=14 width=8)
                                 Index Cond: (cachedgroupmembers_3.memberid = "outer".id)
                     ->  Hash  (cost=476.17..476.17 rows=1 width=168)
                           ->  Nested Loop  (cost=0.00..476.17 rows=1 width=168)
                                 ->  Nested Loop  (cost=0.00..471.54 rows=1 width=168)
                                       ->  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)))
                                       ->  Index Scan using groups1 on groups groups_1  (cost=0.00..5.90 rows=1 width=12)
                                             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)
                                       Index Cond: ("outer".id = principals_2.objectid)
                                       Filter: ((principaltype)::text = 'Group'::text)
(21 rows)

rt3=#



Christopher Browne wrote:

>In the last exciting episode, mallah@trade-india.com wrote:
>  
>
>>>mallah@trade-india.com (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.
>  
>

^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
@ 2003-11-01 03:19  Rajesh Kumar Mallah <mallah@trade-india.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 16+ messages in thread

From: Rajesh Kumar Mallah @ 2003-11-01 03:19 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Tom Lane wrote:

>Rajesh Kumar Mallah <mallah@trade-india.com> 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
>  
>

^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
@ 2003-11-01 05:44  Rajesh Kumar Mallah <mallah@trade-india.com>
  parent: Greg Stark <gsstark@mit.edu>
  0 siblings, 2 replies; 16+ messages in thread

From: Rajesh Kumar Mallah @ 2003-11-01 05:44 UTC (permalink / raw)
  To: Greg Stark <gsstark@mit.edu>; +Cc: Christopher Browne <cbbrowne@acm.org>; pgsql-performance@postgresql.org, Jesse Vincent <jesse@bestpractical.com>



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) = 'mallah_rajesh@yahoo.com')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) = 'mallah_rajesh@yahoo.com'::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 = 'mallah_rajesh@yahoo.com'
rt3(#           AND groups.domain = 'RT::Ticket-Role'
rt3(#           AND groups.type   = 'Requestor'
rt3(#           AND principals.principaltype = 'group'
rt3(#   )
rt3-#   AND type = 'ticket'
rt3-#   AND effectiveid = tickets.id
rt3-#   AND (status = 'new' OR status = 'open')
rt3-# ORDER BY priority DESC
rt3-# 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
----+-------------+-------+------+----------------+------------+-------+---------+-----------------+---------------+----------+---------------+------------+--------+----------+------+--------+---------+-----+----------+---------------+-------------+---------+---------+----------
(0 rows)

Time: 2670.85 ms
rt3=#



Well it may be of interest to write the query in best possible way
but i am not sure if it really helps the RT application becoz i do
not know whether DBIx::SearchBuilder would currently allow
auto generation of such arbitrary SQLs.

Regds
Mallah.




Greg Stark wrote:

>Rajesh Kumar Mallah <mallah@trade-india.com> 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 = 'mallah_rajesh@yahoo.com')
>>          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 = 'mallah_rajesh@yahoo.com'
>          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;
>       
>
>
>
>
>
>
>  
>

^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* [ PROBLEM SOLVED ] Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
@ 2003-11-01 05:47  Rajesh Kumar Mallah <mallah@trade-india.com>
  parent: Greg Stark <gsstark@mit.edu>
  0 siblings, 0 replies; 16+ messages in thread

From: Rajesh Kumar Mallah @ 2003-11-01 05:47 UTC (permalink / raw)
  To: Greg Stark <gsstark@mit.edu>; +Cc: Christopher Browne <cbbrowne@acm.org>; pgsql-performance@postgresql.org, Jesse Vincent <jesse@bestpractical.com>



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@yahoo.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;




^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
@ 2003-11-01 08:31  Rajesh Kumar Mallah <mallah@trade-india.com>
  parent: Greg Stark <gsstark@mit.edu>
  1 sibling, 0 replies; 16+ messages in thread

From: Rajesh Kumar Mallah @ 2003-11-01 08:31 UTC (permalink / raw)
  To: Greg Stark <gsstark@mit.edu>; +Cc: Christopher Browne <cbbrowne@acm.org>; pgsql-performance@postgresql.org, Jesse Vincent <jesse@bestpractical.com>


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) = 'mallah_rajesh@yahoo.com' 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) = 'mallah_rajesh@yahoo.com'::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 = 'mallah_rajesh@yahoo.com')
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;


                                                                                                                                                                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=582.27..582.34 rows=1 width=164) (actual time=592.406..592.529 rows=4 loops=1)
   ->  Unique  (cost=582.27..582.34 rows=1 width=164) (actual time=592.401..592.516 rows=4 loops=1)
         ->  Sort  (cost=582.27..582.28 rows=1 width=164) (actual time=592.398..592.406 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
               ->  Hash Join  (cost=476.18..582.26 rows=1 width=164) (actual time=591.548..592.211 rows=8 loops=1)
                     Hash Cond: ("outer".groupid = "inner".id)
                     ->  Nested Loop  (cost=0.00..105.97 rows=21 width=4) (actual time=0.214..0.645 rows=37 loops=1)
                           ->  Index Scan using users4 on users users_4  (cost=0.00..3.99 rows=2 width=4) (actual time=0.107..0.112 rows=1 loops=1)
                                 Index Cond: ((emailaddress)::text = 'mallah_rajesh@yahoo.com'::text)
                           ->  Index Scan using cachedgroupmembers2 on cachedgroupmembers cachedgroupmembers_3  (cost=0.00..50.81 rows=14 width=8) (actual time=0.098..0.441 rows=37 loops=1)
                                 Index Cond: (cachedgroupmembers_3.memberid = "outer".id)
                     ->  Hash  (cost=476.17..476.17 rows=1 width=168) (actual time=591.121..591.121 rows=0 loops=1)
                           ->  Nested Loop  (cost=0.00..476.17 rows=1 width=168) (actual time=0.391..583.085 rows=3208 loops=1)
                                 ->  Nested Loop  (cost=0.00..471.54 rows=1 width=168) (actual time=0.309..474.968 rows=3208 loops=1)
                                       ->  Seq Scan on tickets main  (cost=0.00..465.62 rows=1 width=164) (actual time=0.111..56.930 rows=3208 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.105..0.112 rows=1 loops=3208)
                                             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.014..0.017 rows=1 loops=3208)
                                       Index Cond: ("outer".id = principals_2.objectid)
                                       Filter: ((principaltype)::text = 'Group'::text)
 Total runtime: 593.062 ms
(22 rows)




Regds
Mallah.

Greg Stark wrote:

>Rajesh Kumar Mallah <mallah@trade-india.com> 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) = 'mallah_rajesh@yahoo.com'
>          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;
>
>  
>

^ permalink  raw  reply  [nested|flat] 16+ messages in thread

* Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
@ 2003-11-01 08:50  Rajesh Kumar Mallah <mallah@trade-india.com>
  parent: Greg Stark <gsstark@mit.edu>
  1 sibling, 0 replies; 16+ messages in thread

From: Rajesh Kumar Mallah @ 2003-11-01 08:50 UTC (permalink / raw)
  To: Greg Stark <gsstark@mit.edu>; +Cc: Christopher Browne <cbbrowne@acm.org>; pgsql-performance@postgresql.org, Jesse Vincent <jesse@bestpractical.com>


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) = 'mallah_rajesh@yahoo.com' 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) = 'mallah_rajesh@yahoo.com'::text)
 Total runtime: 2859.34 msec
(25 rows)





Greg Stark wrote:

>Rajesh Kumar Mallah <mallah@trade-india.com> 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) = 'mallah_rajesh@yahoo.com'
>          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;
>
>  
>

^ permalink  raw  reply  [nested|flat] 16+ messages in thread


end of thread, other threads:[~2003-11-01 08:50 UTC | newest]

Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-29 20:47 Query puts 7.3.4 on endless loop but 7.4beta5 is fine. mallah@trade-india.com
2003-10-30 11:32 ` Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. [ with better indenting ] Rajesh Kumar Mallah <mallah@trade-india.com>
2003-10-30 12:52   ` Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. [ with better indenting ] Christopher Browne <cbbrowne@acm.org>
2003-10-30 14:12     ` Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. [ with better indenting ] mallah@trade-india.com
2003-10-30 15:10       ` Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. [ with better indenting ] Christopher Browne <cbbrowne@acm.org>
2003-11-01 03:08         ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-10-30 18:18           ` Greg Stark <gsstark@mit.edu>
2003-10-30 18:45           ` Greg Stark <gsstark@mit.edu>
2003-11-01 05:44             ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-10-30 21:32               ` Greg Stark <gsstark@mit.edu>
2003-11-01 08:31                 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-11-01 08:50                 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-10-30 22:38               ` Greg Stark <gsstark@mit.edu>
2003-11-01 05:47                 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-10-30 16:11   ` Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. [ with better indenting ] Tom Lane <tgl@sss.pgh.pa.us>
2003-11-01 03:19     ` Rajesh Kumar Mallah <mallah@trade-india.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox