Choosing the best indexes for PostgreSQL query optimization

 

PostgreSQL is extremely popular these days, and we decided to share a few tips on choosing the best indexes for your PostgreSQL implementation.

In this article we will explain how to do it manually, but if you prefer to find missing indexes, automatically you can use tools like EverSQL.

let's start.

How to find existing indexes in PostgreSQL?

Unlike other databases, PostgreSQL doesn't have the 'SHOW INDEXES' command. However, You can select a list of all your PostgreSQL Indexes using this command:

SELECT 
   tablename,indexname,indexdef
FROM
   pg_indexes
WHERE
   schemaname = 'public'
ORDER BY
   tablename,
   indexname;

You can also list all the indexes on a specific table:

SELECT
   tablename,indexname,tablespace,indexdef
FROM
   pg_indexes 
WHERE 
   tablename = 'your_table_name'
ORDER BY 
   indexname;

Or use the PSQL command, with the \d option to list all tables, indexes and views

Does the order of the WHERE clause matter?

In PostgreSQL it is not essential, and it makes no difference. PostgreSQL optimizer will automatically reorder the clauses to ensure it can utilize the best indexes.

If you want to test it, you can run EXPLAIN on your original query, then change the order of the WHERE Clause and rerun the EXPLAIN. The output will be the same.

What is a composite index?

A composite index is an index defined on more than one column, and can be helpful when running a WHERE clause that filters on the 1st column or filters on both the 1st and 2nd columns.

CREATE INDEX IND_name_owner ON "demo_table" (tablename,tableowner);

Composite indexes vs Separate indexes

The composite index, also known as the concatenated or multi-column index, is an index on multiple columns in a table. There is a common question: Which is better: Using separate or composite indexes?

In composite indexes the order of the column is critical. Therefore:

• if your query has a WHERE clause that filters both on the 1st column and on the 2nd column, it would be good to have a composite index.
• If your WHERE clause filters on the 1st column, the composite index will work great.
• If your WHERE clause filters on the 2nd column, the composite index will not work great comparing to a separate index on the 2nd column, or alternatively a composite index, where the 2nd column is now the 1st in order.

ANALYZE? How do I make sure that PostgreSQL has the updated stats on my table size?

To ensure there are optimizer statistics, you can run ANALYZE so that PostgreSQL's optimizer will choose the best execution plan..
The autovacuum usually creates statistics for your table, but to make sure, run ANALYZE as well.

ANALYZE <TableName>

Does PostgreSQL allow two identical indexes on the same table?

Unfortunately Yes. PostgreSQL allows it and doesn't alert for duplicate indexes.

Does PostgreSQL know to use more than one index at the same time?

Luckily Yes. This is an excellent feature in PostgreSQL and helps queries with OR in their WHERE clause.

For example:

...WHERE id = 3865
      OR id = 3239;

In the above case, the optimizer will use the id index twice to speed up the execution. another option is that the optimizer will use a bitmap scan.

What does B-Tree index stands for?

The most common type of index is B-Tree, which stands for... Balanced-Tree and not Binary as many developers assume.

Why Balanced? Balanced means that it takes the same number of steps for every search to find a value, and this can be done when all tree's leaves are on the same level

What is PostgreSQL's bitmap index scan?

The common method for PostgreSQL is to use a regular index scan, where the optimizer looks at the index and find the relevant rows to fetch.

However, in some cases, PostgreSQL uses a temporary heap structure to speed up the execution. PostgreSQL will build a temporary bitmap heap that contain potential row locations. This is common practice for PostgreSQL when it uses more than one index for a query.

How to find duplicated or redundant indexes on PostgreSQL?

Redundant indexes are two or more indexes defined on the exact scope of columns and you can delete one of them.
It can happen in cases you created two identical indexes or created a composite index where

When a unique constraint or primary key is defined for a table, PostgreSQL automatically creates a unique index. Indexes are used to enforce unique constraints by covering columns that make up the primary key or unique constraint (multicolumn indices, if necessary). One of the common scenarios we see, is when a user tries to create another index on the primary key, forgetting that it already has an index.

Manually creating indexes on unique columns would duplicate the automatically created index.

In some cases it makes sense to create two different indeT types (e.g. FULLTEXT and BTREE index) on the same column.

Another common redundant indexes scenario that we see is when one index is already contained in another index. Here is an example:

Duplicated index detected in table: `account_tax`

● Potentially redundant index: account_status (`status`); 
● Already included in: account_idx_status_account_number (`status`,`account_number`);
● To drop the redundant index, execute: DROP INDEX account_status ON account_tax;

In these cases you can delete the minimal index, as it is already included in the composite index.

These redundant indexes can be found manually or automatically.

Which indexes should I create to optimize my PostgreSQL query?

The required indexes are related to your WHERE Clause. you should create indexes that will help the PostgreSQL to avoid a full table scan, and will allow it to use the index and access directly to the relevant rows.

In other cases index can help you in a query where you are fetching Min() or Max() value of a column.

If your query is dynamic, you can use slow query logs to find the executed query.

Suppose you're looking for a way to automate your index creation while adding the benefit of a proprietary indexing algorithm and query optimization recommendations. In that case, you can try out PostgreSQL Index Advisor, which does all the heavy lifting for you.