[rt-users] Re: [rt-devel] DBIx-SearchBuilder 0.81 released

[Anton Berezin]

On Sat, May 03, 2003 at 02:41:21PM +0200, Jeroen Ruigrok/asmodai wrote:
> -On [20030502 14:02], Jesse Vincent (jesse at bestpractical.com) wrote:
> >This version is exactly the same as 0.81_04. It contains changes to
> >massively improve performance when using postgresql for complex selects.
> 
> I never saw anyone respond to my emails on the subject where there was
> called for testing with pgsql and your newer DBIx::SearchBuilder.  In
> those emails I said that performance hadn't changed very much to make it
> worthwhile.
> 
> So I wonder what changed which makes you say the above?

Heh.  True, the SearchBuilder pgsql performance appears to be pathetic.

We looked together with Phil into the queries & timings, and "SET
enable_nestloop = off" thingy transforms one query which took
3.8 sec into a query which takes 0.25 sec.  (This can be put into
DBIx::SearchBuilder::Handle::Pg inside Connect method).

But!

There was another query, which took ~27 second, regardless of any
planner's settings I could get hold of:

SELECT DISTINCT main.* FROM Users main  JOIN Principals as Principals_3
ON  main.id = Principals_3.id JOIN CachedGroupMembers as
CachedGroupMembers_6  ON  Principals_3.Id =
CachedGroupMembers_6.MemberId JOIN Principals as Principals_1  ON
main.id = Principals_1.id JOIN CachedGroupMembers as
CachedGroupMembers_7  ON  Principals_1.id =
CachedGroupMembers_7.MemberId JOIN Principals as Principals_4  ON
CachedGroupMembers_6.GroupId = Principals_4.Id, Groups Groups_2, ACL
ACL_5  WHERE ((ACL_5.RightName = 'SuperUser')OR(ACL_5.RightName =
'OwnTicket')) AND ((CachedGroupMembers_7.GroupId = '4')) AND
((Principals_1.Disabled = '0')) AND ((Principals_1.PrincipalType =
'User')) AND ((Principals_3.PrincipalType = 'User')) AND
(ACL_5.ObjectType = 'RT::System'  OR (ACL_5.ObjectType = 'RT::Queue'
AND ACL_5.ObjectId = '5') ) AND ( (ACL_5.PrincipalId = Principals_4.Id
AND Principals_4.id = Groups_2.Id AND ACL_5.PrincipalType = 'Group' AND
(Groups_2.Domain = 'SystemInternal' OR Groups_2.Domain = 'UserDefined'
OR Groups_2.Domain = 'ACLEquivalence'))  OR ( ( (Groups_2.Domain =
'RT::Queue-Role' AND Groups_2.Instance = '5')  OR ( Groups_2.Domain =
'RT::Ticket-Role' AND Groups_2.Instance = '771')  )  AND Groups_2.Type =
ACL_5.PrincipalType AND Groups_2.Id = Principals_4.id AND
Principals_4.PrincipalType = 'Group') )  ORDER BY main.Name ASC;

I did not have time since Friday to try and rearrange it, but my gut
feeling is that in case of PostgreSQL, it is very profitable to convert
such monstrous joins into subselects.

\Anton.
-- 
Perl is strongly typed, it just has very few types. -- Dan Sugalski