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
> ;+-------+------------+---------------------------+----------------+-----------+----------+
> |
> 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.94 sec)
>
> mysql>
>
> WHEREAS for PostgreSQL:
> rt3=# SELECT version();
> PostgreSQL 7.4beta5 on i686-pc-linux-gnu, compiled by GCC 2.96
>
>
> rt3=# 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
> ;+-------+------------+---------------------------+----------------+-----------+----------+
> |
> 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)
> Time: 7281.574 ms
> rt3=#
>
> Explain Analyze of Above Query is being given below:
>
> Unique (cost=4744.06..4744.08 rows=1 width=81) (actual time=6179.789..6179.828
> rows=5 loops=1)
> -> Sort (cost=4744.06..4744.07 rows=1 width=81) (actual time=6179.785..6179.792
> 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=584.004..6179.712 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=583.804..1187.448
> 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'::text)) AND ((("inner"."domain")::text =
> 'SystemInternal'::text) OR
> (("inner"."domain")::text = 'UserDefined'::text) OR
> (("inner"."domain")::text = 'ACLEquivalence'::text) OR
> ("inner".id =
> "outer".id))) -> Index Scan using
> principals_pkey on
> principals principals_1 (cost=0.00..2536.49 rows=82221 width=4)
> (actual
> time=0.087..169.725 rows=64626 loops=1) ->
> Sort
> (cost=1788.68..1797.99 rows=3726 width=81) (actual
> time=583.624..625.604
> rows=20153 loops=1) Sort Key: main.id
> -> Index Scan using groups_domain, groups_domain,
> groups_domain,
> groups_lower_instance, groups_domain on groups main
> (cost=0.00..1567.66 rows=3726 width=81) (actual
> time=0.132..449.240
> rows=20153 loops=1) Index
> Cond:
> ((("domain")::text = 'SystemInternal'::text) OR
> (("domain")::text = 'UserDefined'::text) OR
> (("domain")::text =
> 'ACLEquivalence'::text) OR ((instance)::text =
> '6973'::text) OR
> (("domain")::text = 'RT::Queue-Role'::text))
> Filter: (((("domain")::text =
> 'SystemInternal'::text) OR (("domain")::text =
> 'UserDefined'::text) OR (("domain")::text =
> 'ACLEquivalence'::text) OR (("domain")::text =
> 'RT::Ticket-Role'::text) OR (("domain")::text =
> 'RT::Queue-Role'::text)) AND ((("domain")::text =
> 'SystemInternal'::text) OR (("domain")::text =
> 'UserDefined'::text) OR (("domain")::text =
> 'ACLEquivalence'::text) OR (("domain")::text =
> 'RT::Ticket-Role'::text) OR ((instance)::text =
> '25'::text))
> AND ((("domain")::text = 'SystemInternal'::text) OR
> (("domain")::text = 'UserDefined'::text) OR
> (("domain")::text =
> 'ACLEquivalence'::text) OR ((instance)::text =
> '6973'::text) OR
> ((instance)::text = '25'::text))) ->
> Index Scan
> using acl_objectid, acl_objecttype on acl acl_2
> (cost=0.00..8.03
> rows=3 width=13) (actual time=0.032..0.138 rows=6 loops=20153)
> Index Cond: ((objectid = 25) OR ((objecttype)::text =
> 'RT::System'::text))
> Filter: ((((rightname)::text = 'OwnTicket'::text) OR
> ((rightname)::text =
> 'SuperUser'::text)) AND (((objecttype)::text =
> 'RT::Queue'::text) OR
> ((objecttype)::text = 'RT::System'::text))) Total runtime:
> 6183.155 ms [ 6
> secs approx ]
> (18 rows)
>
> Sincerely Looking Forward to a 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 2: you
> can get off
> all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
-----------------------------------------
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 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
