Help with optimizing a sql statement - Mailing list pgsql-performance

Hello

We are running an application via web that use a lot of time to perform
some operations. We are trying to find out if some of the sql statements
used are the reason of the slow speed.

We have identified a sql that takes like 4-5000ms more than the second
slowest sql in out test server. I hope that we will get some help to try
to optimize it.

Thanks in advance for any help.

Some information:
********************************************************************************
rttest=# EXPLAIN ANALYZE SELECT DISTINCT main.*

FROM Users main ,
     Principals Principals_1,
     ACL ACL_2,
     Groups Groups_3,
     CachedGroupMembers CachedGroupMembers_4

WHERE ((ACL_2.RightName = 'OwnTicket'))
AND ((CachedGroupMembers_4.MemberId = Principals_1.id))
AND ((Groups_3.id = CachedGroupMembers_4.GroupId))
AND ((Principals_1.Disabled = '0') or (Principals_1.Disabled = '0'))
AND ((Principals_1.id != '1'))
AND ((main.id = Principals_1.id))
AND  ( (    ACL_2.PrincipalId = Groups_3.id AND ACL_2.PrincipalType =
'Group' AND (   Groups_3.Domain = 'SystemInternal' OR Groups_3.Domain =
'UserDefined' OR Groups_3.Domain = 'ACLEquivalence')) OR ( (
(Groups_3.Domain = 'RT::Queue-Role' ) )  AND Groups_3.Type
=ACL_2.PrincipalType) )
AND (ACL_2.ObjectType = 'RT::System' OR (ACL_2.ObjectType = 'RT::Queue')
)

ORDER BY main.Name ASC

QUERY PLAN
-----------------------------------------------------------------
 Unique  (cost=28394.99..28395.16 rows=2 width=706) (actual
time=15574.272..15787.681 rows=254 loops=1)
   ->  Sort  (cost=28394.99..28394.99 rows=2 width=706) (actual
time=15574.267..15607.310 rows=22739 loops=1)
         Sort Key: main.name, main.id, main."password", main.comments,
main.signature, main.emailaddress, main.freeformcontactinfo,
main.organization, main.realname, main.nickname, main.lang,
main.emailencoding, main.webencoding, main.externalcontactinfoid,
main.contactinfosystem, main.externalauthid, main.authsystem,
main.gecos, main.homephone, main.workphone, main.mobilephone,
main.pagerphone, main.address1, main.address2, main.city, main.state,
main.zip, main.country, main.timezone, main.pgpkey, main.creator,
main.created, main.lastupdatedby, main.lastupdated
         ->  Nested Loop  (cost=20825.91..28394.98 rows=2 width=706)
(actual time=1882.608..14589.596 rows=22739 loops=1)
               Join Filter: (((("inner"."domain")::text =
'RT::Queue-Role'::text) OR ("outer".principalid = "inner".id)) AND
((("inner"."type")::text = ("outer".principaltype)::text) OR
("outer".principalid = "inner".id)) AND ((("inner"."domain")::text =
'RT::Queue-Role'::text) OR (("outer".principaltype)::text =
'Group'::text)) AND ((("inner"."type")::text =
("outer".principaltype)::text) OR (("outer".principaltype)::text =
'Group'::text)) AND ((("inner"."type")::text =
("outer".principaltype)::text) OR (("inner"."domain")::text =
'SystemInternal'::text) OR (("inner"."domain")::text =
'UserDefined'::text) OR (("inner"."domain")::text =
'ACLEquivalence'::text)))
               ->  Seq Scan on acl acl_2  (cost=0.00..40.57 rows=45
width=13) (actual time=0.020..1.730 rows=51 loops=1)
                     Filter: (((rightname)::text = 'OwnTicket'::text)
AND (((objecttype)::text = 'RT::System'::text) OR ((objecttype)::text =
'RT::Queue'::text)))
               ->  Materialize  (cost=20825.91..20859.37 rows=3346
width=738) (actual time=36.925..166.374 rows=66823 loops=51)
                     ->  Merge Join  (cost=15259.56..20825.91 rows=3346
width=738) (actual time=1882.539..3538.258 rows=66823 loops=1)
                           Merge Cond: ("outer".id = "inner".memberid)
                           ->  Merge Join  (cost=0.00..5320.37
rows=13182 width=710) (actual time=0.116..874.960 rows=13167 loops=1)
                                 Merge Cond: ("outer".id = "inner".id)
                                 ->  Index Scan using users_pkey on
users main  (cost=0.00..1063.60 rows=13181 width=706) (actual
time=0.032..52.355 rows=13181 loops=1)
                                 ->  Index Scan using principals_pkey on
principals principals_1  (cost=0.00..3737.49 rows=141801 width=4)
(actual time=0.020..463.043 rows=141778 loops=1)
                                       Filter: ((disabled = 0::smallint)
AND (id <> 1))
                           ->  Sort  (cost=15259.56..15349.54 rows=35994
width=36) (actual time=1882.343..1988.353 rows=80357 loops=1)
                                 Sort Key: cachedgroupmembers_4.memberid
                                 ->  Hash Join  (cost=3568.51..12535.63
rows=35994 width=36) (actual time=96.151..1401.537 rows=80357 loops=1)
                                       Hash Cond: ("outer".groupid =
"inner".id)
                                       ->  Seq Scan on
cachedgroupmembers cachedgroupmembers_4  (cost=0.00..5961.53 rows=352753
width=8) (actual time=0.011..500.508 rows=352753 loops=1)
                                       ->  Hash  (cost=3535.70..3535.70
rows=13124 width=32) (actual time=95.966..95.966 rows=0 loops=1)
                                             ->  Index Scan using
groups1, groups1, groups1, groups1 on groups groups_3
(cost=0.00..3535.70 rows=13124 width=32) (actual time=0.045..76.506
rows=13440 loops=1)
                                                   Index Cond:
((("domain")::text = 'RT::Queue-Role'::text) OR (("domain")::text =
'SystemInternal'::text) OR (("domain")::text = 'UserDefined'::text) OR
(("domain")::text = 'ACLEquivalence'::text))

 Total runtime: 15825.022 ms

********************************************************************************
rttest=# \d users
                                        Table "public.users"
        Column         |            Type             |
Modifiers
-----------------------+-----------------------------+------------------------------------------------
 id                    | integer                     | not null default
nextval('users_id_seq'::text)
 name                  | character varying(200)      | not null
 password              | character varying(40)       |
 comments              | text                        |
 signature             | text                        |
 emailaddress          | character varying(120)      |
 freeformcontactinfo   | text                        |
 organization          | character varying(200)      |
 realname              | character varying(120)      |
 nickname              | character varying(16)       |
 lang                  | character varying(16)       |
 emailencoding         | character varying(16)       |
 webencoding           | character varying(16)       |
 externalcontactinfoid | character varying(100)      |
 contactinfosystem     | character varying(30)       |
 externalauthid        | character varying(100)      |
 authsystem            | character varying(30)       |
 gecos                 | character varying(16)       |
 homephone             | character varying(30)       |
 workphone             | character varying(30)       |
 mobilephone           | character varying(30)       |
 pagerphone            | character varying(30)       |
 address1              | character varying(200)      |
 address2              | character varying(200)      |
 city                  | character varying(100)      |
 state                 | character varying(100)      |
 zip                   | character varying(16)       |
 country               | character varying(50)       |
 timezone              | character varying(50)       |
 pgpkey                | text                        |
 creator               | integer                     | not null default
0
 created               | timestamp without time zone |
 lastupdatedby         | integer                     | not null default
0
 lastupdated           | timestamp without time zone |
Indexes:
    "users_pkey" primary key, btree (id)
    "users1" unique, btree (name)
    "users2" btree (name)
    "users3" btree (id, emailaddress)
    "users4" btree (emailaddress)
********************************************************************************
rttest=# \d principals

                                  Table "public.principals"
    Column     |         Type          |
Modifiers
---------------+-----------------------+----------------------------------------
 id            | integer               | not null default
nextval('principals_id_seq'::text)
 principaltype | character varying(16) | not null
 objectid      | integer               |
 disabled      | smallint              | not null default 0
Indexes:
    "principals_pkey" primary key, btree (id)
    "principals2" btree (objectid)

********************************************************************************
rttest=# \d acl

                                  Table "public.acl"
    Column     |         Type          |
Modifiers
---------------+-----------------------+----------------------------------------------
 id            | integer               | not null default
nextval('acl_id_seq'::text)
 principaltype | character varying(25) | not null
 principalid   | integer               | not null
 rightname     | character varying(25) | not null
 objecttype    | character varying(25) | not null
 objectid      | integer               | not null default 0
 delegatedby   | integer               | not null default 0
 delegatedfrom | integer               | not null default 0
Indexes:
    "acl_pkey" primary key, btree (id)
    "acl1" btree (rightname, objecttype, objectid, principaltype,
principalid)


********************************************************************************
rttest=# \d groups

                                 Table "public.groups"
   Column    |          Type          |
Modifiers
-------------+------------------------+-------------------------------------------------
 id          | integer                | not null default
nextval('groups_id_seq'::text)
 name        | character varying(200) |
 description | character varying(255) |
 domain      | character varying(64)  |
 type        | character varying(64)  |
 instance    | integer                |
Indexes:
    "groups_pkey" primary key, btree (id)
    "groups1" unique, btree ("domain", instance, "type", id, name)
    "groups2" btree ("type", instance, "domain")


********************************************************************************
 rttest=# \d  cachedgroupmembers"

                          Table "public.cachedgroupmembers"
      Column       |   Type   |
Modifiers
-------------------+----------+-------------------------------------------------------------
 id                | integer  | not null default
nextval('cachedgroupmembers_id_seq'::text)
 groupid           | integer  |
 memberid          | integer  |
 via               | integer  |
 immediateparentid | integer  |
 disabled          | smallint | not null default 0
Indexes:
    "cachedgroupmembers_pkey" primary key, btree (id)
    "cachedgroupmembers2" btree (memberid)
    "cachedgroupmembers3" btree (groupid)
    "disgroumem" btree (groupid, memberid, disabled)


********************************************************************************

--
Rafael Martinez, <r.m.guerrero@usit.uio.no>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/