I have a usernames table with a username string field, I have a "start with" query and trying both pattern and regex matching

SELECT username FROM usernames WHERE username LIKE 'foo%';

SELECT username FROM usernames WHERE username ~ '^foo';

I have a b-tree index on the username field and SET ENABLE_SEQSCAN =false; , When I EXPLAIN the abovementioned queries, the query planner uses SEQ_SCAN (Seq Scan)

I am using PostgreSQL 13.4 , tried VACUUM (VERBOSE, ANALYZE) usernames and REINDEX TABLE usernames too

What's wrong here?

CodePudding user response：

Ideally by default once the B-tree index has been created it should be using the Index Scan, the only condition otherwise is-

using the LIKE keyword followed by a wildcard and then a string ('%nik') cannot use an index defined on the column but if it is vice versa then the index is used ('Slo%')

Try passing hints to the Query plan builder, also is Index_scan enabled?

/* IndexScan(username)*/ select username from usernames where username like 'foo%'

CodePudding user response：

This is happening because of 'where' condition given in the statement (username LIKE 'foo%'). Here You are trying to fetch all rows which meet this pattern. Therefore it required to check all rows which is started with character "foo" and ignore the index.