[Rt-devel] Another sql improvement recommendation for rt3.4.0

[David Kerry]

On Sun, Jan 09, 2005 at 02:50:16PM -0500, Jesse Vincent wrote:
> 
> > If possible, avoid using functions on a column in a query.
> 
> If you look a bit deeper, you'll see that that's being done by
> SearchBuilder when a case-insensitive search is called for on a database
> that doesn't natively support case-insensitive queries.

Ah.. ok.

> > In all the databases I've used, the optimizer will not use
> > any indexes associated with that column as soon as you wrap
> > it in a function and will do a full table scan.
>  
> Postgres and Oracle both support functional indexes.

True - but it's nice to avoid such things if they're not really
necessary.

> > The better way is to store the data in a known format (all lowercase
> > in this example) and do something like this instead:
> 
> Except that would mean storing usernames in all lowercase, as well as
> all correspondence related to a ticket.

I didn't mean all columns had to be done this way, just cases where
it can be done easily.  For example - I see no reason to not store
user's email addresses in all-lowercase.  Email addresses are naturally
case-insensitive anyways, so no problems there.  That would remove
the one slow query I found already.

> > I point this out because loading up a ticket used this query:
> 
> That query sure looks like a search for all tickets with user at abc.com,
> not loading a single ticket...What happens if you add a functional
> index?
<snip query>

This was for loading up a single ticket - I presume it was looking for
any other tickets created by the same user to list in one of the related
ticket display boxes.

For what it's worth, adding a function based index didn't seem to make
any difference in postgres.

ie: create index users5dk on users(lower(emailaddress));

Query time was the same (2-4 seconds) with or without this index.


I've also found another one:

SELECT * FROM Groups WHERE LOWER(Domain) =  'systeminternal' AND LOWER(Type) =  'privileged';

500ms (with LOWER()) vs 36 ms (without LOWER()).

This seems to be an internal query - RT should know the values in its
own internal tables, no?  Why the case-insensitive search for this?

If the lower() is being added to columns automatically, I think
this is probably a major cause of some of the RT slowness.  Overzealous
use of that is going to kill your performance (unless you make all your
indexes case-insensitive function-based indexes, that is).

Also, note that Oracle (haven't tried postgres) won't allow you to index
a column more than once (with a single-column index), so you either have
a case-sensitive index (lower()), or a case-insensitive one, but you
can't have both, so you either have to do all your queries with a matching
lower() or none at all (or as few as possible).

Anyways... I'm just pointing out that you can get some major performance
boosts out of RT if you fine tune some of the queries a bit.

-- 
David Kerry