Postgres CREATE INDEX

Introduction

If you’re using PostgreSQL to store and retrieve data, it’s important to have a solid understanding of indexes and how to use them. Indexes can help optimize the performance of your database, enabling data to be located more quickly. It’s simple to construct an index in PostgreSQL– all you need to do is use the CREATE INDEX command. In this article, we’ll provide a basic overview of indexes and how they work, and we’ll show how to use the Postgres CREATE INDEX command to build a new index.

Prerequisites

Let’s begin by reviewing a few prerequisites that need to be in place before we try using the Postgres CREATE INDEX command:

  • You must ensure that PostgreSQL server is properly installed, configured and running in the background.

Linux and Windows users can download PostgreSQL here.

  • If you’re a Linux user, you can start up the PostgreSQL service with the following command:
sudo service postgresql start
  • You can verify that the PostgreSQL service is running using this command:
service postgresql status
  • You’ll receive output that looks something like the following:
â— postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor prese
Active: active (exited) since Thu 2019-08-01 14:51:20 PST; 36min ago
Process: 1230 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 1230 (code=exited, status=0/SUCCESS)

Aug 01 14:51:20 user-UX330UAK systemd[1]: Starting PostgreSQL RDBMS...
Aug 01 14:51:20 user-UX330UAK systemd[1]: Started PostgreSQL RDBMS.
lines 1-8/8 (END)
  • To start, stop or restart the PostgreSQL service on a Windows machine, you can use the following instructions:
  1. Open Control Panel
  2. Open Administrative Tools
  3. Open Services
  4. Find the PostgreSQL Server service
  5. Start, stop or restart the PostgreSQL Server service

Restarting PostgreSQL server in a Windows Machine

What are PostgreSQL Indexes?

Indexes helps databases retrieve data more efficiently. To understand how a database index works, it can be helpful to compare it to a book index. When we want to find all pages in a book where a specific topic is referenced, we consult the index of that book. The book index organizes all the topics in an alphabetical order; to narrow down the search, we refer to a specific page number of that topic. Without the book’s index, we’d have to flip through every page of the book until we found the references we were looking for. Similarly, a database index allows data to be located quickly without having to check every row of a table.

The WHERE clauses and SELECT queries perform much more efficiently with the help of an index; however, the data input process is slower, which impacts the performance of the INSERT and UPDATE statements. Indexes can be dropped or created without affecting the data.

We use the Postgres CREATE INDEX statement to construct an index; the statement allows us to specify the following:

  • Allows us to provide a name for an index
  • Allows us to define the table and the specific column or columns to be indexed
  • Allows us to specify the order of the index

What is the PostgreSQL CREATE INDEX statement?

The below statement depicts the basic syntax of the PostgreSQL CREATE INDEX statement:

CREATE INDEX you_index_name ON your_table_name
[USING method]
(
your_column_name [ASC | DESC] [NULLS {FIRST | LAST }],
...
);

Let’s take a closer look at each part of the syntax shown above:

  • We start by specifying the index name immediately after the clause CREATE INDEX. It’s important to give the index a meaningful name.
  • Next, we provide the table name on which the index will reside.
  • We then specify the method to be used by our index, choosing from the following available methods:
    • brin
    • gin
    • btree
    • hash
    • gist
    • spgist

NOTE: The default method used by PostgreSQL is btree.

  • Finally, we can specify the column or multiple columns that we will be indexing. We use ASC and DESC to specify ascending or descending sort order, with ASC being the default. After the sort order, you can also specify how NULL values should be sorted. When the order is descending, then the default setting for null sorting is NULLS FIRST; otherwise NULL LAST serves as the default.

How to Use PostgreSQL CREATE INDEX

Now that we’ve covered some basic information about the PostgreSQL CREATE INDEX statement, let’s take this knowledge and apply it to an example.

Before we proceed with the example, we’ll need to create a sample table:

CREATE TABLE customer_address (
address_id INT PRIMARY KEY NOT NULL,
address TEXT NOT NULL,
phone VARCHAR(11) NOT NULL
);

Let’s insert a bit of sample data into our sample table:

INSERT INTO customer_address(address_id,address, phone)
VALUES (001,'#43 Long Road Nowhere City', '12345678901');

We can use the statement shown below to create an index against the values of the phone column within the customer_address table:

CREATE INDEX first_index_addr_phone
ON customer_address(phone);

Now, let’s try to query the customer_address table using the below statement to see if the index was created successfully:

\d customer_address

The result of this statement should look something like the following:

TABLE "public.customer_address"
COLUMN | TYPE | Collation | NULLABLE | DEFAULT
------------+-----------------------+-----------+----------+---------
address_id | INTEGER | | NOT NULL |
address | text | | NOT NULL |
phone | CHARACTER VARYING(11) | | NOT NULL |
Indexes:
"customer_address_pkey" PRIMARY KEY, btree (address_id)
"first_index_addr_phone" btree (phone)

Conclusion

It’s important to make sure that your database can retrieve data as quickly as possible in order to optimize the performance of your applications. Indexes provide a simple way to help PostgreSQL retrieve data more efficiently, in the same manner that a book index makes it easier to locate information. In this article, we talked about indexes and explained how to use the Postgres CREATE INDEX command to define an index on a table. With the examples we offered in this tutorial, you’ll have no trouble utilizing indexes in your own PostgreSQL database.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed Redis,
MongoDB & Elasticsearch

Get Started

OR

Try CockroachDB
in Beta

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.