Improving query performance with indexes

An index is a separate data structure that PostgreSQL maintains for a table. It stores values from one or more table columns in a form that allows PostgreSQL to locate matching table rows efficiently.

Without a suitable index, PostgreSQL may need to examine every row in a table:

With a suitable index, PostgreSQL may be able to search the index for the required value and then retrieve only the matching table rows:

Indexes are particularly useful for selective conditions. A condition is selective when it matches only a small proportion of the table.

The condition in our query is highly selective:

It matches one row out of 100,000, making it a good candidate for an index.

Note that there is no fixed number or percentage of matching rows at which PostgreSQL decides to use an index. When planning a query, PostgreSQL compares the estimated cost of using the available index with other possible plans, such as a sequential scan, and chooses the plan with the lowest estimated cost.

Also, PostgreSQL automatically keeps an index up to date as the table data changes. When rows are inserted, updated, or deleted, PostgreSQL updates the index when necessary so that it continues to point to the correct table rows.

PostgreSQL provides several built-in index types:

• B-tree

• Hash

• GiST

• SP-GiST

• GIN

• BRIN

Each index type is designed for particular kinds of data and operations.

In this chapter, we will concentrate on the B-tree index. B-tree is the default index type created by PostgreSQL and supports many common equality and range conditions, including:

Our query uses an equality condition:

A B-tree index can support this type of condition, so it is a suitable index type for our example.

The basic form of CREATE INDEX is:

The statement contains three important parts:

• index_name is the name assigned to the index.

• table_name is the table the index belongs to.

• column_name is the column whose values will be indexed.

Create an index on the payment_reference column:

Type the statement into the SQL editor and run it.

The index name is idx_orders_payment_reference. The name describes both the table and the indexed column, making the purpose of the index easier to recognize.

We didn't specify an index type. PostgreSQL therefore created a B-tree index, which is the default. The same index could be defined explicitly like this:

Specifying USING btree is optional in this case.

Now run the same statement again:

You should now see a different execution plan:

The Seq Scan has been replaced by an Index Scan:

This tells us that PostgreSQL used the newly created index to locate the matching order.

The next line contains:

Index Cond means that PostgreSQL used this condition while searching the index. Previously, the same condition appeared as a Filter applied while scanning every table row.

Notice that the new plan doesn't contain:

PostgreSQL no longer needs to apply the condition to every row in the table. It can use the index to locate the matching row directly.

The difference between the two executions can be summarized as follows:

• Scan node: Before the index, PostgreSQL used a Seq Scan. After the index, it used an Index Scan.

• Rows returned: Both executions returned one row.

• Rows removed by the filter: The sequential scan removed 99,999 rows. The index scan didn't need to filter those rows.

• Shared-buffer accesses: The sample sequential scan reported 1,021. The sample index scan reported four.

• Estimated total cost: The estimate changed from 2271.00 to 8.44.

• Execution time: In the sample executions, the time changed from 7.146 milliseconds to 0.018 milliseconds.

For this particular execution, the reported execution time was approximately 400 times faster after the index was created.

You shouldn't expect the same timing on every computer or every execution. Timing is affected by caching, hardware, other database activity, and measurement overhead.

The more important result is the reduction in work. Before the index was created, PostgreSQL examined 100,000 rows and accessed 1,021 shared blocks. Afterward, it used the index and made only four shared-buffer accesses to locate and retrieve the matching row.

Creating an index doesn't force PostgreSQL to use it.

The planner considers the available execution plans and selects the one with the lowest estimated cost. For a query that returns one row from a large table, an index scan will often be cheaper.

However, if a query returns most or all of a table, a sequential scan may be faster. Reading the table sequentially can require less work than repeatedly moving between an index and the table.

For example, the following statement returns every order:

An index on payment_reference doesn't help this query because PostgreSQL still needs to retrieve every row.

A sequential scan is therefore not automatically a sign of poor performance. It becomes worth investigating when PostgreSQL examines a large number of rows but returns only a small number of them.

The performance improvement provided by an index isn't free.

An index requires additional storage. You can inspect the size of the table and the index with the following statement:

You should see values similar to these:

The exact sizes may differ, but the important point is that the index occupies additional storage.

Indexes also add work when table data changes. PostgreSQL must keep the index synchronized when relevant rows are inserted, updated, or deleted. Additional indexes can therefore make write operations more expensive.

For this reason, you shouldn't create an index on every column. An index is most valuable when it supports queries that are important and frequently executed.

Before removing an index, you can check which indexes currently exist on the orders table. Run the following query:

The result includes the name and definition of each index on the table.

Suppose you no longer need the idx_orders_payment_reference index. You can remove it using the following statement:

Dropping the index removes only the index. It doesn't remove the orders table or any of its rows.

The important lesson isn't that index scans are always better than sequential scans. It is that EXPLAIN ANALYZE can reveal when PostgreSQL is performing substantially more work than the result appears to require, and a suitable index can give the planner a more efficient option.