[rt-users] postgres slowdowns? (pg 7.3.4, rt 3.0.4)

[Justin Hawkins]

On Wed, Oct 15, 2003 at 09:49:15AM -0400, Vivek Khera wrote:
> >>>>> "JH" == Justin Hawkins <justin at internode.com.au> writes:
> 
> JH> (Users_5.EmailAddress = 'email at address.here') AND
> 
> JH> Changes the query time from several minutes (!) to less than a second.
> 
> JH> Obviously the query is not quite right then, since I am guessing we
> JH> do care about case insensitivity. So perhaps:
> 
> Or create an index on lower(EmailAddress) on that table:
> 
> CREATE INDEX users5 ON users (lower(emailaddress));
> 
> see if that speeds it up, since PG will then be able to use the
> index.  It sounds like the users5 index is unused, since the query is
> not against a fixed string but on a function.

Tried, but no dice. Are you interested in seeing the postgres EXPLAIN output's
before and after the index?

I tested the ILIKE solution and it does work. It reduces query time to less than
one second. I expect this may not be suitable in terms of keeping this 
cross-database-platform compatible.

	- Justin