PostgreSQLのデータベースでLIKE文の効率を確保する方法（推奨）

postgres=# create database testdb01 owner highgo;
CREATE DATABASE
postgres=# \c testdb01 highgo
 
 
testdb01=# create table testliketb01 (userid int primary key,username varchar(20),password varchar(60),description text);
CREATE TABLE

testdb01=# insert into testliketb01 select generate_series(1,500000),split_part('Zhang San,Li Si,Wang Wu,Xiao Ming,Xiao Hong',',',',(random()*(5-1)+1)::int),md5(( random()*(5-1)+1)::varchar),split_part('highgo,highgo02,highgo03',','',(random()*(3-1)+1)::int);

testdb01=# select * from testliketb01 limit 10;
userid | username | password | description
--------+----------+----------------------------------+-------------
      1 | wangwu | 4f2bca371b42abd1403d5c20c4542dff | highgo
      2 | Li Si | 2a978c605188770c5ed162889fff189e | highgo02
      3 | Li Si | f5d129ab728b72ac6f663fe544bc7c16 | highgo
      4 | 小明 | 53134fa1022c58e65168b6aa1fbe5e39 | highgo02
      5 | 王五 | 2cf9abb2a8b676a626fa2c317d401ed8 | highgo02
      6 | 王五 | 2247a0cfda1f2819554d6e8e454622eb | highgo02
      7 | Zhang San | 59dfdc680c17533dfba1c72c9ce0bf76 | highgo02
      8 | wangwu | 87db4258236a3826259dcc3e7cb5fc63 | highgo02
      9 | wangwu | baaf7a2f7027df9aaeb665121432b6e2 | highgo02
     10 | wangwu | 2f8fb36b3227c795b111b9bd5b031a76 | highgo02
(10 rows)
The status of the database at this point.
testdb01=# \l+ testdb01
                                                List of databases
   Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
----------+--------+----------+-------------+-------------+-------------------+-------+------------+-------------
testdb01 | highgo | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 59 MB | pg_default |
(1 row)

testdb01=# explain analyze select * from testliketb01 where username like 'Wang%';
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on testliketb01 (cost=0.00..11405.00 rows=125350 width=52) (actual time=0.014..177.571 rows=124952 loops=1)
   Filter: ((username)::text ~~ 'Wang%'::text)
   Rows Removed by Filter: 375048
Planning Time: 0.121 ms
Execution Time: 190.554 ms
(5 rows)

testdb01=# explain analyze select * from testliketb01 where username like 'Wang%';
                                                                   QUERY PLAN                                                                    
---------------------------------------------------------------------------------------------------------------------------------- ---------------
Bitmap Heap Scan on testliketb01 (cost=2665.26..9387.14 rows=125350 width=52) (actual time=31.383..94.745 rows=124952 loops=1)
   Filter: ((username)::text ~~ 'Wang%'::text)
   Heap Blocks: exact=5155
   -> Bitmap Index Scan on idx_testliketb01_username (cost=0.00..2633.92 rows=125350 width=0) (actual time=29.730..29.730 rows=124952 loops=1)
         Index Cond: (((username)::text ~>=~ 'king'::text) AND ((username)::text ~<~ 'cemetery'::text))
Planning Time: 0.111 ms
Execution Time: 107.030 ms
(7 rows)

testdb01=# create database testdb02 with TEMPLATE template0 LC_COLLATE='C' LC_CTYPE ='C' owner highgo;
CREATE DATABASE
 
 
testdb02=# \l+ testdb02
                                           List of databases
   Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
----------+--------+----------+---------+-------+-------------------+-------+------------+-------------
testdb02 | highgo | UTF8 | C | C | | 59 MB | pg_default |
(1 row)
 
 
testdb02=# create index idx_testliketb01_username on testliketb01(username);
CREATE INDEX
testdb02=# analyze testliketb01 ;
ANALYZE
testdb02=# analyze testliketb01 ;
ANALYZE
testdb02=# analyze testliketb01 ;
ANALYZE
testdb02=# explain analyze select * from testliketb01 where username like 'Wang%';
                                                                   QUERY PLAN                                                                    
---------------------------------------------------------------------------------------------------------------------------------- ---------------
Bitmap Heap Scan on testliketb01 (cost=2680.26..9410.67 rows=126033 width=52) (actual time=35.262..99.052 rows=124992 loops=1)
   Filter: ((username)::text ~~ 'Wang%'::text)
   Heap Blocks: exact=5155
   -> Bitmap Index Scan on idx_testliketb01_username (cost=0.00..2648.75 rows=126033 width=0) (actual time=33.920..33.920 rows=124992 loops=1)
         Index Cond: (((username)::text >= 'king'::text) AND ((username)::text < 'naming'::text))
Planning Time: 0.276 ms
Execution Time: 111.578 ms
(7 rows)