DBIx::SearchBuilder::Handle::Pg -- again

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
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

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 -----

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,
KenOn 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


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

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 -----


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

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

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


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

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 -----


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

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


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

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.

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


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

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 -----


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

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


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

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.

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:

  • almost empty Pg DB

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


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

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 -----


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

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


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

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.

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@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@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


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

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 -----


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

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


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

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.

Best regards, Ruslan.

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,
KenOn 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 ktm@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@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


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

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 -----


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

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


http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

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.


Best regards, Ruslan.