[rt-devel] Postgres performance.
David Wheeler
david at kineticode.com
Mon Apr 7 16:37:15 EDT 2003
> Can you post (or send me) the results of "explain analyze" on this
and the
> optimized query?
I can. Attached.
David
--
David Wheeler AIM: dwTheory
david at kineticode.com ICQ: 15726394
Yahoo!: dew7e
Jabber: Theory at jabber.org
Kineticode. Setting knowledge in motion.[sm]
-------------- next part --------------
Unoptimized:
===========
rt3=# explain analyze SELECT DISTINCT main.*
rt3-# FROM Users main,
rt3-# Principals Principals_1,
rt3-# Groups Groups_2,
rt3-# Principals Principals_3,
rt3-# Principals Principals_4,
rt3-# ACL ACL_5,
rt3-# CachedGroupMembers CachedGroupMembers_6,
rt3-# CachedGroupMembers CachedGroupMembers_7
rt3-# WHERE ((ACL_5.RightName = 'SuperUser')
rt3(# OR (ACL_5.RightName = 'OwnTicket'))
rt3-# AND CachedGroupMembers_7.GroupId = '4'
rt3-# AND Principals_1.Disabled = '0'
rt3-# AND Principals_1.PrincipalType = 'User'
rt3-# AND Principals_3.PrincipalType = 'User'
rt3-# AND (ACL_5.ObjectType = 'RT::System'
rt3(# OR (ACL_5.ObjectType = 'RT::Queue'
rt3(# AND ACL_5.ObjectId = '14'))
rt3-# AND ((ACL_5.PrincipalId = Principals_4.Id
rt3(# AND Principals_4.id = Groups_2.Id
rt3(# AND ACL_5.PrincipalType = 'Group'
rt3(# AND (Groups_2.Domain = 'SystemInternal'
rt3(# OR Groups_2.Domain = 'UserDefined'
rt3(# OR Groups_2.Domain = 'ACLEquivalence'))
rt3(# OR ( ( (Groups_2.Domain = 'RT::Queue-Role'
rt3(# AND Groups_2.Instance = '14')
rt3(# OR (Groups_2.Domain = 'RT::Ticket-Role'
rt3(# AND Groups_2.Instance = '135'))
rt3(# AND Groups_2.Type = ACL_5.PrincipalType
rt3(# AND Groups_2.Id = Principals_4.id
rt3(# AND Principals_4.PrincipalType = 'Group'))
rt3-# AND main.id = Principals_1.id
rt3-# AND Principals_1.id = CachedGroupMembers_7.MemberId
rt3-# AND main.id = Principals_3.id
rt3-# AND CachedGroupMembers_6.MemberId = Principals_3.Id
rt3-# AND CachedGroupMembers_6.GroupId = Principals_4.Id
rt3-# ORDER BY main.Name ASC;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=39.32..39.42 rows=1 width=828) (actual time=18518.22..18519.49 rows=7 loops=1)
-> Sort (cost=39.32..39.32 rows=1 width=828) (actual time=18518.21..18518.24 rows=16 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=4.03..39.31 rows=1 width=828) (actual time=274.01..18515.01 rows=16 loops=1)
Join Filter: ((("outer".id = "inner".id) OR ("inner"."domain" = 'RT::Ticket-Role'::character varying) OR ("inner"."domain" = 'RT::Queue-Role'::character varying)) AND (("outer".id = "inner".id) OR ("inner".instance = '135'::character varying) OR ("inner"."domain" = 'RT::Queue-Role'::character varying)) AND (("outer".id = "inner".id) OR ("inner"."domain" = 'RT::Ticket-Role'::character varying) OR ("inner".instance = '14'::character varying)) AND (("outer".id = "inner".id) OR ("inner".instance = '135'::character varying) OR ("inner".instance = '14'::character varying)) AND (("outer".id = "inner".id) OR ("inner".id = "outer".id)) AND (("inner"."domain" = 'SystemInternal'::character varying) OR ("inner"."domain" = 'UserDefined'::character varying) OR ("inner"."domain" = 'ACLEquivalence'::character varying) OR ("inner".id = "outer".id)) AND (("outer".id = "inner".id) OR ("outer".principaltype = 'Group'::character varying)) AND (("inner"."domain" = 'SystemInternal'::character varying) OR ("inner"."domain" = 'UserDefined'::character varying) OR ("inner"."domain" = 'ACLEquivalence'::character varying) OR ("outer".principaltype = 'Group'::character varying)) AND (("outer".principalid = "outer".id) OR ("inner"."domain" = 'RT::Ticket-Role'::character varying) OR ("inner"."domain" = 'RT::Queue-Role'::character varying)) AND (("outer".principalid = "outer".id) OR ("inner".instance = '135'::character varying) OR ("inner"."domain" = 'RT::Queue-Role'::character varying)) AND (("outer".principalid = "outer".id) OR ("inner"."domain" = 'RT::Ticket-Role'::character varying) OR ("inner".instance = '14'::character varying)) AND (("outer".principalid = "outer".id) OR ("inner".instance = '135'::character varying) OR ("inner".instance = '14'::character varying)) AND (("outer".principalid = "outer".id) OR ("inner"."type" = "outer".principaltype)) AND (("outer".id = "inner".id) OR ("inner"."type" = "outer".principaltype)) AND (("outer".principalid = "outer".id) OR ("inner".id = "outer".id)) AND (("outer".principaltype = 'Group'::character varying) OR ("inner".id = "outer".id)) AND (("outer".principaltype = 'Group'::character varying) OR ("inner"."domain" = 'RT::Ticket-Role'::character varying) OR ("inner"."domain" = 'RT::Queue-Role'::character varying)) AND (("outer".principaltype = 'Group'::character varying) OR ("inner".instance = '135'::character varying) OR ("inner"."domain" = 'RT::Queue-Role'::character varying)) AND (("outer".principaltype = 'Group'::character varying) OR ("inner"."domain" = 'RT::Ticket-Role'::character varying) OR ("inner".instance = '14'::character varying)) AND (("outer".principaltype = 'Group'::character varying) OR ("inner".instance = '135'::character varying) OR ("inner".instance = '14'::character varying)) AND (("outer".principaltype = 'Group'::character varying) OR ("inner"."type" = "outer".principaltype)) AND (("inner"."domain" = 'SystemInternal'::character varying) OR ("inner"."domain" = 'UserDefined'::character varying) OR ("inner"."domain" = 'ACLEquivalence'::character varying) OR ("inner"."type" = "outer".principaltype)))
-> Nested Loop (cost=4.03..27.88 rows=1 width=791) (actual time=2.24..619.46 rows=3200 loops=1)
Join Filter: ((("inner".principalid = "outer".id) OR ("outer".principaltype = 'Group'::character varying)) AND (("inner".principaltype = 'Group'::character varying) OR ("outer".principaltype = 'Group'::character varying)))
-> Nested Loop (cost=4.03..24.80 rows=1 width=778) (actual time=2.04..90.53 rows=320 loops=1)
-> Nested Loop (cost=4.03..21.19 rows=1 width=766) (actual time=1.94..60.55 rows=320 loops=1)
-> Nested Loop (cost=4.03..17.57 rows=1 width=762) (actual time=1.79..26.90 rows=320 loops=1)
Join Filter: ("inner".memberid = "outer".id)
-> Nested Loop (cost=4.03..10.52 rows=1 width=754) (actual time=1.68..4.03 rows=16 loops=1)
-> Hash Join (cost=4.03..5.21 rows=1 width=750) (actual time=1.28..2.31 rows=10 loops=1)
Hash Cond: ("outer".id = "inner".id)
-> Seq Scan on users main (cost=0.00..1.10 rows=10 width=746) (actual time=0.05..0.53 rows=10 loops=1)
-> Hash (cost=4.00..4.00 rows=10 width=4) (actual time=0.76..0.76 rows=0 loops=1)
-> Seq Scan on principals principals_3 (cost=0.00..4.00 rows=10 width=4) (actual time=0.08..0.72 rows=10 loops=1)
Filter: (principaltype = 'User'::character varying)
-> Index Scan using disgroumem on cachedgroupmembers cachedgroupmembers_7 (cost=0.00..5.30 rows=1 width=4) (actual time=0.05..0.07 rows=2 loops=10)
Index Cond: ((cachedgroupmembers_7.groupid = 4) AND ("outer".id = cachedgroupmembers_7.memberid))
-> Index Scan using cachedgroupmembers2 on cachedgroupmembers cachedgroupmembers_6 (cost=0.00..7.03 rows=2 width=8) (actual time=0.03..0.38 rows=20 loops=16)
Index Cond: (cachedgroupmembers_6.memberid = "outer".memberid)
-> Index Scan using principals_pkey on principals principals_1 (cost=0.00..3.60 rows=1 width=4) (actual time=0.04..0.05 rows=1 loops=320)
Index Cond: ("outer".id = principals_1.id)
Filter: ((disabled = 0::smallint) AND (principaltype = 'User'::character varying))
-> Index Scan using principals_pkey on principals principals_4 (cost=0.00..3.60 rows=1 width=12) (actual time=0.03..0.03 rows=1 loops=320)
Index Cond: ("outer".groupid = principals_4.id)
-> Seq Scan on acl acl_5 (cost=0.00..2.90 rows=9 width=13) (actual time=0.06..0.87 rows=10 loops=320)
Filter: (((rightname = 'SuperUser'::character varying) OR (rightname = 'OwnTicket'::character varying)) AND ((objecttype = 'RT::Queue'::character varying) OR (objecttype = 'RT::System'::character varying)) AND ((objectid = 14) OR (objecttype = 'RT::System'::character varying)))
-> Seq Scan on groups groups_2 (cost=0.00..11.00 rows=3 width=37) (actual time=0.10..5.45 rows=14 loops=3200)
Filter: ((("domain" = 'SystemInternal'::character varying) OR ("domain" = 'UserDefined'::character varying) OR ("domain" = 'ACLEquivalence'::character varying) OR ("domain" = 'RT::Ticket-Role'::character varying) OR ("domain" = 'RT::Queue-Role'::character varying)) AND (("domain" = 'SystemInternal'::character varying) OR ("domain" = 'UserDefined'::character varying) OR ("domain" = 'ACLEquivalence'::character varying) OR (instance = '135'::character varying) OR ("domain" = 'RT::Queue-Role'::character varying)) AND (("domain" = 'SystemInternal'::character varying) OR ("domain" = 'UserDefined'::character varying) OR ("domain" = 'ACLEquivalence'::character varying) OR ("domain" = 'RT::Ticket-Role'::character varying) OR (instance = '14'::character varying)) AND (("domain" = 'SystemInternal'::character varying) OR ("domain" = 'UserDefined'::character varying) OR ("domain" = 'ACLEquivalence'::character varying) OR (instance = '135'::character varying) OR (instance = '14'::character varying)))
Total runtime: 18524.32 msec
(32 rows)
Optimized:
=========
rt3=# explain analyze SELECT DISTINCT main.*
rt3-# FROM ((((((Users main join Principals Principals_1 on main.id =
rt3(# Principals_1.id)
rt3(# join CachedGroupMembers CachedGroupMembers_7 on
rt3(# Principals_1.id = CachedGroupMembers_7.MemberId)
rt3(# join Principals Principals_3 on Principals_1.id =
rt3(# Principals_3.id)
rt3(# join CachedGroupMembers CachedGroupMembers_6 on
rt3(# CachedGroupMembers_6.MemberId = Principals_3.Id)
rt3(# join Principals Principals_4 on CachedGroupMembers_6.GroupId =
rt3(# Principals_4.Id)
rt3(# join Groups Groups_2 on Principals_4.id = Groups_2.Id),
rt3-# ACL ACL_5
rt3-# WHERE ( (ACL_5.RightName = 'SuperUser')
rt3(# OR (ACL_5.RightName = 'OwnTicket'))
rt3-# AND CachedGroupMembers_7.GroupId = '4'
rt3-# AND Principals_1.Disabled = '0'
rt3-# AND Principals_1.PrincipalType = 'User'
rt3-# AND Principals_3.PrincipalType = 'User'
rt3-# AND (ACL_5.ObjectType = 'RT::System'
rt3(# OR (ACL_5.ObjectType = 'RT::Queue' AND ACL_5.ObjectId =
rt3(# '14'))
rt3-#
rt3-# AND ( (ACL_5.PrincipalId = Principals_4.Id
rt3(# AND ACL_5.PrincipalType = 'Group'
rt3(# AND (Groups_2.Domain = 'SystemInternal'
rt3(# OR Groups_2.Domain = 'UserDefined'
rt3(# OR Groups_2.Domain = 'ACLEquivalence'))
rt3(#
rt3(# OR ( ( (Groups_2.Domain = 'RT::Queue-Role'
rt3(# AND Groups_2.Instance = '14')
rt3(# OR (Groups_2.Domain = 'RT::Ticket-Role'
rt3(# AND Groups_2.Instance = '135'))
rt3(# AND Groups_2.Type = ACL_5.PrincipalType
rt3(# AND Principals_4.PrincipalType = 'Group'))
rt3-# ORDER BY main.Name ASC;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=36.13..36.22 rows=1 width=828) (actual time=166.71..167.97 rows=7 loops=1)
-> Sort (cost=36.13..36.14 rows=1 width=828) (actual time=166.70..166.72 rows=16 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=21.59..36.12 rows=1 width=828) (actual time=116.54..162.88 rows=16 loops=1)
Join Filter: ((("inner".principalid = "outer".id) OR ("outer"."domain" = 'RT::Ticket-Role'::character varying) OR ("outer"."domain" = 'RT::Queue-Role'::character varying)) AND (("inner".principalid = "outer".id) OR ("outer".instance = '135'::character varying) OR ("outer"."domain" = 'RT::Queue-Role'::character varying)) AND (("inner".principalid = "outer".id) OR ("outer"."domain" = 'RT::Ticket-Role'::character varying) OR ("outer".instance = '14'::character varying)) AND (("inner".principalid = "outer".id) OR ("outer".instance = '135'::character varying) OR ("outer".instance = '14'::character varying)) AND (("inner".principalid = "outer".id) OR ("outer"."type" = "inner".principaltype)) AND (("inner".principalid = "outer".id) OR ("outer".principaltype = 'Group'::character varying)) AND (("inner".principaltype = 'Group'::character varying) OR ("outer".principaltype = 'Group'::character varying)) AND (("inner".principaltype = 'Group'::character varying) OR ("outer"."domain" = 'RT::Ticket-Role'::character varying) OR ("outer"."domain" = 'RT::Queue-Role'::character varying)) AND (("inner".principaltype = 'Group'::character varying) OR ("outer".instance = '135'::character varying) OR ("outer"."domain" = 'RT::Queue-Role'::character varying)) AND (("inner".principaltype = 'Group'::character varying) OR ("outer"."domain" = 'RT::Ticket-Role'::character varying) OR ("outer".instance = '14'::character varying)) AND (("inner".principaltype = 'Group'::character varying) OR ("outer".instance = '135'::character varying) OR ("outer".instance = '14'::character varying)) AND (("inner".principaltype = 'Group'::character varying) OR ("outer"."type" = "inner".principaltype)) AND (("outer"."domain" = 'SystemInternal'::character varying) OR ("outer"."domain" = 'UserDefined'::character varying) OR ("outer"."domain" = 'ACLEquivalence'::character varying) OR ("outer"."type" = "inner".principaltype)))
-> Nested Loop (cost=21.59..32.32 rows=1 width=815) (actual time=28.58..80.48 rows=80 loops=1)
Join Filter: (("inner"."domain" = 'SystemInternal'::character varying) OR ("inner"."domain" = 'UserDefined'::character varying) OR ("inner"."domain" = 'ACLEquivalence'::character varying) OR ("outer".principaltype = 'Group'::character varying))
-> Hash Join (cost=21.59..26.01 rows=2 width=778) (actual time=27.13..48.35 rows=320 loops=1)
Hash Cond: ("outer".id = "inner".groupid)
-> Seq Scan on principals principals_4 (cost=0.00..3.60 rows=160 width=12) (actual time=0.02..1.32 rows=160 loops=1)
-> Hash (cost=21.58..21.58 rows=2 width=766) (actual time=26.81..26.81 rows=0 loops=1)
-> Nested Loop (cost=4.43..21.58 rows=2 width=766) (actual time=2.17..24.15 rows=320 loops=1)
-> Nested Loop (cost=4.43..14.53 rows=1 width=758) (actual time=2.05..5.30 rows=16 loops=1)
-> Nested Loop (cost=4.43..10.92 rows=1 width=754) (actual time=1.92..3.72 rows=16 loops=1)
-> Hash Join (cost=4.43..5.61 rows=1 width=750) (actual time=1.52..2.35 rows=10 loops=1)
Hash Cond: ("outer".id = "inner".id)
-> Seq Scan on users main (cost=0.00..1.10 rows=10 width=746) (actual time=0.07..0.47 rows=10 loops=1)
-> Hash (cost=4.40..4.40 rows=10 width=4) (actual time=1.04..1.04 rows=0 loops=1)
-> Seq Scan on principals principals_1 (cost=0.00..4.40 rows=10 width=4) (actual time=0.09..0.99 rows=10 loops=1)
Filter: ((disabled = 0::smallint) AND (principaltype = 'User'::character varying))
-> Index Scan using disgroumem on cachedgroupmembers cachedgroupmembers_7 (cost=0.00..5.30 rows=1 width=4) (actual time=0.04..0.06 rows=2 loops=10)
Index Cond: ((cachedgroupmembers_7.groupid = 4) AND (cachedgroupmembers_7.memberid = "outer".id))
-> Index Scan using principals_pkey on principals principals_3 (cost=0.00..3.60 rows=1 width=4) (actual time=0.04..0.04 rows=1 loops=16)
Index Cond: (principals_3.id = "outer".id)
Filter: (principaltype = 'User'::character varying)
-> Index Scan using cachedgroupmembers2 on cachedgroupmembers cachedgroupmembers_6 (cost=0.00..7.03 rows=2 width=8) (actual time=0.03..0.28 rows=20 loops=16)
Index Cond: (cachedgroupmembers_6.memberid = "outer".id)
-> Index Scan using groups_pkey on groups groups_2 (cost=0.00..3.17 rows=1 width=37) (actual time=0.07..0.07 rows=0 loops=320)
Index Cond: (groups_2.id = "outer".groupid)
Filter: ((("domain" = 'SystemInternal'::character varying) OR ("domain" = 'UserDefined'::character varying) OR ("domain" = 'ACLEquivalence'::character varying) OR ("domain" = 'RT::Ticket-Role'::character varying) OR ("domain" = 'RT::Queue-Role'::character varying)) AND (("domain" = 'SystemInternal'::character varying) OR ("domain" = 'UserDefined'::character varying) OR ("domain" = 'ACLEquivalence'::character varying) OR (instance = '135'::character varying) OR ("domain" = 'RT::Queue-Role'::character varying)) AND (("domain" = 'SystemInternal'::character varying) OR ("domain" = 'UserDefined'::character varying) OR ("domain" = 'ACLEquivalence'::character varying) OR ("domain" = 'RT::Ticket-Role'::character varying) OR (instance = '14'::character varying)) AND (("domain" = 'SystemInternal'::character varying) OR ("domain" = 'UserDefined'::character varying) OR ("domain" = 'ACLEquivalence'::character varying) OR (instance = '135'::character varying) OR (instance = '14'::character varying)))
-> Seq Scan on acl acl_5 (cost=0.00..2.90 rows=9 width=13) (actual time=0.06..0.85 rows=10 loops=80)
Filter: (((rightname = 'SuperUser'::character varying) OR (rightname = 'OwnTicket'::character varying)) AND ((objecttype = 'RT::Queue'::character varying) OR (objecttype = 'RT::System'::character varying)) AND ((objectid = 14) OR (objecttype = 'RT::System'::character varying)))
Total runtime: 173.20 msec
(33 rows)
More information about the Rt-devel
mailing list