Thread: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
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=1width=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_pkeyon 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_objecttypeon 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/
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 majordomo@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/
> 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
> > > > On Thu, Oct 30, 2003 at 01:15:44AM +0530, mallah@trade-india.com 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 -- root@eruditorum.org -- jesse@fsck.com > 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/
>> 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=1width=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'::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_pkeyon principals principals_1
(cost=0.00..2536.49 rows=82221 width=4) (actual time=0.073..248.849
rows=64626 loops=1) -> Sort (cost=1788.68..1797.99 rows=3726 width=81)
(actual
time=597.360..645.859 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.105..456.682
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_objecttypeon acl acl_2 (cost=0.00..8.03
rows=3 width=13) (actual time=0.034..0.150 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: 6778.888 ms
BETTER ONE:
explain analyze 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 ;
Unique (cost=22.18..22.20 rows=1 width=81) (actual time=0.878..0.910 rows=5 loops=1)
-> Sort (cost=22.18..22.19 rows=1 width=81) (actual time=0.875..0.881 rows=6 loops=1)
Sort Key: main.name, main.id, main.description, main."domain", main."type", main.instance
-> Nested Loop (cost=0.00..22.17 rows=1 width=81) (actual time=0.255..0.814 rows=6
loops=1) -> Nested Loop (cost=0.00..17.54 rows=1 width=85) (actual time=0.194..0.647
rows=6 loops=1) Join Filter: (((("outer".principaltype)::text = 'Group'::text) OR
(("inner"."domain")::text = 'RT::Ticket-Role'::text) OR
(("inner"."domain")::text = 'RT::Queue-Role'::text)) AND
((("outer".principaltype)::text = 'Group'::text) OR (("inner".instance)::text
= '6973'::text) OR (("inner"."domain")::text = 'RT::Queue-Role'::text)) AND
((("outer".principaltype)::text = 'Group'::text) OR (("inner"."domain")::text
= 'RT::Ticket-Role'::text) OR (("inner".instance)::text = '25'::text)) AND
((("outer".principaltype)::text = 'Group'::text) OR (("inner".instance)::text
= '6973'::text) OR (("inner".instance)::text = '25'::text)) AND
((("outer".principaltype)::text = 'Group'::text) OR (("inner"."type")::text =
("outer".principaltype)::text)) AND ((("inner"."domain")::text =
'SystemInternal'::text) OR (("inner"."domain")::text = 'UserDefined'::text)
OR (("inner"."domain")::text = 'ACLEquivalence'::text) OR
(("inner"."type")::text = ("outer".principaltype)::text))) -> Index Scan
usingacl_objectid, acl_objecttype on acl acl_2
(cost=0.00..8.03 rows=3 width=13) (actual time=0.064..0.190 rows=6 loops=1)
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))) -> Index Scan using
groups_pkeyon groups main (cost=0.00..3.11 rows=1
width=81) (actual time=0.050..0.051 rows=1 loops=6) Index Cond:
("outer".principalid= main.id)
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 ((instance)::text =
'6973'::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 principals_pkey on
principalsprincipals_1 (cost=0.00..4.62
rows=1 width=4) (actual time=0.017..0.019 rows=1 loops=6) Index Cond:
("outer".principalid= principals_1.id)
Total runtime: 1.151 ms
(15 rows)
>
> --
> Sean Chittenden
>
> ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and
> unsubscribe commands go to majordomo@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/
On Thu, 30 Oct 2003 mallah@trade-india.com 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?
Have you tried upping the statistics target on those two fields?
I assume they are compatible types.
You might try 'set enable_mergejoin = false' and see if it does something
faster here. Just a guess.
> On Thu, 30 Oct 2003 mallah@trade-india.com 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/
<mallah@trade-india.com> 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
mallah@trade-india.com 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")
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)
Tom Lane <tgl@sss.pgh.pa.us> 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
On Thursday 30 Oct 2003 4:53 am, you wrote: > <mallah@trade-india.com> 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
On Thu, Oct 30, 2003 at 01:15:44AM +0530, mallah@trade-india.com 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 -- root@eruditorum.org -- jesse@fsck.com 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.
>>>>> "scott" == scott marlowe <scott.marlowe@ihs.com> writes:
[...]
scott> Note here:
scott> Merge Join (cost=1788.68..4735.71 rows=1 width=85) (actual
scott> time=597.540..1340.526 rows=20153 loops=1) Merge Cond:
scott> ("outer".id = "inner".id)
scott> This estimate is WAY off. Are both of those fields indexed
scott> and analyzed? Have you tried upping the statistics target on
scott> those two fields? I assume they are compatible types.
Should I understand that a join on incompatible types (such as integer
and varchar) may lead to bad performances ?
--
Laurent Martelli
laurent@aopsys.com Java Aspect Components
http://www.aopsys.com/ http://jac.aopsys.com
Laurent Martelli wrote:
>>>>>>"scott" == scott marlowe <scott.marlowe@ihs.com> writes:
>
>
> [...]
>
> scott> Note here:
>
> scott> Merge Join (cost=1788.68..4735.71 rows=1 width=85) (actual
> scott> time=597.540..1340.526 rows=20153 loops=1) Merge Cond:
> scott> ("outer".id = "inner".id)
>
> scott> This estimate is WAY off. Are both of those fields indexed
> scott> and analyzed? Have you tried upping the statistics target on
> scott> those two fields? I assume they are compatible types.
>
> Should I understand that a join on incompatible types (such as integer
> and varchar) may lead to bad performances ?
Conversely, you should enforce strict type compatibility in comparisons for
getting any good plans..:-)
Shridhar
>>>>> "Shridhar" == Shridhar Daithankar <shridhar_daithankar@myrealbox.com> writes:
Shridhar> Laurent Martelli wrote:
[...]
>> Should I understand that a join on incompatible types (such as
>> integer and varchar) may lead to bad performances ?
Shridhar> Conversely, you should enforce strict type compatibility
Shridhar> in comparisons for getting any good plans..:-)
Ha ha, now I understand why a query of mine was so sluggish.
Is there a chance I could achieve the good perfs without having he
same types ? I've tried a CAST in the query, but it's even a little
worse than without it. However, using a view to cast integers into
varchar gives acceptable results (see at the end).
I'm using Postgresql 7.3.4.
iprofil-jac=# EXPLAIN ANALYZE SELECT * from classes where exists (select value from lists where lists.id='16' and
lists.value=classes.id);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Seq Scan on classes (cost=0.00..5480289.75 rows=9610 width=25) (actual time=31.68..7321.56 rows=146 loops=1)
Filter: (subplan)
SubPlan
-> Index Scan using lists_id on lists (cost=0.00..285.12 rows=1 width=8) (actual time=0.38..0.38 rows=0
loops=19220)
Index Cond: (id = 16)
Filter: ((value)::text = ($0)::text)
Total runtime: 7321.72 msec
iprofil-jac=# EXPLAIN ANALYZE SELECT * from classes2 where exists (select value from lists where lists.id='16' and
lists.value=classes2.id);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Seq Scan on classes2 (cost=0.00..5923.87 rows=500 width=64) (actual time=0.76..148.20 rows=146 loops=1)
Filter: (subplan)
SubPlan
-> Index Scan using lists_value on lists (cost=0.00..5.90 rows=1 width=8) (actual time=0.01..0.01 rows=0
loops=19220)
Index Cond: ((id = 16) AND (value = $0))
Total runtime: 148.34 msec
--
-- Tables classes and classes2 are populated with the same data, they
-- only differ on the type of the "id" column.
--
iprofil-jac=# \d classes
Table "public.classes"
Colonne | Type | Modifications
---------+-------------------+---------------
id | integer | not null
classid | character varying |
Index: classes_pkey primary key btree (id)
iprofil-jac=# \d classes2
Table "public.classes2"
Colonne | Type | Modifications
---------+-------------------+---------------
id | character varying | not null
classid | character varying |
Index: classes2_pkey primary key btree (id)
iprofil-jac=# \d lists
Table "public.lists"
Colonne | Type | Modifications
---------+-------------------+---------------
id | integer | not null
index | integer | not null
value | character varying |
Index: lists_index unique btree (id, "index"),
lists_id btree (id),
lists_value btree (id, value)
--
-- IT'S EVEN BETTER WITH A JOIN
--
iprofil-jac=# EXPLAIN ANALYZE SELECT * from lists join classes on classes.id=lists.value where lists.id='16';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..90905.88 rows=298 width=41) (actual time=53.93..9327.87 rows=146 loops=1)
Join Filter: (("inner".id)::text = ("outer".value)::text)
-> Seq Scan on lists (cost=0.00..263.43 rows=146 width=16) (actual time=8.38..9.70 rows=146 loops=1)
Filter: (id = 16)
-> Seq Scan on classes (cost=0.00..333.20 rows=19220 width=25) (actual time=0.00..28.45 rows=19220 loops=146)
Total runtime: 9328.35 msec
iprofil-jac=# EXPLAIN ANALYZE SELECT * from lists join classes2 on classes2.id=lists.value where lists.id='16';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=268.67..324.09 rows=16 width=80) (actual time=9.59..65.55 rows=146 loops=1)
Merge Cond: ("outer".id = "inner".value)
-> Index Scan using classes2_pkey on classes2 (cost=0.00..52.00 rows=1000 width=64) (actual time=0.03..40.83
rows=18778loops=1)
-> Sort (cost=268.67..269.03 rows=146 width=16) (actual time=9.50..9.56 rows=146 loops=1)
Sort Key: lists.value
-> Seq Scan on lists (cost=0.00..263.43 rows=146 width=16) (actual time=8.83..9.17 rows=146 loops=1)
Filter: (id = 16)
Total runtime: 65.73 msec
--
-- CASTING IN THE QUERY IS NO GOOD
--
iprofil-jac=# EXPLAIN ANALYZE SELECT * from lists join classes on CAST(classes.id AS character varying)=lists.value
wherelists.id='16';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..90905.88 rows=298 width=41) (actual time=69.03..10017.26 rows=146 loops=1)
Join Filter: ((("inner".id)::text)::character varying = "outer".value)
-> Seq Scan on lists (cost=0.00..263.43 rows=146 width=16) (actual time=20.64..22.03 rows=146 loops=1)
Filter: (id = 16)
-> Seq Scan on classes (cost=0.00..333.20 rows=19220 width=25) (actual time=0.00..30.45 rows=19220 loops=146)
Total runtime: 10017.72 msec
--
-- CREATING A VIEW IS BETTER
--
iprofil-jac=# CREATE VIEW classes3 as SELECT CAST(id AS varchar), classid from classes;
iprofil-jac=# EXPLAIN ANALYZE SELECT * from classes3 where exists (select value from lists where lists.id='16' and
lists.value=classes3.id);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Seq Scan on classes (cost=0.00..113853.60 rows=9610 width=25) (actual time=0.91..192.31 rows=146 loops=1)
Filter: (subplan)
SubPlan
-> Index Scan using lists_value on lists (cost=0.00..5.91 rows=1 width=8) (actual time=0.01..0.01 rows=0
loops=19220)
Index Cond: ((id = 16) AND (value = (($0)::text)::character varying))
Total runtime: 192.47 msec
--
Laurent Martelli
laurent@aopsys.com Java Aspect Components
http://www.aopsys.com/ http://jac.aopsys.com
Laurent Martelli wrote: >>>>>>"Shridhar" == Shridhar Daithankar <shridhar_daithankar@myrealbox.com> writes: > > > Shridhar> Laurent Martelli wrote: > > [...] > > >> Should I understand that a join on incompatible types (such as > >> integer and varchar) may lead to bad performances ? > > Shridhar> Conversely, you should enforce strict type compatibility > Shridhar> in comparisons for getting any good plans..:-) > > Ha ha, now I understand why a query of mine was so sluggish. > > Is there a chance I could achieve the good perfs without having he > same types ? I've tried a CAST in the query, but it's even a little > worse than without it. However, using a view to cast integers into > varchar gives acceptable results (see at the end). > > I'm using Postgresql 7.3.4. I am stripping the analyze outputs and directly jumping to the end. Can you try following? 1. Make all fields integer in all the table. 2. Try following query EXPLAIN ANALYZE SELECT * from lists join classes on classes.id=lists.value where lists.id='16'::integer; How does it affect the runtime? Shridhar
>>>>> "Shridhar" == Shridhar Daithankar <shridhar_daithankar@myrealbox.com> writes: Shridhar> Laurent Martelli wrote: >>>>>>> "Shridhar" == Shridhar Daithankar >>>>>>> <shridhar_daithankar@myrealbox.com> writes: Shridhar> Laurent Martelli wrote: >> [...] >> Should I understand that a join on incompatible types >> (such as >> integer and varchar) may lead to bad performances ? Shridhar> Conversely, you should enforce strict type compatibility Shridhar> in comparisons for getting any good plans..:-) >> Ha ha, now I understand why a query of mine was so sluggish. Is >> there a chance I could achieve the good perfs without having he >> same types ? I've tried a CAST in the query, but it's even a >> little worse than without it. However, using a view to cast >> integers into varchar gives acceptable results (see at the end). >> I'm using Postgresql 7.3.4. Shridhar> I am stripping the analyze outputs and directly jumping to Shridhar> the end. Shridhar> Can you try following? Shridhar> 1. Make all fields integer in all the table. I can't do this because lists.values contains non integer data which do not refer to a classes.id value. It may sound weird. This is because it's a generic schema for a transparent persistence framework. The solution for me would rather be to have varchar everywhere. Shridhar> 2. Try following query EXPLAIN ANALYZE SELECT * from lists Shridhar> join classes on classes.id=lists.value where Shridhar> lists.id='16'::integer; Shridhar> How does it affect the runtime? Shridhar> Shridhar -- Laurent Martelli laurent@aopsys.com Java Aspect Components http://www.aopsys.com/ http://jac.aopsys.com
Laurent Martelli wrote: >>>>>>"Shridhar" == Shridhar Daithankar <shridhar_daithankar@myrealbox.com> writes: > Shridhar> I am stripping the analyze outputs and directly jumping to > Shridhar> the end. > > Shridhar> Can you try following? > > Shridhar> 1. Make all fields integer in all the table. > > I can't do this because lists.values contains non integer data which > do not refer to a classes.id value. It may sound weird. This is > because it's a generic schema for a transparent persistence framework. Fine .I understand. So instead of using a field value, can you use integer version of that field? (Was that one of your queries used that? I deleted the OP) > The solution for me would rather be to have varchar everywhere. You need to cast every occurance of that varchar field appropriately, to start with. The performance might suffer as well for numbers. > Shridhar> 2. Try following query EXPLAIN ANALYZE SELECT * from lists > Shridhar> join classes on classes.id=lists.value where > Shridhar> lists.id='16'::integer; classes.id=lists.value::integer. Try that. The aim is absolute type compatibility. If types aren't exactly same, the plan is effectively dead. <OT> I would say postgresql enforces good habits in it's application developers, from a cultural POV. Had C refused to compile without such strict type compatibility, we wouldn't have to worry about 16bit/32bit and 64 bit software. Just upgrade the compiler and everything is damn good..:-) I doubt if C would have so popular with such strict type checking but that is another issue. I think pascal enforces such strict syntax.. Not sure though.. </OT> Shridhar
>>>>> "Shridhar" == Shridhar Daithankar <shridhar_daithankar@myrealbox.com> writes: [...] Shridhar> 2. Try following query EXPLAIN ANALYZE SELECT * from lists Shridhar> join classes on classes.id=lists.value where Shridhar> lists.id='16'::integer; Shridhar> classes.id=lists.value::integer. With classes.id of type integer and lists.value of type varchar, I get "ERROR: Cannot cast type character varying to integer", which is not such a surprise. Thanks for your help anyway. -- Laurent Martelli laurent@aopsys.com Java Aspect Components http://www.aopsys.com/ http://jac.aopsys.com
Laurent Martelli wrote: >>>>>>"Shridhar" == Shridhar Daithankar <shridhar_daithankar@myrealbox.com> writes: > > > [...] > > Shridhar> 2. Try following query EXPLAIN ANALYZE SELECT * from lists > Shridhar> join classes on classes.id=lists.value where > Shridhar> lists.id='16'::integer; > > Shridhar> classes.id=lists.value::integer. > > With classes.id of type integer and lists.value of type varchar, I get > "ERROR: Cannot cast type character varying to integer", which is not > such a surprise. Try to_numbr function to get a number out of string. Then cast it to integer. http://developer.postgresql.org/docs/postgres/functions-formatting.html I hope that works. Don't have postgresql installation handy here.. Shridhar