In today’s article, we will be explaining how a database search engine can use indexes to speed up data retrieval.
For example, if you want to reference all pages in a book that discusses a certain topic, you have to first refer to the index, which lists all topics alphabetically and then refer to one or more specific page numbers.
Indexes are necessary to access the data in the tables in the shortest and faster way.
Indexes are one of the most important structures for database systems.
Performance problems may happen in systems that do not use index.
The general use of indexes is as follows.
|
1 |
CREATE INDEX index_name ON table_name (column_name); |
PostgreSQL supports different index types.
For each type of index, a different algorithm is used that is best suited to different types of queries.
Index types of PostgreSQL:
B-tree, hash, GIST, SP-GIST, GIN, BRIN
We will discuss the index types mentioned above under separate headings to better understand them.
A non-indexed table will try to search all records on the disk one by one, and therefore the return of our query will be long and costly.
Tables without indexes not only lengthen the query result, but also increase the CPU and disk usage rate and strain the machine on which the database runs in terms of CPU and disk.
NOTE: In some cases, we see that the table is scanned even if the index is created. This is because it detects that the written query will respond faster by scanning the table and will slow down the index.
