Re: PostgreSQL 7.4beta5 vs MySQL 4.0.16 with RT(DBIx::SearchBuilder) - Mailing list pgsql-performance

>> 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/