Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> The attached patch implements a one-value pattern cache for the
> multi-byte encoding case for ILIKE. This reduces calls to lower() by
> (50% -1) in the common case where the pattern is a constant. My own
> testing and Guillaume Smet's show that this cuts roughly in half the
> performance penalty we inflicted by using lower() in that case.
It might be a better solution to create the new text type 'lower_text'
and replace 'text ILIKE text' to 'text ILIKE lower_text'. The converter
function 'lower' is marked as immutable, planner can evaluate it just
one time in planning if the right-hand side is a constant expression.
Here is a pseudo-code for the above.
CREATE TYPE lower_text (INPUT = lower, ... );
CREATE CAST (text AS lower_text)
WITH FUNCTION lower(text) AS IMPLICIT;
CREATE OPERATOR ILIKE (
LEFTARG = text,
RIGHTARG = lower_text,
NTT Open Source Software Center
In response to
pgsql-patches by date
|Next:||From: Jaime Casanova||Date: 2007-09-25 02:26:36|
|Subject: Re: [HACKERS] 'Waiting on lock'|
|Previous:||From: Gregory Stark||Date: 2007-09-24 14:15:01|
|Subject: Re: [PATCHES] Eliminate more detoast copies for packed varlenas|