I'm not big expert on optimizing Pg queries using indexes. Below you'll find suggestions for some.
But please run EXPLAIN for each query before creating any index and after. Without explains we can not help you precisely, just guessing. Also, please use some real constants in EXPLAIN, random strings and numbers will generate plans far from reality. On Fri, Jul 18, 2008 at 3:27 AM, Jessie Bryan <[EMAIL PROTECTED]> wrote: > On Wed, Jul 16, 2008 at 8:58 PM, Jessie Bryan <[EMAIL PROTECTED]> wrote: >> Update- >> >> Ok, after several hours of logging, what I see most common in the slow >> query (1s) log is: >> >> LOG: duration: 3151.786 ms statement: EXECUTE <unnamed> [PREPARE: >> SELECT * FROM Groups WHERE LOWER(Domain) = LOWER($1) AND LOWER(Type) >> = LOWER($2)] EXPLAIN ... CREATE INDEX RUZ_G1 ON Groups(LOWER(Domain), LOWER(Type)); EXPLAIN ... >> >> Several hundred of these queries in the slow query log data. >> > > Any suggestions - or more information I can provide? the slow query > log is pretty lengthy... > It's most common slow query is listed above, as well as many of these below: > > <2008-07-17 12:49:03 PDT> LOG: duration: 3737.479 ms statement: > EXECUTE <unnamed> [PREPARE: SELECT * FROM GroupMembers WHERE > GroupId = $1 AND MemberId = $2] EXPLAIN ... CREATE INDEX RUZ_GM1 ON GroupMembers(GroupId, MemberId); EXPLAIN ... > > <2008-07-17 17:20:43 PDT> LOG: duration: 8930.945 ms statement: > SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_4 JOIN > Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN > CachedGroupMembers CachedGroupMembers_2 ON ( > CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3 > ON ( Groups_3.id = CachedGroupMembers_2.GroupId ) WHERE > (Principals_1.Disabled = '0') AND (ACL_4.PrincipalType = > Groups_3.Type) AND (Principals_1.id != '1') AND > (Principals_1.PrincipalType = 'User') AND (ACL_4.RightName = > 'OwnTicket') AND (Groups_3.Domain = 'RT::System-Role') AND > ((ACL_4.ObjectType = 'RT::Queue') OR (ACL_4.ObjectType = > 'RT::System')) ORDER BY main.Name ASC Generate an explain, pretty hard to guess what's wrong. > <2008-07-17 13:42:06 PDT> LOG: duration: 9115.251 ms statement: > SELECT DISTINCT main.* FROM Tickets main CROSS JOIN Users Users_3 JOIN > Groups Groups_1 ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND ( > Groups_1.Type = 'Requestor' ) AND ( Groups_1.Instance = main.id ) JOIN > CachedGroupMembers CachedGroupMembers_2 ON ( > CachedGroupMembers_2.MemberId = Users_3.id ) AND ( > CachedGroupMembers_2.GroupId = Groups_1.id ) WHERE (Users_3.id = > '5698') AND (main.Status != 'deleted') AND ( ( CachedGroupMembers_2.id > IS NOT NULL ) AND ( main.Status = 'open' OR main.Status = 'new' ) ) > AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) ORDER BY > main.Priority DESC LIMIT 10 This one is request for "More about user XXX" box on the main page of a ticket. EXPLAIN ... CREATE INDEX RUZ_CGM1 ON CachedGroupMembers(MemberId, GroupId, Disabled); EXPLAIN ... [snip] > _______________________________________________ > http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users > > Community help: http://wiki.bestpractical.com > Commercial support: [EMAIL PROTECTED] > > > Discover RT's hidden secrets with RT Essentials from O'Reilly Media. > Buy a copy at http://rtbook.bestpractical.com > -- Best regards, Ruslan. _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
