What are Indexes?
The role of database indexes is similar to the index section at the back of a book. A database index stores information on where a data row is located in a table so the database doesn't have to scan the entire table for information. When the database has a query to retrieve, it goes to the index first and then uses that information to retrieve the requested data. For example, if the names in a contact book are not alphabetized, you would have to go down every row and search through every name before you reach the specific phone number that you are searching for. An index speeds up the SELECT commands and WHERE phrases, performing data entry in the UPDATE and INSERT commands. Regardless of whether indexes are inserted or deleted, there is no impact on the information contained in the table. Indexes can be special in the same way that the UNIQUE limitation helps to avoid replica records in the field or set of fields for which the index exists.
Postgres Index:
Indexes are their own data structures and they’re part of the Postgres data definition language (the DDL). They're stored on disk along with data tables and other objects.
General Syntax:
The following statement is used to create an index on a particular table on the specified column or field.
CREATE INDEX index_name ON table_name (column_name);
What makes Indexing so useful?
Indexing doesn’t change the existing document/table it is being applied to (unless it is clustered indexing which I’ll discuss later in the blog), it instead creates a new data structure that has two blocks for every entry. These two blocks are the:
- Data Field on which index is created
- Pointer to the Row/Document where that data field is stored in Database(Address)
Testing Index Performance:
To test if indexes will begin to decrease query times, you can run a set of queries on your database, record the time it takes those queries to finish, and then begin creating indexes and rerunning your tests.
To do this, try using the EXPLAIN ANALYZE clause in PostgreSQL:
EXPLAIN ANALYZE SELECT * FROM table_name WHERE id = 7;
The output will tell you which method of search from the query plan was chosen and how long the planning and execution of the query took.
Important Guidelines for PostgreSQL indexes:
- The default Postgres index is a B-tree index.
- You should always properly analyze your workload using query execution plans to determine the suitable Postgres index type.
- Always create indexes on the most executed and costly queries. Avoid creating an index to satisfy a specific query.
- As per best practice, always define a primary or unique key in a Postgres table. It automatically creates the B-tree index.
- Avoid creating multiple indexes on a single column. It is better to look at which index is appropriate for your workload and drop the unnecessary indexes.
- There is no specific limit on the number of indexes in the table; however, try to create the minimum indexes satisfying your workload.
Index Types:
B-tree indexes
Hash indexes
GIN indexes
BRIN
GiST Indexes
A detailed discussion on these indexes would be covered on the next article.
Want to extract all the benefits of PostgreSQL along with Graph features look at:
Top comments (0)