Re: Postgres case insensitive searches

On Mon, Jul 1, 2013 at 10:01 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:
> bhanu udaya wrote:
>>>> What is the best way of doing case insensitive searches in postgres using Like.
>>>
>>> Table "laurenz.t"
>>> Column | Type | Modifiers
>>> --------+---------+-----------
>>> id | integer | not null
>>> val | text | not null
>>> Indexes:
>>> "t_pkey" PRIMARY KEY, btree (id)
>>>
>>>
>>> CREATE INDEX t_val_ci_ind ON t ((upper(val) text_pattern_ops);
>>>
>>> ANALYZE t;
>>>
>>> EXPLAIN SELECT id FROM t WHERE upper(val) LIKE 'AB%';
>>>
>>> QUERY PLAN
>>> ------------------------------------------------------------------------------
>>> Index Scan using t_val_ci_ind on t (cost=0.01..8.28 rows=1 width=4)
>>> Index Cond: ((upper(val) ~>=~ 'AB'::text) AND (upper(val) ~<~ 'AC'::text))
>>> Filter: (upper(val) ~~ 'AB%'::text)
>>> (3 rows)
>

>
> My solution is fast and efficient, it will call upper() only once
> per query. I don't see your problem. Different database systems
> do things in different ways, but as long as you can do what you need
> to do, that should be good enough.
>
> Yours,
> Laurenz Albe
>

I was toying around a little bit with this example, just for my
understanding, the function upper is called for every row in the
result. I think this has something to to with the filter in the plan.
This is what I did

create table foo as (select md5(random()::text) from
generate_series(1,2.5e6::integer));
-- create a little wrapper function to see when it is called
create ': create or replace function test_upper(text_in TEXT) RETURNS TEXT AS
$func$
begin
raise warning 'called';
return upper(text_in);
end;
$func$ LANGUAGE plpgsql IMMUTABLE;

create index foo_ind on foo (test_upper(md5) text_pattern_ops); --lots
of 'called' ouptut
analyze foo;

-- here you see that the function is called for every row in the result
postgres=# select * from foo where test_upper(md5) like 'ABAAB%';
WARNING: called
WARNING: called
WARNING: called
md5
----------------------------------
abaab10ff1690418d69c360d2dc9c8fc
abaab339fb14a7a10324f6007d35599a
abaab34f0cebabee89fa222bfee7b6ea
(3 rows)

postgres=# explain select * from foo where test_upper(md5) like 'ABAAB%';
QUERY PLAN
----------------------------------------------------------------------------------------------
Index Scan using foo_ind on foo (cost=0.50..14.02 rows=250 width=33)
Index Cond: ((test_upper(md5) ~>=~ 'ABAAB'::text) AND
(test_upper(md5) ~<~ 'ABAAC'::text))
Filter: (test_upper(md5) ~~ 'ABAAB%'::text)
(3 rows)

So under my assumption that it is the filter that causes the function
execution, I don't understand
how a row can satisfy

--which I read as >= 'ABAAB' and < 'ABAAC'
((test_upper(md5) ~>=~ 'ABAAB'::text) AND (test_upper(md5) ~<~ 'ABAAC'::text))

and not

(test_upper(md5) ~~ 'ABAAB%'::text)

Ingmar