Ruslan,
Here are the results for this query:
=# EXPLAIN ANALYZE 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;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1263.60..1263.60 rows=1 width=1179) (actual time=956.963..957.124
rows=277 loops=1)
Sort Key: main.name
Sort Method: quicksort Memory: 119kB
-> Nested Loop (cost=1094.48..1263.59 rows=1 width=1179) (actual
time=952.019..955.245 rows=277 loops=1)
-> HashAggregate (cost=1094.48..1094.93 rows=45 width=12) (actual
time=952.005..952.276 rows=277 loops=1)
-> Nested Loop (cost=2.76..1094.37 rows=45 width=12) (actual
time=0.403..907.237 rows=41094 loops=1)
-> Nested Loop (cost=2.76..1080.42 rows=45 width=8)
(actual time=0.363..546.328 rows=41094 loops=1)
-> Nested Loop (cost=2.76..544.73 rows=1638
width=4) (actual time=0.311..109.119 rows=41782 loops=1)
-> Bitmap Heap Scan on acl acl_2
(cost=2.76..37.09 rows=43 width=4) (actual time=0.234..0.831 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.198..0.198 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.023..0.374 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=4) (actual time=0.005..0.006 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 = principals_1.id)
Total runtime: 957.753 ms
(23 rows)
Here are the timing results without EXPLAIN ANALYZE:
...
(277 rows)
Time: 562.191 ms
Happy holidays,
Ken
On Wed, Dec 24, 2008 at 03:17:58AM +0300, Ruslan Zakirov wrote:
> 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 <[email protected]> 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
> >> <[email protected]> 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 <[email protected]> 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 <[email protected]> -----
> >> >>>
> >> >>> Date: Tue, 30 Jan 2007 10:23:52 -0600
> >> >>> From: Kenneth Marshall <[email protected]>
> >> >>> To: [email protected]
> >> >>> 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: [email protected]
> >> >>>
> >> >>> 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: [email protected]
> >> >>>
> >> >>>
> >> >>> 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: [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.
> >> >
> >>
> >>
> >>
> >> --
> >> Best regards, Ruslan.
> >>
> >
>
>
>
> --
> 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
