Create index on groups(instance) is a booster

RT Developers
1

I’ve found that an index on groups (instance) is a real booster. It will
help this very common query:

SELECT * FROM Groups WHERE lower(Domain) = ‘rt::ticket-role’ AND Instance
= ‘1888’ AND lower(Type) =‘requestor’;

at least on postgresql with my database, the query will move from 20 ms to
1 ms.

/Palle

2

I’ve found that an index on groups (instance) is a real booster. It
will help this very common query:

Your message got me to thinking about this. It seems to me that pretty
much every index in RT is not gonna be used by postgres since all the
text fields are always searched with lower() but the indexes are not
created with lower(). For example, in the groups table, the index
groups2 covers three text fields – the exact three used in the above
query. Presumably this index would be used for the query in question
if it was created with lower(). I’m not sure how to create
multi-column functional indexes, however. And it seems that the
instance column is not searched with lower(), so that complicates
things somewhat, too.

3

if it was created with lower(). I’m not sure how to create
multi-column functional indexes, however.

This seems to work:

CREATE INDEX t1_t2_lower_idx on foo (lower(t1),lower(t2));

A

Andrew Sullivan | ajs@crankycanuck.ca
I remember when computers were frustrating because they did exactly what
you told them to. That actually seems sort of quaint now.
–J.D. Baldwin