Thread: 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.m.guerrero@usit.uio.no>
Center for Information Technology Services
University of Oslo, Norway
PGP Public Key: http://folk.uio.no/rafael/
At least part of the problem is that it's way off on some of the row
estimates. I'd suggest upping the statisticss target on at least all of
the join columns to at least 100. (Note that it's doing a nested loop
thinking it will have only 2 rows but it actually has 22000 rows).
On Thu, Feb 09, 2006 at 04:10:27PM +0100, Rafael Martinez Guerrero wrote:
> 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/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
First I'm wondering if the tables have been recently analyzed. If an analyze has been run recently, then it is probably a good idea to look at the statistics target. -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Jim C. Nasby Sent: Thursday, February 09, 2006 1:34 PM To: Rafael Martinez Guerrero Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Help with optimizing a sql statement At least part of the problem is that it's way off on some of the row estimates. I'd suggest upping the statisticss target on at least all of the join columns to at least 100. (Note that it's doing a nested loop thinking it will have only 2 rows but it actually has 22000 rows). On Thu, Feb 09, 2006 at 04:10:27PM +0100, Rafael Martinez Guerrero wrote: > 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. > [Snip]
I looked at the estimates for the table access methods and they all looked ok, so I think the statistics are pretty up-to-date; there just aren't enough of them for the planner to do a good job. On Thu, Feb 09, 2006 at 01:44:22PM -0600, Dave Dutcher wrote: > First I'm wondering if the tables have been recently analyzed. If an > analyze has been run recently, then it is probably a good idea to look > at the statistics target. > > > -----Original Message----- > From: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Jim C. > Nasby > Sent: Thursday, February 09, 2006 1:34 PM > To: Rafael Martinez Guerrero > Cc: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Help with optimizing a sql statement > > At least part of the problem is that it's way off on some of the row > estimates. I'd suggest upping the statisticss target on at least all of > the join columns to at least 100. (Note that it's doing a nested loop > thinking it will have only 2 rows but it actually has 22000 rows). > > On Thu, Feb 09, 2006 at 04:10:27PM +0100, Rafael Martinez Guerrero > wrote: > > 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. > > > [Snip] > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Thu, 2006-02-09 at 13:46 -0600, Jim C. Nasby wrote: > I looked at the estimates for the table access methods and they all > looked ok, so I think the statistics are pretty up-to-date; there just > aren't enough of them for the planner to do a good job. > VACUUM ANALYZE runs 4 times every hour, so yes, statistics are up-to-date. I will increase default_statistics_target tomorrow at work and see what happens. Thanks for your help. -- 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/
Rafael Martinez Guerrero <r.m.guerrero@usit.uio.no> writes:
> 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')
> )
Are you sure this WHERE clause really expresses your intent? It seems
awfully oddly constructed. Removing the redundant parens and clarifying
the layout, I get
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')
That next-to-last major AND clause seems a rather unholy mix of join and
restriction clauses; I wonder if it's not buggy in itself. If it is
correct, I think most of the performance problem comes from the fact
that the planner can't break it down into independent clauses. You
might try getting rid of the central OR in favor of doing a UNION of
two queries that comprise all the other terms. More repetitious, but
would likely perform better.
BTW, what PG version is this? It looks to me like it's doing some
manipulations of the WHERE clause that we got rid of a couple years ago.
If this is 7.4 or older then you really ought to be thinking about an
update.
regards, tom lane
On Thu, 2006-02-09 at 18:22 -0500, Tom Lane wrote: > Rafael Martinez Guerrero <r.m.guerrero@usit.uio.no> writes: > > 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') > > ) > > Are you sure this WHERE clause really expresses your intent? It seems > awfully oddly constructed. Removing the redundant parens and clarifying > the layout, I get > [............] This is an application that we have not programmed, so I am not sure what they are trying to do here. I will contact the developers. Tomorrow I will try to test some of your suggestions. > BTW, what PG version is this? It looks to me like it's doing some > manipulations of the WHERE clause that we got rid of a couple years ago. > If this is 7.4 or older then you really ought to be thinking about an > update. > We are running 7.4.8 in this server and will upgrade to 8.0.6 in a few weeks. Thanks. -- 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/
On Feb 9, 2006, at 6:36 PM, Rafael Martinez wrote: > This is an application that we have not programmed, so I am not sure > what they are trying to do here. I will contact the developers. > Tomorrow > I will try to test some of your suggestions. well, obviously you're running RT... what you want to do is update all your software to the latest versions. in particular update RT to 3.4.5 and all the dependent modules to their latest. We run with Pg 8.0 which is plenty fast. one of these days I'll update to 8.1 but need to test it out first. i'm not sure how much RT has been tested against 8.1
Tom Lane <tgl@sss.pgh.pa.us> writes: > Are you sure this WHERE clause really expresses your intent? It seems > awfully oddly constructed. Removing the redundant parens and clarifying > the layout, I get ... > That next-to-last major AND clause seems a rather unholy mix of join and > restriction clauses; I wonder if it's not buggy in itself. FYI RT uses a perl module called SearchBuilder which constructs these queries dynamically. So he's probably not really free to fiddle with the query all he wants. At the very least I would suggest checking the changelog for SearchBuilder for more recent versions. There have been a lot of tweaks for working with Postgres. In the past it really only worked properly with MySQL. -- greg