[rt-users] DBIx::SearchBuilder::Handle::Pg -- again
Kenneth Marshall
ktm at rice.edu
Tue Dec 23 18:00:39 EST 2008
Ruslan,
Here is a sample query that benefits from the change:
# EXPLAIN ANALYZE SELECT main.* FROM ( SELECT DISTINCT main.id FROM Users main CROSS JOIN ACL ACL_2 JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_3 ON ( CachedGroupMembers_3.MemberId = Principals_1.id ) WHERE (Principals_1.Disabled = '0') AND (ACL_2.PrincipalId = CachedGroupMembers_3.GroupId) AND (Principals_1.id != '1') AND (ACL_2.PrincipalType = 'Group') AND (Principals_1.PrincipalType = 'User') AND (ACL_2.RightName = 'OwnTicket') AND ((ACL_2.ObjectType = 'RT::Queue') OR (ACL_2.ObjectType = 'RT::System')) ) distinctquery, Users main WHERE (main.id = distinctquery.id) ORDER BY main.Name ASC;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1265.48..1265.60 rows=45 width=1179) (actual time=1112.411..1112.575 rows=277 loops=1)
Sort Key: main.name
Sort Method: quicksort Memory: 119kB
-> Nested Loop (cost=1094.91..1264.25 rows=45 width=1179) (actual time=1055.481..1110.575 rows=277 loops=1)
-> Unique (cost=1094.91..1095.14 rows=45 width=4) (actual time=1055.441..1107.382 rows=277 loops=1)
-> Sort (cost=1094.91..1095.03 rows=45 width=4) (actual time=1055.436..1081.169 rows=41094 loops=1)
Sort Key: main.id
Sort Method: quicksort Memory: 1988kB
-> Nested Loop (cost=2.76..1093.68 rows=45 width=4) (actual time=0.602..996.531 rows=41094 loops=1)
-> Nested Loop (cost=2.76..1079.73 rows=45 width=8) (actual time=0.533..601.817 rows=41094 loops=1)
-> Nested Loop (cost=2.76..544.72 rows=1636 width=4) (actual time=0.437..119.086 rows=41782 loops=1)
-> Bitmap Heap Scan on acl acl_2 (cost=2.76..37.09 rows=43 width=4) (actual time=0.302..0.950 rows=145 loops=1)
Recheck Cond: (((rightname)::text = 'OwnTicket'::text) AND ((principaltype)::text = 'Group'::text))
Filter: (((objecttype)::text = 'RT::Queue'::text) OR ((objecttype)::text = 'RT::System'::text))
-> Bitmap Index Scan on acl1 (cost=0.00..2.75 rows=49 width=0) (actual time=0.260..0.260 rows=145 loops=1)
Index Cond: (((rightname)::text = 'OwnTicket'::text) AND ((principaltype)::text = 'Group'::text))
-> Index Scan using cachedgroupmembers3 on cachedgroupmembers cachedgroupmembers_3 (cost=0.00..11.31 rows=40 width=8) (actual time=0.026..0.406 rows=288 loops=145)
Index Cond: (cachedgroupmembers_3.groupid = acl_2.principalid)
-> Index Scan using principals_pkey on principals principals_1 (cost=0.00..0.31 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=41782)
Index Cond: (principals_1.id = cachedgroupmembers_3.memberid)
Filter: ((principals_1.id <> 1) AND (principals_1.disabled = 0::smallint) AND ((principals_1.principaltype)::text = 'User'::text))
-> Index Scan using users_pkey on users main (cost=0.00..0.30 rows=1 width=4) (actual time=0.005..0.007 rows=1 loops=41094)
Index Cond: (main.id = principals_1.id)
-> Index Scan using users_pkey on users main (cost=0.00..3.74 rows=1 width=1179) (actual time=0.006..0.007 rows=1 loops=277)
Index Cond: (main.id = main.id)
Total runtime: 1113.705 ms
(26 rows)
# EXPLAIN ANALYZE SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_2 JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_3 ON ( CachedGroupMembers_3.MemberId = Principals_1.id ) WHERE (Principals_1.Disabled = '0') AND (ACL_2.PrincipalId = CachedGroupMembers_3.GroupId) AND (Principals_1.id != '1') AND (ACL_2.PrincipalType = 'Group') AND (Principals_1.PrincipalType = 'User') AND (ACL_2.RightName = 'OwnTicket') AND ((ACL_2.ObjectType = 'RT::Queue') OR (ACL_2.ObjectType = 'RT::System')) ORDER BY main.Name ASC;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=1094.91..1098.85 rows=45 width=1179) (actual time=2970.410..3136.157 rows=277 loops=1)
-> Sort (cost=1094.91..1095.03 rows=45 width=1179) (actual time=2970.405..2999.946 rows=41094 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
Sort Method: quicksort Memory: 16442kB
-> Nested Loop (cost=2.76..1093.68 rows=45 width=1179) (actual time=0.180..962.053 rows=41094 loops=1)
-> Nested Loop (cost=2.76..1079.73 rows=45 width=8) (actual time=0.165..553.454 rows=41094 loops=1)
-> Nested Loop (cost=2.76..544.72 rows=1636 width=4) (actual time=0.146..109.807 rows=41782 loops=1)
-> Bitmap Heap Scan on acl acl_2 (cost=2.76..37.09 rows=43 width=4) (actual time=0.102..0.565 rows=145 loops=1)
Recheck Cond: (((rightname)::text = 'OwnTicket'::text) AND ((principaltype)::text = 'Group'::text))
Filter: (((objecttype)::text = 'RT::Queue'::text) OR ((objecttype)::text = 'RT::System'::text))
-> Bitmap Index Scan on acl1 (cost=0.00..2.75 rows=49 width=0) (actual time=0.085..0.085 rows=145 loops=1)
Index Cond: (((rightname)::text = 'OwnTicket'::text) AND ((principaltype)::text = 'Group'::text))
-> Index Scan using cachedgroupmembers3 on cachedgroupmembers cachedgroupmembers_3 (cost=0.00..11.31 rows=40 width=8) (actual time=0.022..0.380 rows=288 loops=145)
Index Cond: (cachedgroupmembers_3.groupid = acl_2.principalid)
-> Index Scan using principals_pkey on principals principals_1 (cost=0.00..0.31 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=41782)
Index Cond: (principals_1.id = cachedgroupmembers_3.memberid)
Filter: ((principals_1.id <> 1) AND (principals_1.disabled = 0::smallint) AND ((principals_1.principaltype)::text = 'User'::text))
-> Index Scan using users_pkey on users main (cost=0.00..0.30 rows=1 width=1179) (actual time=0.005..0.006 rows=1 loops=41094)
Index Cond: (main.id = principals_1.id)
Total runtime: 3140.694 ms
(20 rows)
This was the problem query. Without the change, the page loads
take 3 secs and 1 second with. The only thing that I could track
down the difference to is that the "DISTINCT main.*" versus the
"DISTINCT main.id" was the amount of time needed for the sort.
Please let me know if you see another way to improve this. I
can pull some more samples if you need.
Regards,
Ken
On Wed, Dec 24, 2008 at 01:24:57AM +0300, Ruslan Zakirov wrote:
> * almost empty Pg DB
>
> On Wed, Dec 24, 2008 at 1:21 AM, Ruslan Zakirov
> <ruslan.zakirov at gmail.com> wrote:
> > We will be happy to improve Pg performance, however my simple tests on
> > almost Pg DB shows that new queries are slower. I'm comparing
> > execution plans at this point. Can you grab queries that benefit from
> > such change and send me execution plans with this patch and without.
> >
> > On Tue, Dec 23, 2008 at 6:43 PM, Kenneth Marshall <ktm at rice.edu> wrote:
> >> FYI,
> >>
> >> This change has not been made to DBIx::SearchBuilder::Handle::Pg
> >> for the definition of DistinctQuery. I just checked and you can
> >> simply use the same definition of DistinctQuery for PostgreSQL
> >> that you are using for Oracle in DBIx::SearchBuilder::Handle::Oracle.
> >> This make a substantial performance improvement for RT with a
> >> PostgreSQL backend database. It would be great if this change
> >> could be rolled into the next update to DBIx::SearchBuilder.
> >>
> >> Happy Holidays,
> >> Ken
> >>
> >> On Thu, Feb 07, 2008 at 08:21:39AM -0600, Kenneth Marshall wrote:
> >>> Dear DBIx::SearchBuilder developers:
> >>>
> >>> Here is a re-send of a message that I posted in January of 2007
> >>> regarding a change in the DistinctQuery handling for PostgreSQL.
> >>> Using the version from the Oracle definition is a big performance
> >>> win. Would it be possible to include this change in the next
> >>> update to DBIx::SearchBuilder?
> >>>
> >>> Cheers,
> >>> Ken
> >>>
> >>> ----- Forwarded message from Kenneth Marshall <ktm at rice.edu> -----
> >>>
> >>> Date: Tue, 30 Jan 2007 10:23:52 -0600
> >>> From: Kenneth Marshall <ktm at rice.edu>
> >>> To: rt-users at lists.bestpractical.com
> >>> Subject: [rt-users] DBIx::SearchBuilder::Handle::Pg
> >>>
> >>> Just an FYI. In preliminary testing, using the Handle::Oracle
> >>> definition for the DistinctQuery definition in Handle::Pg provides
> >>> quite a performance improvement. Here is the original line:
> >>>
> >>> $$statementref = "SELECT DISTINCT main.* FROM $$statementref";
> >>>
> >>> and the line from Handle::Oracle that should replace it:
> >>>
> >>> $$statementref = "SELECT main.* FROM ( SELECT DISTINCT main.id FROM $$statementref ) distinctquery, $table main WHERE (main.id = distinctquery.id) ";
> >>>
> >>> Ken Marshall
> >>> _______________________________________________
> >>> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> >>>
> >>> Community help: http://wiki.bestpractical.com
> >>> Commercial support: sales at bestpractical.com
> >>>
> >>> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> >>> Buy a copy at http://rtbook.bestpractical.com
> >>> ----- End forwarded message -----
> >>> _______________________________________________
> >>> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> >>>
> >>> Community help: http://wiki.bestpractical.com
> >>> Commercial support: sales at bestpractical.com
> >>>
> >>>
> >>> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> >>> Buy a copy at http://rtbook.bestpractical.com
> >>>
> >> _______________________________________________
> >> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> >>
> >> Community help: http://wiki.bestpractical.com
> >> Commercial support: sales at bestpractical.com
> >>
> >>
> >> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> >> Buy a copy at http://rtbook.bestpractical.com
> >>
> >
> >
> >
> > --
> > Best regards, Ruslan.
> >
>
>
>
> --
> Best regards, Ruslan.
>
More information about the rt-users
mailing list