Reformat SQL-request

1

I’ve got on my RT-4.0.2 the sql-request, which never finish:
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’ OR ACL_2.RightName = ‘SuperUser’) AND ((ACL_2.ObjectType = ‘RT::Queue’) OR (ACL_2.ObjectType = ‘RT::System’) OR (ACL_2.ObjectType = ‘RT::Queue’) OR (ACL_2.ObjectType = ‘RT::System’)) ORDER BY main.Name ASC;

is it possible to re-format the sql-request to

select distinct main.* from users main join (SELECT distinct Principals_1.id FROM Principals Principals_1 JOIN CachedGroupMembers CachedGroupMembers_3 ON ( CachedGroupMembers_3.MemberId = Principals_1.id ) CROSS JOIN ACL ACL_2 WHERE ( CachedGroupMembers_3.GroupId = ACL_2.PrincipalId ) and (Principals_1.Disabled = ‘0’) AND (Principals_1.id != ‘1’) AND (Principals_1.PrincipalType = ‘User’) AND (ACL_2.PrincipalType = ‘Group’) AND (ACL_2.RightName = ‘OwnTicket’) AND ((ACL_2.ObjectType = ‘RT::Queue’) OR (ACL_2.ObjectType = ‘RT::System’) OR (ACL_2.ObjectType = ‘RT::Queue’) OR (ACL_2.ObjectType = ‘RT::System’)) ) t ON ( main.id = t.id ) order by main.name ;

This request happens when click on “New Search” which runs Query Builder with ‘NewQuery’=1 .
From /usr/share/request-tracker4/html/Elements/SelectOwnerDropdown runs RT::User::WhoHaveRight() (for “owner” drop-down menu).
The table Principals has got 70mln rows.
The table CachedGroupMembers has got 105mln rows.
The table ACL has got 20000 rows.
The table Users has got 3700 rows.
The database is Postgresql 9.1.

The upper sql-request never finishes.
The second sql-request executes for 0.3 seconds.

I’ve created a view, based on the second sql-request.
Is it possible to run select * from through
the DBIx::SearchBuilder directly?
Same as you did in the /usr/share/request-tracker4/lib/RT/Users.pm

#XXX: DIRTY HACK
use DBIx::SearchBuilder::Union;
my $union = DBIx::SearchBuilder::Union->new();
$union->add( $from_group );
$union->add( $from_role );

Something like below:
DBIx::SearchBuilder::new(‘RT::Users2’, ‘RT::CurrentUser2=HASH(0x7fb30c165f20)’ …)
and send the sql-request: “select * from UsersView;”
to DBIx::SearchBuilder
?
I cannot use current RT::Users , as the constructor already has got join and limit:
…
$self->OrderBy( ALIAS => ‘main’,
FIELD => ‘Name’,
ORDER => ‘ASC’ );

$self->{'princalias'} = $self->NewAlias('Principals');

# XXX: should be generalized
$self->Join( ALIAS1 => 'main',
             FIELD1 => 'id',
             ALIAS2 => $self->{'princalias'},
             FIELD2 => 'id' );
$self->Limit( ALIAS => $self->{'princalias'},
              FIELD => 'PrincipalType',
              VALUE => 'User',
            );

…

BTW,
The RT::CurrentUser initiates with Table (‘Users’), that is why I need RT::CurrentUser2 with Table(‘UsersView’).

So I need swap one sql-request with another one.
Please, help?