If you see that a SQL SELECT query is slow - what would you do to improve it?

If a SQL SELECT query is slow, there are some simple steps we can take to identify the cause and improve its performance.

I will explain the above step by step:

1 Step - First see the plan for the execution of the query:

To do that you can use EXPLAIN or EXPLAIN ANALYZE to understand how this query is working behind the scenes.

For example, I built a simple book stores system, with 3 entities, Books, Authors, and Publisher, I ran a simple query with a merge between Books and Authors to show how it works

As you can see in the table Result 1 this command can show the query execution plan with all process in the query running it can be used to see the bottlenecks in the process, I will proposital add a bottleneck in the process for example adding a table in the query merge without foreign keys.

CREATE TABLE books_publisher (
    publisher_id SERIAL PRIMARY KEY,
    publisher_name VARCHAR(100) NOT NULL
);

ALTER TABLE books_book
ADD COLUMN publisher_id INTEGER;

INSERT INTO books_publisher (publisher_name) VALUES ('Penguin');
INSERT INTO books_publisher (publisher_name) VALUES ('Harper Collins');
INSERT INTO books_publisher (publisher_name) VALUES ('Oxford University Press');
INSERT INTO books_publisher (publisher_name) VALUES ('Pearson');

UPDATE books_book
SET publisher_id = (SELECT publisher_id FROM books_publisher ORDER BY RANDOM() LIMIT 1);

Now I run the query doing a join with publisher:

Note: The time of execution is 11.180 ms

2 Step - Verify foreign keys and indexes:

I will add a foreign key index to improve the algorithm when we run the query:

ALTER TABLE books_book
ADD CONSTRAINT fk_publisher
FOREIGN KEY (publisher_id)
REFERENCES books_publisher(publisher_id);

CREATE INDEX idx_publisher_id
ON books_book (publisher_id);

CREATE INDEX books_book_btree_publisher_id ON public.books_book USING btree (publisher_id);

After improving in DDL structure:

Note: The time of execution is 4.168 ms

3 Step - Verify what business needs, and filter data:

For example to count how many books a publisher send for each author we should do the query:

select
    books_publisher.publisher_name,
    books_author."name"  as author,
    count(*) qty
from books_book
inner join books_author 
on books_book.author_id = books_author.id
inner join books_publisher 
on books_publisher.publisher_id = books_book.publisher_id
group by books_publisher.publisher_name, books_author."name";

This query took:

We can remove the books where we did not have publishers to avoid unnecessary loops I will add a filter:

  where books_book.publisher_id is not null

It is the result:

It is a very small example, on the large datasets other things we can do are:

• remove necessary data from attribute selection
• do views or materialized views with your subqueries to preprocess data
• organize data to follow normalization rules or unfollow (it can work too), depending on the kind of feature you want to provide to your customer
  • Verify resources of the database, such as: connections opened, connections idle, CPU and RAM use.