SQL Multiple column index not working with LIKE queries

Question

Considering a table as below

CREATE TABLE test (
    "a" varchar(32),
    "b" varchar(32),
    "c" varchar(32),
    "d" varchar(32)
);
CREATE INDEX  "test_index" ON test USING btree("a", "b", "c");

I need execute queries like

SELECT count(*) 
FROM test 
WHERE a like 'a%' 
and b = 'b' 
and c = 'c'

The result of EXPLAIN shows below

Aggregate  (cost=10.36..10.37 rows=1 width=0)
  ->  Index Only Scan using test_index on test  (cost=0.14..10.36 rows=1 width=0)
        Index Cond: ((b = 'b'::text) AND (c = 'c'::text))
        Filter: ((a)::text ~~ 'a%'::text)

Based on the EXPLAIN result from Postgres, only b and c is using index. It seems LIKE 'a%' only works with single column index.

So, how to increase the query speed for the above query?

Answer 1

The perfect index for that query is:

CREATE INDEX ON test (b, c, a varchar_pattern_ops);

You need varchar_pattern_ops for the LIKE condition unless the column uses the C (or equivalently POSIX) collation. Run SHOW lc_collate; to see your default collation.

Your index probably cannot be used because you are using a different collation. Also, LIKE conditions cause an index range scan, and all the columns with an equality condition should be in the index before the column with the range scan.