WhoHaveRight query optimization


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’);
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 :frowning:

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

Indeed. that was incredibly bogus. Thanks very much.

Your pointer actually allowed to me to do some other optimizations to
the query. I’ll be rolling a new release this evening, sometime after I
get some client work done. Unless anyone finds a Real bug, I suspect
that the next patch release after tonight will get called RC 1.

http://www.bestpractical.com/rt – Trouble Ticketing. Free.