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,
KenOn Wed, Dec 24, 2008 at 01:24:57AM +0300, Ruslan Zakirov wrote:
On Wed, Dec 24, 2008 at 1:21 AM, Ruslan Zakirov ruslan.zakirov@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@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@rice.edu -----
Date: Tue, 30 Jan 2007 10:23:52 -0600
From: Kenneth Marshall ktm@rice.edu
To: rt-users@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
The rt-users Archives
Community help: http://wiki.bestpractical.com
Commercial support: sales@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 -----
The rt-users Archives
Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com
Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
The rt-users Archives
Community help: http://wiki.bestpractical.com
Commercial support: sales@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.