[Rt-devel] Performance Problem

[Andrew Sullivan]

On Mon, Apr 19, 2004 at 02:44:44PM +0200, Tina Schade wrote:
> requested tickets take the longest time. I print out the querys and I notice 
> that the querys difference is the "=" and the "ILIKE". Here are the queries:

The ilike was an attempt to mimic MySQL's non-standard habit of
making "=" searches case-insensitive.  But the performance is truly
abysmal.

I think there's a more recent patch to SearchBuilder that uses
lower() on everything instead.  This is quite a bit faster in my
preliminary tests.  You'll need to check your indexes, though: you
need to ensure you have indexes like

CREATE INDEX foo_lwr_idx ON tablename (lower(foo));

even on the int columns.  This moves everything from being a truly
sucky set of sorts and nested loops to some nice indexscans.

BTW, you mentioned you're using 7.3.x.  I suggest upgrading to 7.4 as
well, because it has some planner improvements that let you use
hashes under previously-unhashable circumstances.

A

-- 
Andrew Sullivan  | ajs at crankycanuck.ca