Analyze And Indexing Table In PostgreSQL (Easy Way) 

When I was conducting performance testing on the application, I found that there were several queries that were very heavy and took a significant amount of time. Upon investigation using AWS RDS Performance Insights database, it was discovered that there were several resource-intensive queries. To perform further checks, each query can also be examined individually. Therefore, the solution employed was to implement indexing.

I have a table named 'Employee' with the following columns:

The amount of data 11,01 million rows:

The old query (before indexing).

SELECT *
FROM employee
WHERE salary = 9999998

The result before indexing takes a long time, approximately 0.6 seconds.

Then, an analysis is conducted.

EXPLAIN ANALYZE
SELECT *
FROM employee
WHERE salary = 9999998

The result still uses parallel seq scan, which is not good because it scans one data at a time until it finds what is being searched for. If the number is at the bottom, it has to search one by one until it reaches the bottom.

To make it easier to visually observe what is causing the delay (Visualization).

Using Explain Depesz

Therefore, the solution to this problem is to implement indexing and change the sequential scan to an index scan.

Creating an index for the salary column in the employee table.

CREATE INDEX idx_salary ON employee USING btree (salary)

After creating an index, let's try analyzing it again using 'explain analyze.'

The result is less than 1ms and now it uses an index scan, which directly searches within the data without scanning one by one.

The visual analysis result is no longer in red.

Indexing the database is necessary to speed up queries, this must be done to ensure that the application runs smoothly.