[rt-devel] WhoHaveRight query optimization

[Willem Jan Palenstijn]

Hi,

We're currently trying to run RT 2-1-84 on RedHat 8 (Apache 2.0.40,
Postgresql 7.2.3, mod-perl 1.99_05).

However, we've run into a problem: any ticket transactions were
originally very slow. Pressing the 'New ticket in' button resulted in a
wait of over 5 minutes on a lightly-loaded Athlon 550, with a database
of about 640 tickets and 80 users). We traced this down to the query
executed by WhoHaveRight in Users_Overlay.pm, when determining who has
the OwnTicket right.

We have made two changes to WhoHaveRight to speed this up:

1: The condition "$groups.Id = $groupprinc.id" is included on both
branches of the (top-level) OR in the 'WhichGroup' sub-clause. Moving it
to outside of the OR resulted in a huge speedup. (Presumably because
this made things easier on the query planner)

2: It turned out that the query is using 3 aliases of the Principals
table, two of which are equal: $self->{'princalias'} and $userprinc.
(Because the ID of both is equal to 'main.id'). I'm not entirely sure if
it's safe to do, but replacing
my $userprinc = $self->NewAlias('Principals');
with
my $userprinc = $self->{'princalias'};
speeded things up quite a lot here.

After these changes, the runtime of the query has been reduced to a more
acceptable 5 seconds.

Manually optimizing the query by using explicit JOINs instead of WHERE
conditions results in a runtime of only 100ms, incidentally. I suspect
the postgresql query scheduler may be having some issues with the
produced queries :-(

-Willem Jan


P.S.: sorry if you receive this twice. I first sent it about 30 hours
ago from an address not subscribed to rt-devel, and it didn't show up on
the list.