Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. [ with better indenting ] - Mailing list 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