📚Database Index

What is Index ?

Index is the way that we create structure of the DB for support the search and it make the performance better (execute time) but It affect tot write operation (Insert,Update Delete) as well .

There are different type of index

1. B-Tree (default for postgres when you created index)

2. Hash (recommend for equilavent query and IN )

Why we need to have index?

In normal case If we have the data in DB for one millions records , everytime you try to find the record id 287 It will scan all of the record in the DB tht mean it spend a lot of time so we can prevent this process by using index. Command for checking performance

// Some code
Explain Analyze Query

-- Explain Analyze select * from employee where id = 283

When you used this command the part that you need to focus is Type of Search Planning Timeand Execution Time

1. Type of seacrh = It is the Sequence Scan or B-Tree or other.

2. Planning time = it is the time that the system planned before query whether Do we has index in this DB ? What is the type of search we will use ?

3. Execution time = it is the time that DB query the data.