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])
