Using Postgres 9.5. The Postgres documentation says that the btree index can be used for left-anchored "like" queries (i.e. "%" only at end). But my left-anchored "like" queries on a 1 billion row table won't use a btree index, and my queries are taking forever. What am I missing? Here's a small example that demonstrates the problem:

-- generate a table of 100K integers with random texts
CREATE TABLE test(num integer, string text);
INSERT INTO test(num, string) (SELECT generate_series(1, 100000), md5(random()::text));

CREATE INDEX test_string ON test USING btree (string); -- b-tree index

EXPLAIN SELECT * FROM test WHERE string LIKE 'asdf%'; -- will use seqscan
EXPLAIN SELECT * FROM test WHERE string = 'asdf'; -- will use index
SET enable_seqscan=off; -- tell query planner to avoid seqscan if possible
EXPLAIN SELECT * FROM test WHERE string LIKE 'asdf%'; -- will still use seqscan
  • Did you run ANALYZE test;? I tested your commands above (with C collation), before an ANALYZE it used a seq scan, after the ANALYZE it used the index.
    – bma
    Apr 4 '17 at 21:50
  • I tried ANALYZE too. Turned out the issue was my locale settings.
    – sudo
    Apr 4 '17 at 21:57

Finally found this after digging through a bunch of tutorials that suggested I was already doing it the right way: http://www-old.bartlettpublishing.com/site/bartpub/blog/3/entry/329 . Relevant documentation is here: https://www.postgresql.org/docs/9.5/static/indexes-opclass.html

Looks like I'm using locale settings (that I didn't touch, just left default) that won't work for btree "like" index usage. I could have sworn that I wasn't seeing this problem before, and maybe I wasn't. I now have to do CREATE INDEX test_string ON test USING btree (string text_pattern_ops);

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