postgresql "SELECT title FROM table WHERE title ILIKE %keyword%" not using BTREE INDEX

Question

First I create the table and index ， and here are the definition：

    CREATE TABLE boxes(id SERIAL PRIMARY KEY, text text, name character varying(255) COLLATE pg_catalog."C.UTF-8")

    development=# CREATE INDEX boxes_name_idx ON boxes USING btree (name COLLATE pg_catalog."C.UTF-8" varchar_pattern_ops);
    development=# \d boxes
                             Table "public.boxes"
    Column |          Type          |                     Modifiers
    --------+------------------------+----------------------------------------------------
    id     | integer                | not null default nextval('boxes_id_seq'::regclass)
    text   | text                   |
    name   | character varying(255) | collate C.UTF-8
    Indexes:
        "boxes_pkey" PRIMARY KEY, btree (id)
        "boxes_name_idx" btree (name varchar_pattern_ops)

Second，Insert 10003 items into tables, and explain the query. But it vary slow：

development=# EXPLAIN ANALYZE SELECT name FROM boxes  WHERE name  ILIKE '%商品%';
                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on boxes  (cost=626.93..1019.21 rows=3 width=87) (actual time=2.356..48.937 rows=3 loops=1)
   Filter: ((name)::text ~~* '%商品%'::text)
   Rows Removed by Filter: 10100
   Heap Blocks: exact=266
   ->  Bitmap Index Scan on boxes_name_idx  (cost=0.00..626.92 rows=10103 width=0) (actual time=1.910..1.910 rows=10103 loops=1)
 Planning time: 0.509 ms
 Execution time: 48.973 ms
    (7 rows)



development=# EXPLAIN ANALYZE SELECT name FROM boxes  WHERE name COLLATE pg_catalog."C.UTF-8"  ILIKE '%商品%';
                                QUERY PLAN
 ---------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on boxes  (cost=626.93..1019.21 rows=3 width=87) (actual time=2.358..49.039 rows=3 loops=1)
   Filter: ((name)::text ~~* '%商品%'::text)
   Rows Removed by Filter: 10100
   Heap Blocks: exact=266
   ->  Bitmap Index Scan on boxes_name_idx  (cost=0.00..626.92 rows=10103 width=0) (actual time=1.896..1.896 rows=10103 loops=1)
 Planning time: 0.506 ms
 Execution time: 49.073 ms
(7 rows)



development=# SELECT name FROM boxes  WHERE name  ILIKE '%商品%';
    name
    -----------
 商品标题3
 商品标题
 商品标题2
 (3 rows)


development=# SELECT count(id) FROM  boxes;
    count
    -------
 10103
(1 row)


development=# SHOW LC_COLLATE;
lc_collate
-------------
en_HK.UTF-8
(1 row)

We can see that， the index not using by Filter.

My PostgreSQL version is 9.5.1

score 3 · Accepted Answer · answered May 04 '16 at 12:13

The documentation is quite clear on this point:

The optimizer can also use a B-tree index for queries involving the pattern matching operators LIKE and ~ if the pattern is a constant and is anchored to the beginning of the string — for example, col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'.

Your pattern starts with a wildcard. Hence, it doesn't use the index.

Postgres has GIN, GIST, and n-gram indexes to support full-text support.

**Thanks Sir** it help me so much :) – Fish May 05 '16 at 09:17 — Fish, May 05 '16 at 09:17

postgresql "SELECT title FROM table WHERE title ILIKE %keyword%" not using BTREE INDEX

1 Answers1