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?

Please edit your question and add the execution plan generated using explain (analyze, buffers) (not just a "simple" explain). Formatted text please, no screen shots or upload the plan to explain.depesz.com — user330315
– user330315, Commented Dec 18, 2018 at 8:09

Laurenz Albe · Accepted Answer · 2018-12-18 10:38:14Z

3

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.

answered Dec 18, 2018 at 10:38

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Weizhou He Over a year ago

Cool! This index is working. EXPLAIN result is below

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

Collectives™ on Stack Overflow

SQL Multiple column index not working with LIKE queries

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related