[1] dbfiddle uk
[2] Pattern matching with LIKE, SIMILAR TO or regular expressions

original post says

If concerned with performance, create an index like this for bigger tables to support left-anchored search patterns (matching from the start of the string):

CREATE INDEX spelers_name_special_idx ON spelers (name COLLATE "C");

my [1] test shows that btree index support search patterns (matching from any position of the string).

Do that mean CREATE INDEX spelers_name_special_idx ON spelers (name COLLATE "C"); can cover text string equality and containment use cases (input parameter is being contained in text columns) and prefix/subfix pattern searching?


    am.amname AS index_method,
    opf.opfname AS opfamily_name,
    amop.amopopr::regoperator AS opfamily_operator,
    opc.opcintype::regtype AS indexed_type,
    opc.opcdefault AS is_default
    pg_am am,
    pg_opfamily opf,
    pg_amop amop,
    pg_opclass opc
    opf.opfmethod = am.oid
    AND amop.amopfamily = opf.oid
    AND amop.amopopr::regoperator = '~(text,text)'::regoperator
    AND opc.opcintype::regtype = 'text'::regtype;


 index_method | opfamily_name | opfamily_operator | indexed_type | is_default
 gist         | gist_trgm_ops | ~(text,text)      | text         | t
 gin          | gin_trgm_ops  | ~(text,text)      | text         | f
 gin          | gin_trgm_ops  | ~(text,text)      | text         | t
 gist         | gist_trgm_ops | ~(text,text)      | text         | f

in following precondition:

set enable_seqscan to off;
create index on spelers using GIN(name gin_trgm_ops);
create index spelers_name_c_idx on spelers (name collate "C");

I am a little bit confused with following query output.

explain(analyze, costs off, buffers)
select name from spelers where name ~ '你好' or name ~ '^Dr' order by 1;


Sort (actual time=30.035..30.037 rows=9 loops=1)
  Sort Key: name
  Sort Method: quicksort  Memory: 25kB
  Buffers: shared hit=1 read=38
  ->  Index Only Scan using spelers_name_c_idx on spelers (actual time=0.126..30.008 rows=9 loops=1)
        Filter: ((name ~ '你好'::text) OR (name ~ '^Dr'::text))
        Rows Removed by Filter: 9995
        Heap Fetches: 0
        Buffers: shared hit=1 read=38
  Buffers: shared hit=32 read=1
Planning Time: 5.876 ms
Execution Time: 30.076 ms

3 Answers 3


Just because an index is used doesn't mean it is used well. Indexes can "support" things which are horribly inefficient. And if you set enable_seqscan = off, then it is not just possible but likely that they will do this.

In the last plan you show, the index is being scanned in its entirety, and it is just comparing the value of "name" stored in the index to the WHERE conditions in a brute force way, just like it would be doing if it were seq scanning the table instead.


No B-tree index can support patterns like LIKE '%substring%' that are not anchored at the beginning.

The index you propose can be used for equality searches and LIKE patterns that do not start with a wildcard. However, the equality search has to be written like this:

... WHERE name COLLATE "C" = 'somename'

In your test you first use a GIN index, which is not a normal btree index and it adds the capability of searching within the element. Postgresql is actively using the search capability of this index to narrow the search, and the query plan shows this line:  

Index Cond: (name ~ '你好'::text)

When you create a normal btree index (index spelers_name_c_idx on spelers (name collate "C")) you can see that postgresql is scanning the index (instead of the table) because it has to report just the indexed column. However, it is not searching only a part of the tree, it's actually scanning all the rows of the index. The query plan shows this line:

Filter: ((name ~ '你好'::text) OR (name ~ '^Dr'::text))
Rows Removed by Filter: 9995

'Filter' means that it's actually reading all the rows and checking if the condition apply. It has no way to directly get the rows that satisfy the condition without checking every one. It actually read all 10.004 rows of the index, 9995 of which didn't satisfy the condition and were discarded.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.