[Rt-devel] create index on groups(instance) is a booster

[Vivek Khera]

On May 13, 2004, at 8:59 PM, Palle Girgensohn wrote:

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