Skip site navigation (1) Skip section navigation (2)

Query puts 7.3.4 on endless loop but 7.4beta5 is fine.

From: <mallah(at)trade-india(dot)com>
To: <jesse(at)fsck(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
Date: 2003-10-29 20:47:09
Message-ID: 33402.203.145.130.142.1067460429.squirrel@mail.trade-india.com (view raw or flat)
Thread:
Lists: 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(at)yahoo(dot)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(at)yahoo(dot)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(at)yahoo(dot)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(at)yahoo(dot)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(at)trade-india(dot)com wrote:
>> >
>> >
>> >
>> > On Thu, Oct 30, 2003 at 01:30:26AM +0530, mallah(at)trade-india(dot)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(at)eruditorum(dot)org -- jesse(at)fsck(dot)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(at)eruditorum(dot)org -- jesse(at)fsck(dot)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/



Responses

pgsql-performance by date

Next:From: scott.marloweDate: 2003-10-29 21:51:18
Subject: Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with
Previous:From: mallahDate: 2003-10-29 20:22:56
Subject: Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group