Re: ilike multi-byte pattern cache

Andrew Dunstan <andrew@dunslane.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,
);

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center