PostgreSQL Indexes

An index is a database object which is used for random access.Indexes are specialized lookup tables that database search engines can use to speed up data retrieval.Instead of going for sequential search, the system will use the index directly to get the desired result.An index is a pointer to data in a table.

For ex:- If you want to search for a particular topic in a book instead of searching on every page, you usually prefer to look at the index of the book that lists all the topics in alphabetical order and then refer to one or more specific page numbers.

The indexes help to speed up queries of SELECT and WHERE clauses, but it slows down with UPDATE and INSERT statements. Index data can be created or dropped with no effect on the data.Postgres creates indexes on columns that have a unique or primary key constraint. A manual index can also be created.

There are many types of Indexes some of them are:-

Single-Column Indexes:-It is the Index that is created based on only one table column.
Multicolumn Indexes:-It is the Index that is created based on more than one column of a table.
Unique Indexes:-It is an index that is not only used for performance, but is also used for data integrity. This does not allow any duplicate values to be inserted into the table.

Syntax Indexes:

The basic syntax is as follows :-

CREATE INDEX indx_name
ON table_name(col_name, [col_name])

Example :

Consider the COMPANY table to be created-

lfcdb=# CREATE INDEX salary_index ON COMPANY (salary);
-----------------------------------------
\\Now, following is an Output to create a index from COMPANY table.
Column  |     Type      | Modifiers
---------+---------------+-----------
id      | integer       | not null
name    | text          | not null
age     | integer       | not null
address | character(50) |
salary  | real        
|
Indexes:
"company_pkey" PRIMARY KEY, btree (id)
"salary_index" btree (salary)

Dropping INDEXES

To drop a INDEXES, use the DROP INDEXES statement with the index_name.

\\Syntax
lfcdb=# DROP INDEX index_name;
-----------------------------
\\Example
lfcdb=# DROP INDEX salary_index;

Visit :

PostgreSQL View

Discussion