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.
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 };
Indicates that the combination of values in the indexed columns must be unique.
Demonstrates that the consistent request decides the physical request of the columns in the table.
Demonstrates that the sensible request does not decide the physical request of the columns in the table.
The name of the list to make.
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.
The condition to determine which rows to include in the index.
Indicates that the index will be created on the specified filegroup.
Indicates the default filegroup.
Indicates where to place the FILESTREAM data for a clustered index. Examples
To Drop an Index:
[c]
DROP INDEX index_name;[/c]
Creating an Index where duplicate values are allowed:
[c]
CREATE INDEX student_details_idx
ON student_details(dept_num);[/c]
Creating a UNIQUE Index where duplicate values are not allowed :
[c]
CREATE INDEX student_details_unique_idx
ON student_details(student_id);
[/c]
Dropping an index
[c]
DROP INDEX student_details_idx;
[/c]
Key Points
- PostgreSQL INDEX - Provide efficiently to retrieving the data and records from the database.