btree text collate C index usages

Question

[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?

update.

SELECT DISTINCT
    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
FROM
    pg_am am,
    pg_opfamily opf,
    pg_amop amop,
    pg_opclass opc
WHERE
    opf.opfmethod = am.oid
    AND amop.amopfamily = opf.oid
    AND amop.amopopr::regoperator = '~(text,text)'::regoperator
    AND opc.opcintype::regtype = 'text'::regtype;

return:

 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;

output:

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
Planning:
  Buffers: shared hit=32 read=1
Planning Time: 5.876 ms
Execution Time: 30.076 ms

score 1 · Answer 1 · answered Apr 21 '23 at 14:54

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.

score 0 · Answer 2 · answered Apr 21 '23 at 10:22

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'

Andrea B. · Answer 3 · 2023-04-21T11:27:59.367

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.

btree text collate C index usages

update.

3 Answers3