Re: 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.
>
> 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