Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
On Thu, Oct 30, 2003 at 01:15:44AM +0530, [EMAIL PROTECTED] wrote: > Actually PostgreSQL is at par with MySQL when the query is being Properly > Written(simplified) > > In mysql: > mysql> SELECT DISTINCT main.* FROM Groups main join Principals Principals_1 > using(id) join ACL > ACL_2 on (ACL_2.PrincipalId = Principals_1.id) Interesting, last time I looked, this syntax wasn't valid on mysql. And I'm not familiar with the "using(id)" notation. Can you point me at proper docs on it? > > So its not just PostgreSQL that is suffering from the bad SQL but MySQL also. > But the question is my does PostgreSQL suffer so badly ?? > I think not all developers write very nice SQLs. > > Its really sad to see that a fine peice of work (RT) is performing sub-optimal > becoz of malformed SQLs. [ specially on database of my choice ;-) ] Can you try using SearchBuilder 0.90? That made certain optimizations to the postgres query builder that got backed out in 0.92, due to a possible really bad failure mode. Thankfully, because all of this is machine generated SQL we can just improve the generator, rather than having to retool the entire application. -- jesse reed vincent -- [EMAIL PROTECTED] -- [EMAIL PROTECTED] 70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90 "If IBM _wanted_ to make clones, we could make them cheaper and faster than anyone else!" - An IBM Rep. visiting Vassar College's Comp Sci Department. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
On Thursday 30 Oct 2003 4:53 am, you wrote: > <[EMAIL PROTECTED]> writes: > > Actually PostgreSQL is at par with MySQL when the query is being > > Properly Written(simplified) > > These are not the same query, though. Your original looks like Yes that was an optimisation on haste the simplification was not accurate. I will work on it again. But incidently both the SQLs produced the same results which *may* mean that the query could have been done in a simpler manner. > > SELECT DISTINCT main.* > FROM Groups main , Principals Principals_1, ACL ACL_2 > WHERE > ((ACL_2.RightName = 'OwnTicket') OR (ACL_2.RightName = 'SuperUser')) > AND ((ACL_2.PrincipalId = Principals_1.id AND > ACL_2.PrincipalType = 'Group' AND > (main.Domain = 'SystemInternal' OR main.Domain = 'UserDefined' OR > main.Domain = 'ACLEquivalence') AND main.id = Principals_1.id) > OR > (((main.Domain = 'RT::Queue-Role' AND main.Instance = 25) OR >(main.Domain = 'RT::Ticket-Role' AND main.Instance = 6973)) AND > main.Type = ACL_2.PrincipalType AND > main.id = Principals_1.id)) > AND (ACL_2.ObjectType = 'RT::System' OR > (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 25)) > ORDER BY main.Name ASC > > where the replacement is > > SELECT DISTINCT main.* > FROM Groups main join Principals Principals_1 using(id) > join ACL ACL_2 on (ACL_2.PrincipalId = Principals_1.id) > WHERE > ((ACL_2.RightName = 'OwnTicket') OR (ACL_2.RightName = 'SuperUser')) > AND ((ACL_2.PrincipalType = 'Group' AND > (main.Domain = 'SystemInternal' OR main.Domain = 'UserDefined' OR > main.Domain = 'ACLEquivalence')) OR > (((main.Domain = 'RT::Queue-Role' AND main.Instance = 25) OR > (main.Domain = 'RT::Ticket-Role' AND main.Instance = 6973)) AND >main.Type = ACL_2.PrincipalType)) > AND (ACL_2.ObjectType = 'RT::System' OR > (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 25)) > ORDER BY main.Name ASC ; > > You have made the condition "ACL_2.PrincipalId = Principals_1.id" > required for all cases, where before it appeared in only one arm of an > OR condition. If the second query is correct, then the first one is > wrong, and your real problem is that your SQL generator is broken. Yes the SQL generator is not doing the best things at the moment and the author(Jesse) is aware of it and looking forward to our help in optimising it. > > (I'd argue that the SQL generator is broken anyway ;-) if it generates > such horrible conditions as that. Or maybe the real problem is that > the database schema is a mess and needs rethinking.) I do not think the database schema is a mess. The ACL system in RT and RT itself is quite comprehensive. The problem is with the Query Generator. Apologies for delayed response to your email. Regards Mallah. > > regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
Tom Lane <[EMAIL PROTECTED]> writes: > (I'd argue that the SQL generator is broken anyway ;-) if it generates > such horrible conditions as that. Or maybe the real problem is that > the database schema is a mess and needs rethinking.) I had the same reaction when I first saw those queries. But I think the problem with the RT schema is that it needs to implement an ACL system that satisfies lots of different usage models. Some people that use it want tickets to be accessible implicitly by the opener like a bug tracking system, others want the tickets to be internal only like a network trouble ticketing system. Some people want to restrict specific operations at a fine-grain, others want to be have more sweeping acls. I've tried doing ACL systems before and they always turned into messes long before that point. I always end up pushing back and trying to force the client to make up his or her mind of exactly what he or she needs before my head explodes . If there's a nice general model for ACLs that can include completely different usage models I've never found it. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
[EMAIL PROTECTED] writes:
> I really not intend to start a flame war here but i am genuinely
> seeking help to retain PostgreSQL as my database for my RT system.
If there are things that can be discovered to feed back to the RT
developers to improve PostgreSQL's usefulness as a data store for RT,
that would be a Good Thing for anyone that would be interested in
using PG+RT.
--
output = reverse("ofni.smrytrebil" "@" "enworbbc")
Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
<[EMAIL PROTECTED]> writes: > Actually PostgreSQL is at par with MySQL when the query is being > Properly Written(simplified) These are not the same query, though. Your original looks like SELECT DISTINCT main.* FROM Groups main , Principals Principals_1, ACL ACL_2 WHERE ((ACL_2.RightName = 'OwnTicket') OR (ACL_2.RightName = 'SuperUser')) AND ((ACL_2.PrincipalId = Principals_1.id AND ACL_2.PrincipalType = 'Group' AND (main.Domain = 'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence') AND main.id = Principals_1.id) OR (((main.Domain = 'RT::Queue-Role' AND main.Instance = 25) OR (main.Domain = 'RT::Ticket-Role' AND main.Instance = 6973)) AND main.Type = ACL_2.PrincipalType AND main.id = Principals_1.id)) AND (ACL_2.ObjectType = 'RT::System' OR (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 25)) ORDER BY main.Name ASC where the replacement is SELECT DISTINCT main.* FROM Groups main join Principals Principals_1 using(id) join ACL ACL_2 on (ACL_2.PrincipalId = Principals_1.id) WHERE ((ACL_2.RightName = 'OwnTicket') OR (ACL_2.RightName = 'SuperUser')) AND ((ACL_2.PrincipalType = 'Group' AND (main.Domain = 'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence')) OR (((main.Domain = 'RT::Queue-Role' AND main.Instance = 25) OR (main.Domain = 'RT::Ticket-Role' AND main.Instance = 6973)) AND main.Type = ACL_2.PrincipalType)) AND (ACL_2.ObjectType = 'RT::System' OR (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 25)) ORDER BY main.Name ASC ; You have made the condition "ACL_2.PrincipalId = Principals_1.id" required for all cases, where before it appeared in only one arm of an OR condition. If the second query is correct, then the first one is wrong, and your real problem is that your SQL generator is broken. (I'd argue that the SQL generator is broken anyway ;-) if it generates such horrible conditions as that. Or maybe the real problem is that the database schema is a mess and needs rethinking.) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
> On Thu, 30 Oct 2003 [EMAIL PROTECTED] wrote:
>
>> >> So its not just PostgreSQL that is suffering from the bad SQL but MySQL also.
>> >> But the
>> >> question is my does PostgreSQL suffer so badly ?? I think not all developers
>> >> write very
>> >> nice SQLs.
>> >>
>> >> Its really sad to see that a fine peice of work (RT) is performing sub-optimal
>> >> becoz of
>> >> malformed SQLs. [ specially on database of my choice ;-) ]
>> >
>> > Post EXPLAIN ANALYZES of the queries you're running, then maybe you'll be able to
>> > get some
>> > useful help from this list. Until then, it's very hard to speculate as to why
>> > PostgreSQL is
>> > slower. -sc
>>
>> Here It is:
>>
>> in case they are illegeble please lemme know i will attach it as .txt files.
>>
>> Slower One:
>>
>> explain analyze SELECT DISTINCT main.* FROM Groups main , Principals Principals_1,
>> ACL ACL_2
>> WHERE ((ACL_2.RightName = 'OwnTicket')OR(ACL_2.RightName = 'SuperUser')) AND ( (
>> ACL_2.PrincipalId = Principals_1.id AND ACL_2.PrincipalType = 'Group' AND (
>> main.Domain =
>> 'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence')
>> AND main.id
>> = Principals_1.id) OR ( ( (main.Domain = 'RT::Queue-Role' AND main.Instance = 25)
>> OR (
>> main.Domain = 'RT::Ticket-Role' AND main.Instance = 6973) ) AND main.Type =
>> ACL_2.PrincipalType AND main.id = Principals_1.id) ) AND (ACL_2.ObjectType =
>> 'RT::System' OR
>> (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 25) ) ORDER BY main.Name ASC ;
>
> Note here:
>
> Merge Join
> (cost=1788.68..4735.71 rows=1 width=85)
> (actual time=597.540..1340.526 rows=20153 loops=1)
> Merge Cond: ("outer".id = "inner".id)
>
> This estimate is WAY off. Are both of those fields indexed and analyzed?
Yes both are primary keys. and i did vacuum full verbose analyze;
Have you tried
> upping the statistics target on those two fields?
> I assume they are compatible types.
Yes they are
>
> You might try 'set enable_mergejoin = false' and see if it does something faster
> here. Just a
> guess.
Did not help
regds
mallah.
-
Over 1,00,000 exporters are waiting for your order! Click below to get
in touch with leading Indian exporters listed in the premier
trade directory Exporters Yellow Pages.
http://www.trade-india.com/dyn/gdh/eyp/
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
>> So its not just PostgreSQL that is suffering from the bad SQL but MySQL also. But
>> the
>> question is my does PostgreSQL suffer so badly ?? I think not all developers write
>> very nice
>> SQLs.
>>
>> Its really sad to see that a fine peice of work (RT) is performing sub-optimal
>> becoz of
>> malformed SQLs. [ specially on database of my choice ;-) ]
>
> Post EXPLAIN ANALYZES of the queries you're running, then maybe you'll be able to
> get some
> useful help from this list. Until then, it's very hard to speculate as to why
> PostgreSQL is
> slower. -sc
Here It is:
in case they are illegeble please lemme know i will attach it as .txt
files.
Slower One:
explain analyze SELECT DISTINCT main.* FROM Groups main , Principals Principals_1, ACL
ACL_2
WHERE ((ACL_2.RightName = 'OwnTicket')OR(ACL_2.RightName = 'SuperUser')) AND ( (
ACL_2.PrincipalId = Principals_1.id AND ACL_2.PrincipalType = 'Group' AND (
main.Domain =
'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence') AND
main.id =
Principals_1.id) OR ( ( (main.Domain = 'RT::Queue-Role' AND main.Instance = 25) OR (
main.Domain
= 'RT::Ticket-Role' AND main.Instance = 6973) ) AND main.Type = ACL_2.PrincipalType
AND main.id
= Principals_1.id) ) AND (ACL_2.ObjectType = 'RT::System' OR (ACL_2.ObjectType =
'RT::Queue' AND
ACL_2.ObjectId = 25) ) ORDER BY main.Name ASC ;
Unique (cost=4744.06..4744.08 rows=1 width=81) (actual time=6774.140..6774.204 rows=5
loops=1)
-> Sort (cost=4744.06..4744.07 rows=1 width=81) (actual time=6774.136..6774.145
rows=6 loops=1)
Sort Key: main.name, main.id, main.description, main."domain", main."type",
main.instance
-> Nested Loop (cost=1788.68..4744.05 rows=1 width=81) (actual
time=597.744..6774.042
rows=6 loops=1) Join Filter: "inner".principaltype)::text =
'Group'::text) OR
(("outer"."domain")::text = 'RT::Ticket-Role'::text) OR
(("outer"."domain")::text =
'RT::Queue-Role'::text)) AND ((("inner".principaltype)::text =
'Group'::text) OR
(("outer".instance)::text = '6973'::text) OR (("outer"."domain")::text =
'RT::Queue-Role'::text)) AND ((("inner".principaltype)::text =
'Group'::text) OR
(("outer"."domain")::text = 'RT::Ticket-Role'::text) OR
(("outer".instance)::text =
'25'::text)) AND ((("inner".principaltype)::text = 'Group'::text) OR
(("outer".instance)::text = '6973'::text) OR (("outer".instance)::text =
'25'::text)) AND ((("inner".principaltype)::text = 'Group'::text) OR
(("outer"."type")::text = ("inner".principaltype)::text)) AND
((("outer"."domain")::text = 'SystemInternal'::text) OR
(("outer"."domain")::text =
'UserDefined'::text) OR (("outer"."domain")::text =
'ACLEquivalence'::text) OR
(("outer"."type")::text = ("inner".principaltype)::text)) AND
(("inner".principalid
= "outer".id) OR (("outer"."domain")::text = 'RT::Ticket-Role'::text) OR
(("outer"."domain")::text = 'RT::Queue-Role'::text)) AND
(("inner".principalid =
"outer".id) OR (("outer".instance)::text = '6973'::text) OR
(("outer"."domain")::text = 'RT::Queue-Role'::text)) AND
(("inner".principalid =
"outer".id) OR (("outer"."domain")::text = 'RT::Ticket-Role'::text) OR
(("outer".instance)::text = '25'::text)) AND (("inner".principalid =
"outer".id) OR
(("outer".instance)::text = '6973'::text) OR (("outer".instance)::text =
'25'::text)) AND (("inner".principalid = "outer".id) OR
(("outer"."type")::text =
("inner".principaltype)::text)) AND (("outer".id = "outer".id) OR
(("outer"."type")::text = ("inner".principaltype)::text)) AND
(("inner".principalid
= "outer".id) OR ("outer".id = "outer".id)) AND
((("inner".principaltype)::text =
'Group'::text) OR ("outer".id = "outer".id))) -> Merge
Join (cost=1788.68..4735.71 rows=1 width=85) (actual
time=597.540..1340.526 rows=20153 loops=1) Merge
Cond: ("outer".id = "inner".id)
Join Filter: ((("inner".id = "outer".id) OR
(("inner"."domain")::text =
'RT::Ticket-Role'::text) OR (("inner"."domain")::text =
'RT::Queue-Role'::text)) AND (("inner".id = "outer".id) OR
(("inner".instance)::text = '6973'::text) OR
(("inner"."domain")::text =
'RT::Queue-Role'::text)) AND (("inner".id = "outer".id) OR
(("inner"."domain")::text = 'RT::Ticket-Role'::text) OR
(("inner".instance)::text = '25'::text)) AND (("inner".id =
"outer".id) OR
(("inner".instance)::text = '6973'::text) OR
(("inner".instance)::text =
'25'::t
Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
> > > > On Thu, Oct 30, 2003 at 01:15:44AM +0530, [EMAIL PROTECTED] wrote: >> Actually PostgreSQL is at par with MySQL when the query is being Properly >> Written(simplified) >> >> In mysql: >> mysql> SELECT DISTINCT main.* FROM Groups main join Principals Principals_1 >> using(id) join >> ACL ACL_2 on (ACL_2.PrincipalId = Principals_1.id) > > Interesting, last time I looked, this syntax wasn't valid on mysql. And I'm not > familiar with > the "using(id)" notation. Can you point me at proper docs on it? I am using MySQL 4.0.16 the latest stable one. Docs MySQL: http://www.mysql.com/doc/en/JOIN.html Postgresql: well i am not able to point out a dedicated page for this topic in pgsql document but below covers it a bit. http://www.postgresql.org/docs/7.3/static/sql-select.html Join i beleive are SQL standard feature and better docs shud exist. > > >> >> So its not just PostgreSQL that is suffering from the bad SQL but MySQL also. But >> the question >> is my does PostgreSQL suffer so badly ?? >> I think not all developers write very nice SQLs. >> >> Its really sad to see that a fine peice of work (RT) is performing sub-optimal >> becoz of >> malformed SQLs. [ specially on database of my choice ;-) ] > > Can you try using SearchBuilder 0.90? That made certain optimizations to the > postgres query > builder that got backed out in 0.92, due to a > possible really bad failure mode. Thankfully, because all of this is machine > generated SQL we > can just improve the generator, rather than having to retool the entire application. True, Its really a pleasure to see that in DBIx/SearchBuilder/Handle/Pg.pm Database Specific optimisations can be done easily Congratulations on writing SearchBuilder in such an well structured manner. mine is .92 just going to try .90 as u are suggesting and will post back the result. > > > -- > jesse reed vincent -- [EMAIL PROTECTED] -- [EMAIL PROTECTED] > 70EBAC90: 2A07 FC22 7DB4 42C1 9D71 0108 41A3 3FB3 70EB AC90 > > "If IBM _wanted_ to make clones, we could make them cheaper and faster than anyone > else!" - An > IBM Rep. visiting Vassar College's Comp Sci Department. - 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
> So its not just PostgreSQL that is suffering from the bad SQL but > MySQL also. But the question is my does PostgreSQL suffer so badly > ?? I think not all developers write very nice SQLs. > > Its really sad to see that a fine peice of work (RT) is performing > sub-optimal becoz of malformed SQLs. [ specially on database of my > choice ;-) ] Post EXPLAIN ANALYZES of the queries you're running, then maybe you'll be able to get some useful help from this list. Until then, it's very hard to speculate as to why PostgreSQL is slower. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
Actually PostgreSQL is at par with MySQL when the query is being Properly Written(simplified) like below rt3=# SELECT DISTINCT main.* FROM Groups main join Principals Principals_1 using(id) join ACL ACL_2 on (ACL_2.PrincipalId = Principals_1.id) WHERE ((ACL_2.RightName = 'OwnTicket')OR(ACL_2.RightName = 'SuperUser')) AND ( ( ACL_2.PrincipalType = 'Group' AND ( main.Domain = 'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence') ) OR ( ( (main.Domain = 'RT::Queue-Role' AND main.Instance = 25) OR ( main.Domain = 'RT::Ticket-Role' AND main.Instance = 6973) ) AND main.Type = ACL_2.PrincipalType ) ) AND (ACL_2.ObjectType = 'RT::System' OR (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 25) ) ORDER BY main.Name ASC ; id |name|description| domain | type| instance ---++---++---+-- 40264 | sales | Sales team in Delhi | UserDefined| | 2 | User 1 | ACL equiv. for user 1 | ACLEquivalence | UserEquiv | 1 11 | User 10| ACL equiv. for user 10| ACLEquivalence | UserEquiv | 10 13 | User 12| ACL equiv. for user 12| ACLEquivalence | UserEquiv | 12 31123 | User 31122 | ACL equiv. for user 31122 | ACLEquivalence | UserEquiv | 31122 (5 rows) ( Total runtime: 1.699 ms ) Time: 6.455 ms which is 0.00 6455 Secs In mysql: mysql> SELECT DISTINCT main.* FROM Groups main join Principals Principals_1 using(id) join ACL ACL_2 on (ACL_2.PrincipalId = Principals_1.id) WHERE ((ACL_2.RightName = 'OwnTicket')OR(ACL_2.RightName = 'SuperUser')) AND ( ( ACL_2.PrincipalType = 'Group' AND ( main.Domain = 'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence') ) OR ( ( (main.Domain = 'RT::Queue-Role' AND main.Instance = 25) OR ( main.Domain = 'RT::Ticket-Role' AND main.Instance = 6973) ) AND main.Type = ACL_2.PrincipalType ) ) AND (ACL_2.ObjectType = 'RT::System' OR (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 25) ) ORDER BY main.Name ASC ;+---++---++---+--+ | id| Name | Description | Domain | Type | Instance | +---++---++---+--+ | 40208 | sales | Sales team in Delhi | UserDefined| | | | 2 | User 1 | ACL equiv. for user 1 | ACLEquivalence | UserEquiv | 1 | |11 | User 10| ACL equiv. for user 10| ACLEquivalence | UserEquiv | 10 | |13 | User 12| ACL equiv. for user 12| ACLEquivalence | UserEquiv | 12 | | 31067 | User 31066 | ACL equiv. for user 31066 | ACLEquivalence | UserEquiv | 31066 | +---++---++---+--+ 5 rows in set (0.00 sec) mysql> So its not just PostgreSQL that is suffering from the bad SQL but MySQL also. But the question is my does PostgreSQL suffer so badly ?? I think not all developers write very nice SQLs. Its really sad to see that a fine peice of work (RT) is performing sub-optimal becoz of malformed SQLs. [ specially on database of my choice ;-) ] Regds Mallah. > > Dear PostgreSQL gurus, > > I really not intend to start a flame war here but i am genuinely > seeking help to retain PostgreSQL as my database for my RT > system. > > Few months back i had posted regarding lowering of column names in SQL being passed > to RDBMS by > DBIx::SearchBuilder , looks like it was controlled by a parameter "CASESENSITIVE" > changing it > to 1 from 0 did help for postgresql to MySQL it probably does not matter. > > > But This time its a different situation > The query in Postgresql is taking 6 times more than MySQL > > The Query being given gets generated by DBIx::SearchBuilder. > Although i am not sure but i feel modules like DBIx::SearchBuilder which are > supposed to > provide RDBMS independent abstraction are unfortunately getting test only with > MySQL or Oracle > otherwise such huge difference in timing were not possible. > > > > IN MYSQL: > > mysql> SELECT DISTINCT main.* FROM Groups main , Principals Principals_1, ACL ACL_2 > WHERE > ((ACL_2.RightName = 'OwnTicket')OR(ACL_2.RightName = 'SuperUser')) AND ( ( > ACL_2.PrincipalId = Principals_1.id AND ACL_2.PrincipalType = 'Group' AND ( > main.Domain = > 'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain = 'ACLEquivalence') > AND main.id > = Principals_1.id) OR ( ( (main.Domain = 'RT::Queue-Role' AND main.Instance = 25) > OR ( > main.Domain = 'RT::Ticket-Role' AND main.Instance = 6973) ) AND main.Type = > ACL_2.PrincipalType AND main.id = Principals_1.id) ) AND (ACL_2.ObjectType = > 'RT::System' OR > (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 25) ) ORDER BY main.Name ASC > ;+---++-
