[rt-users] DBIx::SearchBuilder::Handle::Pg -- again
Ruslan Zakirov
ruslan.zakirov at gmail.com
Tue Dec 23 19:17:58 EST 2008
I see how it can be improved without slowing more simple queries. Can
you test the following query for me? Send plan.
SELECT main.* FROM Users main
WHERE main.id IN (
SELECT 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'))
)
ORDER BY main.Name ASC;
On Wed, Dec 24, 2008 at 2:00 AM, Kenneth Marshall <ktm at rice.edu> wrote:
> 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.
>>
>
--
Best regards, Ruslan.
More information about the rt-users
mailing list