pgroonga_varchar_like.sql

CREATE TABLE names (
  name varchar(255)
);

create extension pgroonga;

create index pgroonga_name_index on names using pgroonga (name);

insert into names values ('山田さん');
insert into names values ('横山さん');
insert into names values ('斉藤さん');

explain analyze verbose select * from names where name::TEXT like '%さん%';

--
--                                                       QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
-- Seq Scan on public.names  (cost=10000000000.00..10000000001.04 rows=1 width=516) (actual time=0.031..0.036 rows=3 loops=1)
--   Output: name
--   Filter: ((names.name)::text ~~ '%さん%'::text)
-- Planning Time: 0.173 ms
-- Execution Time: 0.070 ms
-- (5 rows)

@kouさん

ありがとうございます〜 💝

explain analyze verbose select * from names where name like '%さん%';

--                                                               QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
--  Index Scan using pgroonga_name_index on public.names  (cost=0.00..4.01 rows=1 width=516) (actual time=57.946..57.952 rows=3 loops=1)
--    Output: name
--    Index Cond: ((names.name)::text ~~ '%さん%'::text)
--  Planning Time: 0.856 ms
--  Execution Time: 58.020 ms
-- (5 rows)