Thread: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
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.17rows=1 width=168) (actual
time=0.684..842.401 rows=3209 loops=1) -> Nested Loop
(cost=0.00..471.54rows=1 width=168) (actual
time=0.571..704.492 rows=3209 loops=1) -> Seq
Scanon 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/
Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. [ with better indenting ]
From
Rajesh Kumar Mallah
Date:
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
Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. [ with better indenting ]
From
Christopher Browne
Date:
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/>
> 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/
Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. [ with better indenting ]
From
Christopher Browne
Date:
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.
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 5rt3=# 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.
Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine. [ with better indenting ]
From
Tom Lane
Date:
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
Tom Lane wrote:
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.
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
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
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
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 ticketsWHERE 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;
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
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 ticketsWHERE 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;
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 ticketsWHERE 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;
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
[ PROBLEM SOLVED ] Re: Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
From
Rajesh Kumar Mallah
Date:
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;