PostgreSQL - SPLessons

PostgreSQL Index

Chapter 20

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PostgreSQL Index

PostgreSQL Index

shape Description

An PostgreSQL Index helps us to faster retrieval of records. PostgreSQL Index have an entry of each value in the indexed column. PostgreSQL Index should be created on frequently used columns.

shape Syntax

The syntax for PostgreSQL INDEX is as follows:
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON table_name ( column1 [ASC | DESC ], … column_n [ ASC | DESC ] )
[ INCLUDE ( column1, … column_n ) ]
[ WHERE condition ]
[ WITH ( PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = { ON | OFF }
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| STATISTICS_INCREMENTAL = { ON | OFF }
| DROP_EXISTING = { ON | OFF }
| ONLINE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| MAXDOP = max_degree
| DATA_COMPRESSION = { NONE | PAGE | ROW }
[ ON PARTITIONS ( { number | range } ]
[ ON partition_scheme ( column )
| ON filegroup
| ON default_filegroup ]
[ FILESTREAM_ON { filegroup | partition_scheme };

UNIQUE

Indicates that the combination of values in the indexed columns must be unique.

CLUSTERED

Demonstrates that the consistent request decides the physical request of the columns in the table.

NONCLUSTERED

Demonstrates that the sensible request does not decide the physical request of the columns in the table.

Index_name

The name of the list to make.

Table_name

The name of the table or view on which the list is to be made. column1, … column_n: The sections to base the file. ASC | DESC: The sort request for each of the segments. Incorporate ( column1, … column_n ) The segments that are not key segments to add to the leaf level of the nonclustered list.

Condition

The condition to determine which rows to include in the index.

ON filegroup

Indicates that the index will be created on the specified filegroup.

ON default_filegroup

Indicates the default filegroup.

FILESTREAM_ON { filegroup | partition_scheme }

Indicates where to place the FILESTREAM data for a clustered index.

shape Examples

To Drop an Index:

DROP INDEX index_name;

Creating an Index where duplicate values are allowed:

CREATE INDEX student_details_idx
ON student_details(dept_num);

Creating a UNIQUE Index where duplicate values are not allowed :

CREATE INDEX student_details_unique_idx
ON student_details(student_id);

Dropping an index

DROP INDEX student_details_idx;

Summary

shape Key Points

  • PostgreSQL INDEX – Provide efficiently to retrieving the data and records from the database.