In this PostgreSQL tutorial, we will discuss the PostgreSQL CREATE INDEX to create an index for a table column(s), and will also cover the following topics:
- PostgreSQL CREATE INDEX
- PostgreSQL CREATE INDEX if not exists
- PostgreSQL CREATE INDEX btree
- PostgreSQL CREATE INDEX gin
- PostgreSQL CREATE INDEX during table creation
- PostgreSQL CREATE UNIQUE INDEX
- PostgreSQL CREATE COMPOSITE INDEX
- PostgreSQL CREATE UNIQUE index multiple columns
- PostgreSQL CREATE INDEX concurrently
- PostgreSQL CREATE INDEX without locking
- PostgreSQL CREATE INDEX concurrently if not exists
- PostgreSQL CREATE INDEX concurrently progress
- PostgreSQL CREATE INDEX on partitioned table
- PostgreSQL CREATE INDEX on view
PostgreSQL CREATE INDEX
INDEXES are used to improve the performance of the databases. Searching and retrieving specific rows by using an index is much faster than, doing it without using an index in a database server in PostgreSQL.
But indexes make it more complex to the database system, so we should know when to use them and when not. That is, the inappropriate use of index can result in lower performance of the databases.
Let’s discuss the indexes in detail through an example.
Let’s assume, we want to retrieve the records that follow some specified condition say it to be the data of the players who score more than 380 goals, which is as follows:
SELECT * FROM players WHERE score > 380;As the above statement executes, the database has to scan the entire player table, row by row, to search for score values greater than 380. If there are many rows to be searched in the table player and only a few of the rows satisfy the given condition, then it would be clearly an inefficient way to do it.
Perhaps, if there would be an index maintained on the column score for the player table, then the system could have used a more efficient method to locate the matching rows.
The syntax to create an INDEX in PostgreSQL is as follows:
CREATE INDEX index_name ON table_name [USING method]
(
column_name1 [ASC | DESC] [NULLS {FIRST | LAST }],
column_name2 [ASC | DESC] [NULLS {FIRST | LAST }],
...
...
...
column_nameN [ASC | DESC] [NULLS {FIRST | LAST }],
);In the above syntax,
- The clause CREATE INDEX creates an index on the specified column(s) of the given table.
- index_name specifies the name of the index to be created. We should give an appropriate name to the index that should be easy to remember.
- table_name specifies the name of the table, on whose column(s) the index_name index is to be created.
- method specifies the index method which specifies the algorithms to be used in different queries on the indexed column. PostgreSQL provides the index methods: btree, hash, GiST, and GIN. btree is the default method used by the PostgreSQL in index creation. We can also define our own index methods in PostgreSQL.
- column_name1, column_name2, …, column_nameN are the key field(s) that specify the names of the columns on which the index has to be created. Multiple columns are only supported only if the index method supports it, else there has to be only one column specified as the key field.
- The ASC (for ascending order) or DESC (for descending order) specifies the sort order of the index. ASC is default sort order.
- NULLS FIRST or NULLS LAST specifies the nulls to be sort before or after the non-nulls respectively. The NULLS FIRST is default for the DESC sort order and NULLS LAST is default when DESC is not specified.
As we have gone through the creation of the index in PostgreSQL. So now, let’s continue with the example that we were discussing before.
Let’s create an index on the column score of the players table.
CREATE INDEX score_index ON players
(
score DESC NULLS LAST
);The above statement creates an index with name score_index on the column score with descending sort order and nulls at the last.
NOTE :
- Once an index is created, there is no need for any explicit intervention.
- The system updates the index whenever the table gets modified, and it uses the index whenever it is necessary to use it for efficient scan.
- We might have to run the ANALYZE and EXPLAIN commands regularly to update the information to make the query planner to make effecient decisions. The decisions are to use an index for lookup or not.
- The system has to keep the indexes synchronized with the table that adds an overhead to data manipulation tasks. Therefore, the indexes that are never used should be removed.
The index can be removed by the following statement:
DROP INDEX index_name;Also, read Postgresql generate_series with examples
Example :
Let’s create a sample table employee_data to practice the examples:
create table employee_data (
emp_id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
gender VARCHAR(10),
email VARCHAR(50),
phone VARCHAR(15) NOT NULL,
street VARCHAR(70),
city VARCHAR(70));
\d employee_data
I have populated the table employee_data with some sample data. I have added some of the sample data’s insertion code, for reference:
INSERT INTO employee_data (name, gender, email, phone, street, city)
VALUES ('Lock Mixon', 'Non-binary', 'lmixon0@hp.com', '4798025824', '44 Moose Street', 'Fort Smith'),
('Gussy Moyles', 'Polygender', 'gmoyles1@i2i.jp', '8977000489', '8315 Moulton Point', 'Batken'),
('Tuesday Sudddard', 'Bigender', 'tsudddard2@feedburner.com', '8269437408', '03529 Gerald Terrace', 'Skaramangás'),
('Josepha Guichard', 'Polygender', 'jguichard3@de.vu', '1469317918', '5926 Delladonna Circle', 'Piet Retief'),
('Calida Backson', 'Male', 'cbackson4@bloglines.com', '5118919836', '973 Little Fleur Center', 'Ncue'),
('Alano Cumpsty', 'Female', 'acumpsty5@w3.org', '7772849801', '3471 Jay Circle', 'Örebro'),
('Lotty Freake', 'Non-binary', 'lfreake6@seattletimes.com', '2245210688', '17463 Melody Lane', 'Panyingkiran'),
('Selby Hellwing', 'Bigender', 'shellwing7@twitter.com', '2756859428', '4 Katie Pass', 'Lunen'),
('Lynda Ivanyushkin', 'Polygender', 'livanyushkin8@tumblr.com', '4926478910', '598 Sage Avenue', 'Sanyantang'),
('Sherrie Eagers', 'Female', 'seagersl@usnews.com', '9033717585', '03892 Sutteridge Way', 'Żernica'),
('Tera Bodechon', 'Male', 'tbodechonm@mashable.com', '4232896925', '7 Forest Dale Crossing', 'Burmakino'),
('Inness Casol', 'Polygender', 'icasoln@vkontakte.ru', '9824986061', '63270 Barnett Way', 'Shanjeev Home'),
('Edvard Mossom', 'Polygender', 'emossomo@cargocollective.com', '8342088617', '4 Meadow Valley Street', 'Hoktember'),
('Shirl Atwater', 'Bigender', 'satwaterp@ucla.edu', '1745856477', '99866 Redwing Parkway', 'Tiguion'),
('Mohammed Slocum', 'Agender', 'mslocumq@cpanel.net', '6499627525', '808 Riverside Street', 'Hidalgo'),
('Sher McCracken', 'Non-binary', 'smccrackenr@admin.ch', '2621373627', '04906 Reinke Plaza', 'Cabangahan'),
('Madelena Nickolls', 'Male', 'mnickollss@lycos.com', '6822785526', '23674 Spohn Drive', 'Napoles'),
('Xever Boase', 'Agender', 'xboaset@google.nl', '4765398650', '8 Bluejay Place', 'Xialu'),
('Judy Cossey', 'Non-binary', 'jcosseyu@about.com', '1561251217', '36 Schmedeman Alley', 'Dashkawka');
SELECT COUNT(*) FROM employee_data;
SELECT * FROM employee_data LIMIT 10;
Now, let’s practice the examples:
SELECT * FROM employee_data
WHERE phone = '8599420436';
EXPLAIN SELECT * FROM employee_data
WHERE phone = '8599420436';
CREATE INDEX employee_data_phone_idx ON employee_data
(phone);
EXPLAIN SELECT * FROM employee_data
WHERE phone = '8599420436';
We have created an index employee_data_phone_idx on the employee_data table with the column phone.
Read Postgresql import SQL file
PostgreSQL CREATE INDEX if not exists
We can specify that an index is to be created only if the index with the same name, does not already exist. We can do so by adding the keyword IF NOT EXISTS after CREATE INDEX clause in the index creation statement.
If not specified, and an index with the same name already exists, it throws an error.
Let’s practice through an example:
CREATE INDEX employee_data_phone_idx ON employee_data
(phone);
CREATE INDEX IF NOT EXISTS employee_data_phone_idx ON employee_data
(phone);
We have created an index with the keyword IF NOT EXISTS to prevent PostgreSQL to throw an error if another index with the same name already exists.
Read Postgresql cast int
PostgreSQL CREATE INDEX btree
We can specify the btree index method while creating an index. The btree index method is used in the equality and range queries on the data that may be sorted to an order.
It means that the query planner of the PostgreSQL allows the btree index to be used whenever the indexed column is used in a comparison using the comparison operators (<, >, =, <=, >=).
The query planner may also use the btree index method when the indexed column is involved in the queries using BETWEEN, IN, IS NULL, and IS NOT NULL conditions.
btree indexes may also be used in the queries involving the operators LIKE, ~ for pattern matching. Note that the btree is used in the queries involving the operators ILIKE and ~* also but for only those patterns matching queries that do not involve the first character of the pattern to be an alphabet.
\d employee_data
EXPLAIN SELECT * FROM employee_data
WHERE emp_id < 791 AND emp_id > 800;
EXPLAIN SELECT * FROM employee_data
WHERE salary = 34036;
CREATE INDEX employee_data_salary_idx ON employee_data
USING btree (salary NULLS LAST);
EXPLAIN SELECT * FROM employee_data
WHERE salary = 34036;
We have first analyzed a select query on the PRIMARY KEY column and then created a btree index on the table with the salary column. Where we can see the difference in the performance of select query on the salary column with and without using an index.
Read How to find primary column name in Postgresql
PostgreSQL CREATE INDEX gin
We can specify the GIN (Generalized Inverted Index) index method while creating an index. The GIN indexes are used in queries that involve more than one key like in an array.
The syntax to create a GIN index is as follows:
CREATE INDEX index_name ON table_name
USING GIN (column_name);In the above syntax, the column_name column must be of tsvector type.
GIN supports various user_defined indexing strategies. The involvement of the operators with which a GIN index is used varies according to the indexing strategy used.
The query planner uses the GIN index for the queries that involve the operator classes for a one-dimensional array, which are <@, @>, =, &&, etc.
Read Update query in PostgreSQL
PostgreSQL CREATE INDEX during table creation
We cannot create an index during the table creation explicitly, while the UNIQUE INDEX and PRIMARY KEY INDEX get created automatically by the PostgreSQL on specifying the CONSTRAINT of a column to be UNIQUE and PRIMARY KEY respectively.
Let’s practice an example to create a UNIQUE index and PRIMARY KEY index during table creation:
CREATE TABLE students(
stu_id INT PRIMARY KEY,
name VARCHAR(60),
gender VARCHAR(10),
phone VARCHAR(10) UNIQUE,
address VARCHAR(70) NOT NULL);
\d students
We have specified the PRIMARY KEY constraint and UNIQUE constraint for the columns during the table creation statement, which results in the creation of two indexes with the btree method by default.
Read Postgresql date_trunc function
PostgreSQL CREATE UNIQUE INDEX
We can create a UNIQUE INDEX by adding the parameter UNIQUE in the CREATE INDEX statement.
It makes the system, check for the duplicate values in the table at the creation of INDEX and every time the data is added to the table.
If any INSERT or UPDATE statement to the table data results in duplicate entries, then it will generate an error.
The syntax of creating a UNIQUE INDEX is as follow:
CREATE UNIQUE INDEX index_name ON table_name
(
column_name1 [ASC | DESC] [NULLS {FIRST | LAST }],
column_name2 [ASC | DESC] [NULLS {FIRST | LAST }],
...
...
...
column_nameN [ASC | DESC] [NULLS {FIRST | LAST }]
);Now, let’s practice the concept through an example:
\d employee_data
CREATE UNIQUE INDEX employee_data_name_idx ON employee_data
(name);
\d employee_data
INSERT INTO employee_data (name, gender, email, phone, street, city, salary)
VALUES
('Shoshanna Hovey', 'Female', 'shohovey13@mjoet.com', '9835642730', '616 Colene Place', 'Hallway city', '45000');
We have created a UNIQUE index on the column of the table, which throws an error if we try to add or update duplicate values on that column.
Read PostgreSQL TO_NUMBER() function
PostgreSQL CREATE COMPOSITE INDEX
As we have already seen in the syntax of the CREATE INDEX, that more than one column of a table can be specified in the table creation.
The COMPOSITE INDEX is also known as the MULTICOLUMN INDEX. As the name describes itself, that an index has to be created on more than one column of a table.
As for now, only the btree, GiST, and GIN index types support a COMPOSITE INDEX in PostgreSQL with not more than 32 columns specified at once in the CREATE INDEX statement.
NOTE:
- We can use a multicolumn btree index where the query condition involves any subset of the index’s column but is most efficient when the leftmost columns have some constraints.
- More specifically, the equality constraints has to be there on the leftmost columns and any inequality contraints on the columns, just after the columns with equality contraint. This will reduce the lookup.
- We can use a multicolumn GiST index where the query condition involves any subset of the index’s column. Here, the condition specified on the first column is the most important to determine that how much of the index should be scanned.
- It becomes ineffective in the case where the first column of the index has only a few distinct values, irrespective of the number of distinct values in the additional columns.
- We can use a multicolumn GIN index where the query condition involves any subset of the index’s column. Here unlike the other two indexes, there is no dependency of the effectiveness of the index search, regardless on which index column(s) the query condition is used.
Let’s clear the concepts by practicing some examples:
\d employee_data
CREATE INDEX employee_date_gender_salary_idx ON employee_data
(
gender NULLS LAST,
salary DESC NULLS LAST
);
\d employee_data
EXPLAIN SELECT * FROM employee_data
WHERE gender = 'Male' AND salary <= 21000;
We have created a multicolumn (composite) index and analyze a SELECT query containing the columns specified in the CREATE INDEX statement.
Read PostgreSQL TO_TIMESTAMP function
PostgreSQL CREATE UNIQUE index multiple columns
We can also create a multicolumn index to be constrained as UNIQUE, by specifying the parameter UNIQUE in the CREATE INDEX statement.
It works as same as for the single column UNIQUE index, just the uniqueness is to be maintained for the combination of the columns (multicolumn).
Let’s illustrate the concept through an example:
\d employee_data
CREATE UNIQUE INDEX employee_date_gender_salary_idx ON employee_data
(
gender NULLS LAST,
salary DESC NULLS LAST
);
CREATE UNIQUE INDEX employee_date_name_gender_idx ON employee_data
(
name NULLS LAST,
gender NULLS LAST
);
INSERT INTO employee_data (name, gender, email, phone, street, city, salary)
VALUES ('Shoshanna Hovey', 'Female', 'shohovey13@mjoet.com', '9835642730', '616 Colene Place', 'Hallway city', '45000');
VALUES ('Shoshanna Hovey', 'Male', 'shohovey13@mjoet.com', '9835642730', '616 Colene Place', 'Hallway city', '45000');
We have created a unique multicolumn index on the table and tried to insert a row with duplicate values on the columns specified in the CREATE INDEX statement in PostgreSQL.
Read PostgreSQL TO_CHAR function
PostgreSQL CREATE INDEX concurrently
Normally, PostgreSQL locks the table where the indexing is in action. That is, the table to be indexed is locked against the write operations on the table till the table entirely builds the index with a single scan.
In such cases, PostgreSQL provides the feature of building indexes without locking the write operations of the table by specifying the option CONCURRENTLY in the CREATE INDEX statement.
When creating an index concurrently, PostgreSQL will perform two scans of the table, and each table scan will wait for all the transactions that can modify or use the index to get terminated. Therefore, this will take more time to scan and build the index of a table.
After the second table scan, the index built will wait for any transactions that have the snapshot which predates the second scan to get terminated.
Then finally, the index is marked ready to use, and hence, the CREATE INDEX statement gets terminated.
NOTE:
- Concurrent index build does not permit another concurrent index build on the same table at a time, unlike in regular indexes, where more than one index can be built parallelly at a time.
- A concurrent index cannot be created within a transaction block, while a regular index can be created within a transaction block.
Now, let’s practice an example to understand the concept more clearly:
\d employee_data
CREATE INDEX CONCURRENTLY employee_data_salary_idx ON employee_data
(salary);
\d employee_data
We have created an index concurrently on the PostgreSQL table.
Read Postgresql change column data type
PostgreSQL CREATE INDEX without locking
We can CREATE INDEX without locking by creating it concurrently in PostgreSQL. In the previous topic, we have gone through this concept.
So, let’s do some more examples to make it more clear:
\d employee_data
CREATE INDEX CONCURRENTLY employee_data_name_phone_idx ON employee_data
(
name NULLS LAST,
phone
);
\d employee_data
We have created an index concurrently on the table which results in not locking the write permissions of the table unlike in regular index creation.
Read How to import CSV file into PostgreSQL
PostgreSQL CREATE INDEX concurrently if not exists
We can also specify the keywords IF NOT EXISTS in the PostgreSQL CREATE INDEX statement that restricts the concurrent index creation if another concurrent index with the same name is already built.
Let’s practice an example to implement the concept:
\d employee_data
CREATE INDEX CONCURRENTLY employee_data_salary_idx ON employee_data
(salary);
CREATE INDEX CONCURRENTLY IF NOT EXISTS employee_data_salary_idx ON employee_data
(salary);
We have created an index concurrently on the table with IF NOT EXISTS keywords specified to restrict the duplicate index.
Read How to backup PostgreSQL database
PostgreSQL CREATE INDEX concurrently progress
In PostgreSQL, we can see the details regarding the progress of the CREATE INDEX query through a VIEW named pg_stat_progress_create_index, which contains one row for each backend involves in creating the indexes.
Let’s see the information it provides through an example:
SELECT * FROM pg_stat_progress_create_index;
Let’s discuss the columns of the pg_stat_progress_create_index view:
| Column_name | Type | Description |
|---|---|---|
| pid | INT | It specifies the process ID of the backend. |
| datid | OID | It specifies the OID of the database which is connected to the backend. |
| datname | NAME | It specifies the name of the database which is connected to the backend. |
| relid | OID | It specifies the OID of the table on which the index is created. |
| index_relid | OID | It specifies the OID of the index created or reindexed. Only for concurrent index creation, else 0. |
| command | TEXT | It specifies the running command. CREATE INDEX, CREATE INDEX CONCURRENTLY, REINDEX, or REINDEX CONCURRENTLY. |
| phase | TEXT | It specifies the current processing phase of the index creation (like initializing, building index, index validation: scanning index, etc). |
| lockers_total | BIGINT | It specifies the total number of lockers to wait. |
| lockers_done | BIGINT | It specifies the number of lockers already waited for. |
| current_locker_pid | BIGINT | It specifies the process ID of the locker currently being waited for. |
| blocks_total | BIGINT | It specifies the total number of blocks to be processed in the current phase. |
| blocks_done | BIGINT | It specifies the number of already processed blocks in the current phase. |
| tuples_total | BIGINT | It specifies the total number of tuples to be processed in the current phase. |
| tuples_done | BIGINT | It specifies the number of already processed tuples in the current phase. |
| partitions_total | BIGINT | It specifies the total number of partitions on which the index is to be created, when creating an index on a partitioned table. |
| partitions_done | BIGINT | It specifies the total number of partitions on which the index has already been created, when creating an index on a partitioned table. |
Now, let’s see the Phases in creating an index:
| Phase | Description |
|---|---|
| initializing | When CREATE INDEX or REINDEX starts preparing to create the index. |
| waiting for writers before build | When CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY is waiting for the transactions with write locks that can see the table to finish. |
| building index | When the index is being created by the access method-specific code. The access methods which support progress reporting fill their own progress data. |
| waiting for writers before validation | When CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY is waiting for the transaction with write locks that can write into the table to finish. |
| index validation: scanning index | When CREATE INDEX CONCURRENTLY is scanning the index searching for tuples that need to be validated. |
| index validation: sorting tuples | When CREATE INDEX CONCURRENTLY is sorting the output of the index scanning phase. |
| index validation: scanning table | When CREATE INDEX CONCURRENTLY is scanning the table to validate the index tuples gathered previously. |
| waiting for old snapshots | When CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY is waiting for the transactions that can see the table to release their snapshots. |
| waiting for readers before marking dead | When REINDEX CONCURRENTLY is waiting for the transactions with read locks on the table to finish, before marking the old index dead. |
| waiting for readers before dropping | When the REINDEX CONCURRENTLY is waiting for the transactions with read locks on the table to finish, before dropping the old index. |
Read PostgreSQL Export Table to CSV
PostgreSQL CREATE INDEX on partitioned table
We can CREATE INDEX on the partitioned table in PostgreSQL, which will automatically create indexes on each existing child table. Also, the index is created on any new future partition created.
Let’s illustrate it through an example. First create partitioned tables:
CREATE TABLE tab1 (num INT)
PARTITION BY RANGE(num);
CREATE TABLE child_tab1
PARTITION OF tab1 FOR VALUES FROM (0) TO (5)
PARTITION BY RANGE(num);
CREATE TABLE child2_tab1
PARTITION OF tab1 FOR VALUES FROM (5) TO (10)
PARTITION BY RANGE(num);
CREATE TABLE child_child2_tab1
PARTITION OF child2_tab1 FOR VALUES FROM (5) TO (7);
\d tab1
\d+ tab1
\d+ child2_tab1
\d+ child_tab1
Now, let’s create an index on the partitioned table
CREATE INDEX child_tab1_idx ON child_tab1(num);
\d child_tab1
CREATE INDEX tab1_idx ON tab1(num);
\d tab1
\d child_tab1
\d child2_tab1
\d child_child2_tab1
Above, we have created partitioned tables up to 3 levels and create indexes on the partitioned tables of different levels.
Read PostgreSQL drop all tables
PostgreSQL CREATE INDEX on view
We can create an index only on a materialized view not a regular view in PostgreSQL.
Materialized View is a database object that stores the result of a pre-calculated query of the database. It provides an ease to refresh the results as per our need.
We can create an index on a materialized view, as same as we did in creating an index on the table. The syntax is also as same as we did for the tables, we just have to specify the name of the materialized view in the place of the table_name.
Let’s do an example to illustrate the concept:
CREATE VIEW employee_view AS
SELECT emp_id, name, phone, street, city
FROM employee_data
WHERE salary > 45000;
\d employee_view
CREATE INDEX employee_view_empid_idx ON employee_view
(emp_id ASC);
CREATE MATERIALIZED VIEW employee_materialized_view AS
SELECT emp_id, name, phone, street, city
FROM employee_data
WHERE salary > 45000;
CREATE INDEX employee_materialized_view_empid_idx ON employee_materialized_view
(emp_id ASC);
\d employee_materialized_view
We have tried to create an index on the regular view, which results in an error. Then we have created a materialized view and created an index on that view.
You may also like the following PostgreSQL tutorials:
- How to Uninstall PostgreSQL (Linux, Mac, and Windows)
- PostgreSQL DROP COLUMN + 7 examples
- PostgreSQL INSERT INTO table + 9 Examples
- Postgresql auto increment
In this PostgreSQL tutorial, we have learned about the PostgreSQL CREATE INDEX to create an index for a table column(s), and have also covered the following topics:
- PostgreSQL CREATE INDEX
- PostgreSQL CREATE INDEX if not exists
- PostgreSQL CREATE INDEX btree
- PostgreSQL CREATE INDEX gin
- PostgreSQL CREATE INDEX during table creation
- PostgreSQL CREATE UNIQUE INDEX
- PostgreSQL CREATE COMPOSITE INDEX
- PostgreSQL CREATE UNIQUE index multiple columns
- PostgreSQL CREATE INDEX concurrently
- PostgreSQL CREATE INDEX without locking
- PostgreSQL CREATE INDEX concurrently if not exists
- PostgreSQL CREATE INDEX concurrently progress
- PostgreSQL CREATE INDEX on partitioned table
- PostgreSQL CREATE INDEX on view
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.