Help with optimizing a sql statement

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(dot)m(dot)guerrero(at)usit(dot)uio(dot)no>
Center for Information Technology Services
University of Oslo, Norway

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