Introduction to PostgreSQL (Coding Style) Part-7

This blog aims to assist you in understanding the concepts of PostgreSQL with complete coding as query language.

PostgreSQL SQL commands

Note: Make sure you have Postgres installed on your system to proceed to this tutorial.

PostgreSQL - INDEXES

• Indexes in a database are like pointers that enhance data retrieval speed by creating a reference to specific data in a table, much like an index in a book.

• While indexes improve SELECT queries and WHERE clauses, they can slow down data input (INSERT and UPDATE).

• Indexes can be created and dropped without affecting the data and are established using the CREATE INDEX statement, allowing customization of index name, table, indexed columns, and sorting order.

• Additionally, unique indexes prevent duplicate entries within the specified column or column combination.

Basic Syntax Of CREATE INDEX:

CREATE INDEX index_name ON table_name;

---

Index Types

• PostgreSQL offers various index types, including B-tree, Hash, GiST, SP-GiST, and GIN, each employing distinct algorithms optimized for specific query scenarios.

• The default index type created by the CREATE INDEX command is B-tree, suitable for common use cases.

Single-Column Indexes

Syntax: CREATE INDEX index_name
ON table_name (column_name);

Multicolumn Indexes

Syntax: CREATE INDEX index_name
ON table_name (column1_name, column2_name);

• Whether to create a single-column index or a multicolumn index, take into consideration the column(s) that you may use very frequently in a query's WHERE clause as filter conditions.

• Should there be only one column used, a single-column index should be the choice. Should there be two or more columns that are frequently used in the WHERE clause as filters, the multicolumn index would be the best choice.

Unique Indexes

• Unique indexes are used not only for performance, but also for data integrity.

• A unique index does not allow any duplicate values to be inserted into the table.

Syntax: CREATE UNIQUE INDEX index_name
on table_name (column_name);

Partial Indexes

A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index).

• The index contains entries only for those table rows that satisfy the predicate.

Syntax: CREATE INDEX index_name
on table_name (conditional_expression);

Implicit Indexes

• Implicit indexes are indexes that are automatically created by the database server when an object is created.

• Indexes are automatically created for primary key constraints and unique constraints.

The DROP INDEX Command

• An index can be dropped using PostgreSQL DROP command. Care should be taken when dropping an index because performance may be slowed or improved.

Syntax: DROP INDEX index_name;

---

When Should Indexes be Avoided?

• Although indexes are intended to enhance a database's performance, there are times when they should be avoided.

• The following guidelines indicate when the use of an index should be reconsidered −

• (1) Indexes should not be used on small tables.

• (2) Tables that have frequent, large batch update or insert operations.

• (3) Indexes should not be used on columns that contain a high number of NULL values.

• (4) Columns that are frequently manipulated should not be indexed.

---

I hope this blog has helped you understand the concepts of PostgreSQL with complete coding as a query language.

Check out a summary of Part-8.