Postgres not using index for start with query

Question

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?

Please show the index definition and your locale. My guess is that your locale is not c and the index was not defined with text_pattern_ops: postgresql.org/docs/current/indexes-opclass.html — Jeremy
– Jeremy, Commented Sep 26, 2021 at 11:23
@Jeremy Thanks a lot, you are right, text_pattern_ops solved the problem :-) — A.A
– A.A, Commented Sep 26, 2021 at 13:06

Laurenz Albe · Accepted Answer · 2021-09-26 11:48:36Z

2

The best index for the LIKE query would be

CREATE INDEX ON usernames (username text_pattern_ops);

For the regular expression match, you would need a trigram index, but it is easier to use the LIKE version instead.

answered Sep 26, 2021 at 11:48

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

A.A Over a year ago

As I have already a b-tree index on username field and my query is start with query, the b-tee index is appropriate and I don't want to add overload to database for a redundant index

Laurenz Albe Over a year ago

Drop the other index. The index I suggest works just as well for equality searches. Only to support inequality conditions (<, <=, > and >=) or ORDER BY you need a normal B-tree index.

Agnij · Accepted Answer · 2021-09-26 06:50:26Z

0

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%'

answered Sep 26, 2021 at 6:50

Agnij

5813 silver badges14 bronze badges

5 Comments

A.A Over a year ago

I asked that my query is START WITH, not END WITH

A.A Over a year ago

SHOW enable_indexscan is on

A.A Over a year ago

AFAIK there is no index hint in PostgreSQL ,how to hint?

Agnij Over a year ago

refer this link

Jeremy Over a year ago

That's an extension, there's no reason to believe the op has it installed.

gaurav garg · Accepted Answer · 2021-09-26 06:53:46Z

0

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.

answered Sep 26, 2021 at 6:53

gaurav garg

311 silver badge6 bronze badges

4 Comments

A.A Over a year ago

According to documentation The optimizer can also use a B-tree index for queries involving the pattern matching operators LIKE and ~ if the pattern is a constant and is anchored to the beginning of the string — for example, col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'.

gaurav garg Over a year ago

What percentage of rows return by these queries?

A.A Over a year ago

A few, less than 1%

A.A Over a year ago

Consider that I am using SET ENABLE_SEQSCAN =false;

Collectives™ on Stack Overflow

Postgres not using index for start with query

3 Answers 3

2 Comments

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related