Skip site navigation (1) Skip section navigation (2)

Re: ilike multi-byte pattern cache

From: ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
To: Andrew Dunstan <andrew(at)dunslane(dot)net>
Cc: "Patches (PostgreSQL)" <pgsql-patches(at)postgresql(dot)org>
Subject: Re: ilike multi-byte pattern cache
Date: 2007-09-25 01:07:41
Message-ID: 20070925095115.5E07.ITAGAKI.TAKAHIRO@oss.ntt.co.jp (view raw or flat)
Thread:
Lists: pgsql-patches
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,
);

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



In response to

pgsql-patches by date

Next:From: Jaime CasanovaDate: 2007-09-25 02:26:36
Subject: Re: [HACKERS] 'Waiting on lock'
Previous:From: Gregory StarkDate: 2007-09-24 14:15:01
Subject: Re: [PATCHES] Eliminate more detoast copies for packed varlenas

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group