EverSQL Database Performance Knowledge Base

Question 1

EverSQL Database Performance Knowledge Base

Postgres not using index for start with query

Database type:

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?

Answer

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.

The optimization process and recommendations:

Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

CREATE INDEX usernames_idx_username ON "usernames" ("username");

The optimized query:

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

Question 2

EverSQL Database Performance Knowledge Base

Postgres not using index for start with query

How to optimize this SQL query?

The optimization process and recommendations:

Optimal indexes for this query:

The optimized query:

Related Articles