pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
12+ messages / 8 participants
[nested] [flat]
* PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
@ 2003-10-29 19:22 mallah@trade-india.com
2003-10-29 19:45 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2003-10-29 23:17 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) Christopher Browne <cbbrowne@libertyrms.info>
0 siblings, 2 replies; 12+ messages in thread
From: mallah@trade-india.com @ 2003-10-29 19:22 UTC (permalink / raw)
To: pgsql-performance; +Cc: jesse@fsck.com
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/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
2003-10-29 19:22 PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
@ 2003-10-29 19:45 ` mallah@trade-india.com
2003-10-29 19:51 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) Jesse <jesse@fsck.com>
2003-10-29 20:03 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) Sean Chittenden <sean@chittenden.org>
2003-10-29 23:23 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 3 replies; 12+ messages in thread
From: mallah@trade-india.com @ 2003-10-29 19:45 UTC (permalink / raw)
To: mallah@trade-india.com; +Cc: pgsql-performance; jesse@fsck.com
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/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
2003-10-29 19:22 PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2003-10-29 19:45 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
@ 2003-10-29 19:51 ` Jesse <jesse@fsck.com>
2003-10-29 20:18 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2 siblings, 1 reply; 12+ messages in thread
From: Jesse @ 2003-10-29 19:51 UTC (permalink / raw)
To: mallah@trade-india.com; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
2003-10-29 19:22 PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2003-10-29 19:45 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2003-10-29 19:51 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) Jesse <jesse@fsck.com>
@ 2003-10-29 20:18 ` mallah@trade-india.com
0 siblings, 0 replies; 12+ messages in thread
From: mallah@trade-india.com @ 2003-10-29 20:18 UTC (permalink / raw)
To: jesse@fsck.com; +Cc: mallah@trade-india.com; pgsql-performance
>
>
>
> 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/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
2003-10-29 19:22 PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2003-10-29 19:45 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
@ 2003-10-29 20:03 ` Sean Chittenden <sean@chittenden.org>
2003-10-29 20:22 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2 siblings, 1 reply; 12+ messages in thread
From: Sean Chittenden @ 2003-10-29 20:03 UTC (permalink / raw)
To: mallah@trade-india.com; +Cc: pgsql-performance; jesse@fsck.com
> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
2003-10-29 19:22 PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2003-10-29 19:45 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2003-10-29 20:03 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) Sean Chittenden <sean@chittenden.org>
@ 2003-10-29 20:22 ` mallah@trade-india.com
2003-10-29 21:51 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 1 reply; 12+ messages in thread
From: mallah@trade-india.com @ 2003-10-29 20:22 UTC (permalink / raw)
To: sean@chittenden.org; +Cc: mallah@trade-india.com; pgsql-performance; jesse@fsck.com
>> 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'::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.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_objecttype on 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 using acl_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_pkey on 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 principals principals_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/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with
2003-10-29 19:22 PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2003-10-29 19:45 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2003-10-29 20:03 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) Sean Chittenden <sean@chittenden.org>
2003-10-29 20:22 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
@ 2003-10-29 21:51 ` scott.marlowe <scott.marlowe@ihs.com>
2003-10-29 22:17 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
0 siblings, 1 reply; 12+ messages in thread
From: scott.marlowe @ 2003-10-29 21:51 UTC (permalink / raw)
To: mallah@trade-india.com; +Cc: sean@chittenden.org; pgsql-performance; jesse@fsck.com
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
2003-10-29 19:22 PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2003-10-29 19:45 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2003-10-29 20:03 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) Sean Chittenden <sean@chittenden.org>
2003-10-29 20:22 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2003-10-29 21:51 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with scott.marlowe <scott.marlowe@ihs.com>
@ 2003-10-29 22:17 ` mallah@trade-india.com
0 siblings, 0 replies; 12+ messages in thread
From: mallah@trade-india.com @ 2003-10-29 22:17 UTC (permalink / raw)
To: scott.marlowe@ihs.com; +Cc: mallah@trade-india.com; sean@chittenden.org; pgsql-performance; jesse@fsck.com
> 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/
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
2003-10-29 19:22 PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2003-10-29 19:45 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
@ 2003-10-29 23:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-30 04:19 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) Greg Stark <gsstark@mit.edu>
2003-10-30 11:04 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) Rajesh Kumar Mallah <mallah@trade-india.com>
2 siblings, 2 replies; 12+ messages in thread
From: Tom Lane @ 2003-10-29 23:23 UTC (permalink / raw)
To: mallah@trade-india.com; +Cc: pgsql-performance; jesse@fsck.com
<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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
2003-10-29 19:22 PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2003-10-29 19:45 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2003-10-29 23:23 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-10-30 04:19 ` Greg Stark <gsstark@mit.edu>
1 sibling, 0 replies; 12+ messages in thread
From: Greg Stark @ 2003-10-30 04:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: mallah@trade-india.com; pgsql-performance; jesse@fsck.com
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
2003-10-29 19:22 PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2003-10-29 19:45 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2003-10-29 23:23 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-10-30 11:04 ` Rajesh Kumar Mallah <mallah@trade-india.com>
1 sibling, 0 replies; 12+ messages in thread
From: Rajesh Kumar Mallah @ 2003-10-30 11:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance; jesse@fsck.com
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder)
2003-10-29 19:22 PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
@ 2003-10-29 23:17 ` Christopher Browne <cbbrowne@libertyrms.info>
1 sibling, 0 replies; 12+ messages in thread
From: Christopher Browne @ 2003-10-29 23:17 UTC (permalink / raw)
To: pgsql-performance
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)
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2003-10-30 11:04 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-29 19:22 PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2003-10-29 19:45 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2003-10-29 19:51 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) Jesse <jesse@fsck.com>
2003-10-29 20:18 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2003-10-29 20:03 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) Sean Chittenden <sean@chittenden.org>
2003-10-29 20:22 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2003-10-29 21:51 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with scott.marlowe <scott.marlowe@ihs.com>
2003-10-29 22:17 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) mallah@trade-india.com
2003-10-29 23:23 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) Tom Lane <tgl@sss.pgh.pa.us>
2003-10-30 04:19 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) Greg Stark <gsstark@mit.edu>
2003-10-30 11:04 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) Rajesh Kumar Mallah <mallah@trade-india.com>
2003-10-29 23:17 ` Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) Christopher Browne <cbbrowne@libertyrms.info>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox